System design interview

Unified Analytics Platform (BI + ML + Real-Time)

HardLocked55 min read

Unified platform: Snowflake BI, Iceberg ML, ClickHouse real-time; shared transforms, catalog lineage, quality at zone boundaries.

system-designlakehousebimlreal-timeplatform

Start here

The problem in plain words

A large company has three kinds of data users. Finance wants exact reports. Data scientists want training data for models. Operations wants a live wall display updating every few seconds. One tool is rarely best for all three, so the company uses three engines.

The risk is that three engines mean three versions of the truth. The ops screen shows one revenue figure, finance shows another, and the model trained on a third. Everyone argues about whose number is right.

A unified platform solves this by landing raw data once, cleaning it into one shared set of business tables, and then serving each audience from that shared base. A catalogue and quality checks hold it all together, and a written rule says which number wins when they differ.

Words you will meet

Unified platform
Several engines serving different needs, sharing one raw landing and one set of definitions.
Data warehouse (BI)
A system tuned for business reports and finance. Snowflake is an example.
Iceberg
A table format on cheap object storage, used for large datasets and machine learning.
ClickHouse
A very fast database for real-time analytics tiles.
Bronze, Silver, Gold
Zones of increasing cleanliness: raw, conformed business tables, then serving-ready tables.
Conformed entity
A shared definition of something like customer or order, used by everyone.
Catalogue and lineage
A directory of datasets and a map of where each came from.
Data quality gate
A check at a zone boundary that stops bad data from moving on.
Semantic layer
A place where a metric such as net revenue is defined once and reused by tools.
Point-in-time correctness
Training data uses only what was known at that moment, so models do not peek at the future.
Three engines, three revenue numbers
differsdiffersdiffersOps wallboardrevenue: 100Finance reportrevenue: 94ML modelrevenue: 97Which is right?no shared definition
Press Play to watch it move

Each arrow is one thing happening. Play it step by step, or read the list below.

  1. 1.The live display ignores late refunds, so its number is highest.
  2. 2.Finance includes refunds from the next day, so its number is lowest.
  3. 3.The model uses a third definition. With no written rule, nobody can say which one is right.

The numbers, as things you can picture

  • Three engines honestly neededThe company is big enough that BI, ML and real-time each justify their own engine, so the job is sharing meaning, not removing engines.

What the interviewer wants

Design a platform with Snowflake for BI, Iceberg for ML and ClickHouse for real time that shares definitions, lineage and quality checks.

The interview room

Design a unified analytics platform serving BI (Snowflake), ML training (Iceberg), and real-time dashboards (ClickHouse materialized views). Share transformation logic, use a metadata catalog for lineage, and enforce data quality at zone boundaries.

This is a "platform judgment" interview. They are testing whether you create three disconnected lakes or one semantic system with specialized serving engines.

Why multiple engines exist

  • Warehouses (Snowflake/BigQuery/Redshift): governed SQL, BI tools, security features, elasticity for analytical queries.
  • Lake tables (Iceberg/Delta): open format, large-scale training reads, engine-flexible, cheap storage.
  • Real-time OLAP (ClickHouse/Druid/Pinot): sub-second dashboards, high ingest, denormalized serving.

One engine rarely wins all three SLAs honestly.

The three-numbers problem

ML notebook defines revenue one way. BI dbt model another. ClickHouse MV a third. Exec meeting becomes an argument about whose number is "real." Unified platform is about shared zones and shared meaning, not one logo.

Medallion / zones (quick refresh)

Bronze (raw) --> Silver (conformed, cleaned) --> Gold (products / marts)

Quality gates belong at boundaries: do not promote bad Silver to Gold.

Catalog and lineage

A metadata catalog (DataHub, OpenMetadata, native + OpenLineage) answers: where did this column come from, who owns it, what breaks if it changes.

The problem

  • Land raw once; serve three consumption styles.
  • Share transform definitions where possible.
  • Catalog lineage across engines.
  • Quality gates at zone boundaries.
  • Avoid three conflicting metric definitions.
  • Clear SoR per use case; cost visibility per engine.

What good looks like

You draw one Bronze. You specialize serving. You name a shared semantic/transform approach. You put DQ gates between zones. You declare SoR when ClickHouse and Snowflake disagree. You do not invent three independent raw pipelines.

Clarifying questions

  • Must BI be Snowflake specifically, or "a warehouse"?
  • How real-time is real-time (5s vs 5 min)?
  • Is Iceberg already the company lake standard?
  • Who owns metric definitions?
  • Regulatory constraints on ML training copies of PII?

Out of scope

  • Training model architecture (features yes, deep NN design no).
  • Full vendor cost negotiation.
  • Replacing all existing marts on day one (phased migration is fine).

The exec meeting that creates this design

Monday 10 AM. Three slides. Three revenue numbers. Nobody knows which to trust. Engineering did not fail at "building pipelines." Engineering failed at shared meaning.

This interview rewards candidates who treat semantic alignment as architecture, not as a wiki afterthought.

Why not one engine for everything

You can force Snowflake to do near-real-time with tricks. You can force ClickHouse to do governed finance marts with enough process. You can train models by dumping warehouse extracts. Each force-fit costs money and operational pain. A unified platform admits specialization and invests in the glue: zones, contracts, catalog, DQ, SoR.

Zones are not bureaucracy

Bronze/Silver/Gold (or raw/conformed/product) exist so you know what guarantees you are buying. Bronze may be ugly. Silver should be trustworthy for joins. Gold should be fit for a specific consumer. Quality gates are the bouncers between those rooms.

Catalog is how scale survives people leaving

When the author of a ClickHouse MV leaves, lineage and ownership tags are how the next engineer finds blast radius. Without catalog integration across engines, you have three tribal maps.

Audience map (write it)

| Audience | Engine bias | Freshness | Correctness bar | |---|---|---|---| | Finance | Warehouse | Hours | Highest | | Ops | ClickHouse | Seconds | Medium, labeled | | DS/ML | Iceberg/features | Daily/hourly | PIT correctness | | Exec dashboards | Warehouse or certified RT | Mixed | Must cite SoR |

This table alone impresses interviewers because it shows product thinking.

Anti-goals

  • Three independent raw ingest pipelines
  • Undocumented approximate metrics presented as finance truth
  • Notebooks as the system of record for KPIs
  • Catalog that only knows dbt and ignores streaming MVs