system-design

Data Warehouses, Lakes, and Lakehouses

Your production database answers “what is order 42?” Analytics answers “what was our revenue by region last quarter?” These are different enough that running both on one system breaks both.

Prerequisites: Storage Engines, Choosing a Database Time to read: ~22 minutes

The problem

Someone from the business runs this on your production database:

SELECT region, date_trunc('month', created_at) AS month, sum(total_cents)
FROM orders JOIN customers ON ...
WHERE created_at > '2020-01-01'
GROUP BY 1, 2;

Two billion rows. The query runs for eleven minutes, saturates disk I/O, evicts your hot data from the buffer pool, and holds an MVCC snapshot that blocks vacuum. Meanwhile your checkout endpoint’s p99 goes from 40 ms to 4 seconds.

The instinct is “run it on a replica.” That helps — but it doesn’t fix the underlying mismatch, because the storage format itself is wrong for the question.

OLTP vs OLAP

	OLTP (production)	OLAP (analytics)
Question	“What is order 42?”	“Revenue by region by month?”
Rows touched	1–100	Millions to billions
Columns touched	All of them	3 of 50
Writes	Constant, small	Bulk loads
Users	Millions of end users	Dozens of analysts + dashboards
Latency target	Milliseconds	Seconds to minutes
Data age	Current	Historical, days to years
Storage layout	Row-oriented	Column-oriented

🚨 That last row is the whole story. Everything else follows from it.

Row vs column storage

Row store — all fields of a record stored together:

[1, Bilal, Lahore, 4200][2, Ayesha, Karachi, 1800][3, Omar, Dubai, 9500]

Reading one complete record is one contiguous read. Perfect for SELECT * WHERE id = 2.

Column store — all values of one field stored together:

ids:     [1, 2, 3]
names:   [Bilal, Ayesha, Omar]
cities:  [Lahore, Karachi, Dubai]
totals:  [4200, 1800, 9500]

📐 Why this transforms analytics:

For SELECT sum(total) FROM orders over 2 billion rows at 200 bytes each:

Row store:    reads all 400 GB (the whole row, to get one 8-byte column)
Column store: reads only the totals column = 16 GB
              → 25× less I/O before any other optimization

And it compounds:

Compression is spectacular because a column contains one type with similar values. Run-length encoding on region, dictionary encoding on status, delta encoding on timestamps. 10–100× compression is normal — so that 16 GB might be 1 GB on disk.
Vectorized execution — process a batch of values with SIMD CPU instructions instead of row-by-row.
Zone maps / min-max indexes — each block stores its min and max, so a WHERE created_at > '2026-01-01' skips entire blocks without reading them.

📐 Net effect: 10–1000× faster for analytical queries. That’s not a tuning difference; it’s a different category of system.

⚖️ And it’s terrible at OLTP. Fetching one complete row means reading from 50 separate column files. Updating one field means rewriting a compressed block. This is why you need both systems, not one.

Data warehouse

A structured, curated store optimized for analytical queries. Schema-on-write — data is cleaned, validated, and transformed before loading.

Production DBs ──ETL──> Data Warehouse ──> BI tools, dashboards, reports

Modelling: the star schema. Analytics data is usually modelled as a central fact table (events, measurements) surrounded by dimension tables (descriptive attributes).

              dim_date
                  │
dim_customer ── fact_orders ── dim_product
                  │
              dim_region

CREATE TABLE fact_orders (
    order_id     BIGINT,
    date_key     INT REFERENCES dim_date,
    customer_key INT REFERENCES dim_customer,
    product_key  INT REFERENCES dim_product,
    quantity     INT,
    amount_cents BIGINT          -- the measures
);

🚨 Note this is deliberately denormalized relative to production. Dimensions repeat data (dim_customer contains the city, region, and country as columns) because joins are expensive at this scale and storage is cheap. Normalization rules from relational modeling do not apply here — that’s a genuinely useful thing to know.

Slowly Changing Dimensions (SCD) handle the “customer moved cities” problem: Type 1 overwrites (losing history), Type 2 adds a new row with validity dates (preserving it). Type 2 is standard for anything where historical accuracy matters — you want last year’s report to reflect where the customer lived then.

Systems: Snowflake, BigQuery, Redshift, Databricks SQL, ClickHouse, Firebolt.

Data lake

Raw files in object storage. Schema-on-read — dump everything now, figure out its structure when you query it.

s3://data-lake/
├── raw/events/year=2026/month=07/day=22/*.parquet
├── raw/logs/...
└── curated/orders/...

✅ Cheap (object storage prices), stores anything (JSON, images, video, logs), and you don’t have to decide the schema before you have the data. ❌ 🚨 Without governance it becomes a “data swamp” — nobody knows what’s there, what’s current, or whether it’s trustworthy. This is the standard failure mode and it’s near-universal in organizations that adopted lakes without cataloguing.

File formats matter enormously here:

Format	Character
Parquet	Columnar, compressed, with statistics. The default choice.
ORC	Similar; Hive ecosystem
Avro	Row-based, great schema evolution. Good for streaming ingestion
JSON / CSV	🚨 Human-readable and terrible for analytics — no compression, no column pruning, no statistics

Partitioning by path (year=2026/month=07/) lets query engines skip entire directories — the same partition-elimination idea as in time-series databases.

Lakehouse

The current answer, combining both: data-lake storage economics with warehouse guarantees.

A table format — Apache Iceberg, Delta Lake, or Apache Hudi — sits over Parquet files in object storage and adds a metadata layer:

✅ ACID transactions on files in S3 (via atomic metadata pointer swaps)
✅ Schema evolution — add, rename, and reorder columns safely
✅ Time travel — query the table as of last Tuesday, or roll back a bad write
✅ Efficient upserts and deletes — which raw Parquet cannot do (needed for GDPR erasure)
✅ Partition evolution — change the partitioning scheme without rewriting everything
✅ Open format — Spark, Trino, Snowflake, DuckDB, and Flink can all read the same tables

🎙️ This is the modern default for a data platform, and knowing it is a good currency signal: “I’d use Iceberg tables on S3 rather than a proprietary warehouse — we get ACID, time travel, and schema evolution, while keeping the data in an open format that any engine can query. That avoids locking our data into one vendor’s storage.”

The GDPR angle is genuinely important: “delete all data for user 12345” is nearly impossible across a raw Parquet lake (you’d rewrite every affected file manually) and is a supported operation in a lakehouse table format.

ETL vs ELT

ETL (Extract, Transform, Load) — transform before loading. The historical approach, when warehouse storage and compute were expensive.

ELT (Extract, Load, Transform) — load raw, transform inside the warehouse. The modern default, because cloud warehouses have cheap storage and enormous elastic compute.

ETL:  source → [transform on a separate cluster] → warehouse
ELT:  source → warehouse (raw) → [transform with SQL, in the warehouse] → curated tables

⚖️ Why ELT won:

You keep the raw data, so you can re-derive everything when you find a bug in a transformation — with ETL, a bad transform means the original data is gone.
Transformations are just SQL, which analysts can write and review.
Tools like dbt made transformations version-controlled, tested, and documented — treating analytics code like software.

🚨 “Keep the raw data” is the important part. Every data team eventually discovers a transformation bug that has been silently corrupting a metric for months. If you kept the raw layer, it’s a reprocessing job. If you didn’t, the data is gone.

Getting data in

Method	Latency	Notes
Batch export	Hours	Nightly dump. Simple, heavy, stale.
CDC	Seconds	Read the database replication log. The best approach. → CDC
Event streaming	Seconds	Applications publish events to Kafka; the warehouse consumes
Managed connectors	Minutes	Fivetran, Airbyte — buy rather than build

🚨 Prefer CDC over batch exports. A nightly SELECT * FROM orders hammers production, gets more expensive as data grows, and misses rows created and deleted between runs. CDC streams every committed change from the replication log with near-zero production impact.

The modern data stack

flowchart LR
    P[(Production DBs)] -->|CDC| K[[Kafka]]
    A[App events] --> K
    E[SaaS APIs] -->|connectors| K
    K --> L[Object storage<br/>Iceberg / Delta tables]
    L -->|dbt: SQL transforms| C[Curated marts]
    C --> BI[BI: Looker, Metabase]
    C --> ML[ML training]
    C --> RA[Reverse ETL → back to SaaS tools]

Layered modelling (the dbt convention, and a useful vocabulary):

Bronze / staging — raw, untransformed, exactly as ingested.
Silver / intermediate — cleaned, deduplicated, typed, joined.
Gold / marts — business-facing aggregates that analysts query.

Reverse ETL is worth knowing as a term: pushing warehouse-computed data back into operational tools (a customer’s lifetime value into Salesforce, a churn score into the support tool).

When you don’t need any of this

⚖️ A data warehouse is a significant investment. For a small company:

Read replicas of production + a BI tool covers a lot of ground.
DuckDB is remarkable: an embedded columnar engine that queries Parquet files directly, on a laptop, with no infrastructure at all. It handles hundreds of gigabytes.
Postgres with a columnar extension, or just a well-indexed reporting replica.

🎙️ “At tens of gigabytes I’d point Metabase at a read replica, or use DuckDB over Parquet exports. A warehouse earns its keep when queries span hundreds of millions of rows, when multiple sources need joining, or when analytics load starts affecting production.”

⚖️ Trade-offs

Decision	Gain	Cost
Separate analytics system	Production is protected; queries 10–1000× faster	Another system; data freshness lag; sync pipeline
Column store	Massive scan and compression wins	Terrible at single-row reads and updates
Data lake	Cheap, stores anything, no upfront schema	Becomes a swamp without governance
Lakehouse (Iceberg/Delta)	ACID + time travel + open format	More moving parts than a managed warehouse
ELT over ETL	Raw data retained; SQL transforms; reprocessable	Storage cost; transformation logic in the warehouse
Star schema	Fast analytical joins	Denormalized; SCD complexity
CDC ingestion	Near-real-time, low production impact	Pipeline to operate; schema drift handling

In the real world

BigQuery’s separation of storage and compute was the model the industry followed: store data cheaply, spin up enormous compute only when a query runs, pay per query. Snowflake commercialized the same idea. It’s why “scan a petabyte in 30 seconds” became normal.
dbt changed data teams more than any storage technology — by making transformations version-controlled, tested, and dependency-managed, it brought software engineering practice to analytics. Knowing dbt exists and what it does is worth more in an interview than knowing another warehouse product.
Netflix’s move to Iceberg was driven by the limits of Hive tables at their scale — specifically the need for atomic changes, schema evolution, and correct concurrent writes over object storage. Iceberg came out of that work.

🚨 Interview traps

Running analytics on the production database. Say why it’s a problem: I/O saturation, buffer pool eviction, long-held MVCC snapshots.
Not knowing why columnar storage matters. Column pruning plus compression is the core answer.
Applying normalization rules to a warehouse. Star schemas are deliberately denormalized.
Nightly batch exports when CDC is available.
Not mentioning data freshness. How stale is the warehouse, and does the business care?
Building a data platform for 50 GB. A read replica and Metabase, or DuckDB, is enough.
Ignoring GDPR deletion. “Delete all data for this user” across a raw Parquet lake is genuinely hard — it’s a real argument for a lakehouse format.

🎙️ Soundbites

“Analytics queries scan billions of rows across three columns; production queries fetch one row with all columns. Those want opposite storage layouts, which is why they need separate systems.”
“A column store reads only the columns the query touches and compresses each one 10–100×, so a scan that reads 400 GB in a row store reads a few gigabytes here.”
“I’d feed the warehouse via CDC rather than nightly exports — near-real-time, no load on production, and we don’t miss rows that were created and deleted between runs.”
“Iceberg tables on S3 rather than a proprietary warehouse: we get ACID and time travel while keeping data in an open format any engine can read — and GDPR deletion actually works.”
“Keep the raw layer. Every data team eventually finds a transformation bug that’s been corrupting a metric for months, and with ELT that’s a reprocessing job rather than lost data.”

🛠️ Try it

1. Feel the columnar difference with DuckDB — this takes ten minutes and is genuinely striking:

-- DuckDB, no server required
CREATE TABLE orders AS SELECT
    i AS id,
    (random()*1000)::INT AS customer_id,
    (ARRAY['EU','US','APAC','MENA'])[1 + (random()*3)::INT] AS region,
    (random()*10000)::INT AS amount,
    now() - (random()*365)::INT * INTERVAL '1 day' AS created_at
FROM range(50000000) t(i);

COPY orders TO 'orders.parquet' (FORMAT PARQUET);

-- Now query the Parquet file directly
SELECT region, sum(amount) FROM 'orders.parquet' GROUP BY region;

50 million rows aggregated in about a second, on a laptop. Then compare the Parquet file size against the equivalent in Postgres.

2. Prove column pruning. Run SELECT sum(amount) vs SELECT * over the same Parquet file and watch the bytes read differ by an order of magnitude.

3. Build a tiny lakehouse. Write Parquet files to MinIO, register them as an Iceberg table, then do an UPDATE and a time-travel query. Seeing SELECT * FROM table FOR TIMESTAMP AS OF ... work over object storage makes the value of table formats concrete.

4. Design a star schema. Take an e-commerce OLTP schema and convert it to fact and dimension tables. Notice how much denormalization you deliberately introduce — and how that would be wrong in the production schema.

Check yourself

1. Why is columnar storage so much faster for analytics?

Three compounding effects. **Column pruning:** a query touching 3 of 50 columns reads only those 3, so it reads ~6% of the data instead of 100%. **Compression:** each column holds one type with similar values, so run-length, dictionary, and delta encoding achieve 10–100× — meaning far less data comes off disk. **Vectorized execution and zone maps:** values of one type in contiguous memory can be processed with SIMD instructions, and per-block min/max statistics let the engine skip entire blocks that can't match the predicate. Together these give 10–1000× improvements over a row store, which is a category difference rather than a tuning difference.

2. Why is a star schema deliberately denormalized?

Because the cost model is inverted from OLTP. In production, joins are cheap (small result sets) and duplicated data is dangerous (update anomalies). In a warehouse, data is loaded in bulk and rarely updated in place, so update anomalies barely apply — while joins across billions of rows are genuinely expensive. So dimension tables flatten hierarchies (customer → city → region → country all become columns on `dim_customer`) to reduce join depth. Normalization rules from OLTP modeling actively harm warehouse performance, which surprises people coming from application development.

3. What's the difference between a data lake and a lakehouse?

A **data lake** is raw files in object storage — cheap, stores anything, schema-on-read. It has no transactions, no schema enforcement, no efficient updates or deletes, and no guarantee that a reader sees a consistent snapshot while a writer is running. Without cataloguing it degenerates into a "swamp" nobody trusts. A **lakehouse** adds a table format (Iceberg, Delta Lake, Hudi) — a metadata layer over the same Parquet files that provides ACID transactions, schema evolution, time travel, efficient upserts and deletes (essential for GDPR), and partition evolution. You keep object-storage economics and open formats while gaining warehouse guarantees.

4. Why did ELT largely replace ETL?

Because cloud warehouse storage became cheap and compute became elastic, so the reason to transform *before* loading disappeared. The decisive advantage is that ELT **keeps the raw data**: when you discover a transformation bug that's been corrupting a metric for six months — and every data team eventually does — you re-run the transform over data you still have. With ETL, the original was discarded and the correct values are simply gone. Secondary benefits: transformations are plain SQL that analysts can write and review, and tools like dbt make them version-controlled, tested, and dependency-managed.

5. When do you NOT need a data warehouse?

When the data volume and query complexity are within reach of simpler tools. Under roughly a hundred gigabytes with straightforward reporting, a read replica of production plus a BI tool (Metabase, Superset) covers most needs without any pipeline. DuckDB is remarkable here — an embedded columnar engine that queries Parquet files directly with no infrastructure, handling hundreds of gigabytes on a laptop. A warehouse earns its cost when queries span hundreds of millions of rows, when several data sources must be joined, when many analysts need concurrent access, or when analytical load starts measurably affecting production performance.