Lakes, Warehouses & Lakehouses

Three shapes of storage

Three architectures, on a spectrum from managed-and-structured to open-and-flexible. Know the trade-off each one makes.

OLTP vs OLAP, row vs columnar

Everything above is OLAP — analytical, scan-heavy, aggregate-many-rows-few-columns. That's a different world from the OLTP source systems (Postgres, MySQL) that serve the app: point lookups and single-row writes, low latency, high concurrency. You don't run analytics on the OLTP database — you replicate out of it (see CDC, Chapter 04) into an OLAP store. The physical reason is layout:

• Row storage (OLTP) keeps a whole record contiguous — perfect for "fetch this user's row," terrible for "sum revenue across a billion rows," which would drag every column off disk.
• Columnar storage (OLAP — Parquet, ORC) keeps each column contiguous. An aggregate reads only the columns it touches, and homogeneous columns compress far better (run-length, dictionary encoding). This is why analytical stores are columnar and why Parquet is the lingua franca of the lake.

Being able to say "analytics is scan-heavy, so we store columnar; the source is lookup-heavy, so it's row-oriented — and CDC bridges them" shows you understand why the storage split exists, not just that it does.

Open table formats

The lakehouse's core technology. A table format is a metadata layer over Parquet/ORC files that tracks which files make up a table at each version, giving you ACID commits, time-travel, and schema/partition evolution without a proprietary store. The three contenders:

Partitioning, clustering & small files

Storage layout is where lake/lakehouse designs succeed or quietly rot. The levers:

• Partitioning — physically split data by a column (usually date) so queries prune whole directories. Partition too coarsely and you scan too much; too finely and you get the small-files problem. Iceberg's hidden partitioning lets you change the scheme later without rewriting query logic.
• Clustering / Z-ordering — sort or co-locate data within files by frequently-filtered columns so predicate pushdown and file skipping work. Snowflake auto-clustering and Delta Z-order are the named mechanisms.
• Compaction — merge many small files into fewer large ones. Essential for streaming/CDC ingest, which naturally produces a flood of tiny files.

The medallion layout

The dominant organizing pattern for a lake/lakehouse: progressively refine data through three quality tiers, each a queryable set of tables.

  BRONZE (raw)            SILVER (cleaned/conformed)      GOLD (curated/served)
  ─────────────          ──────────────────────────      ─────────────────────
  Ingest as-is,     ──▶  Dedup, typed, validated,   ──▶  Business-level marts,
  append-only,           conformed keys, joined,         aggregates, star
  full fidelity,         schema-enforced.                schemas, feature
  replayable.            One source of truth.            tables. BI + ML read.
       │                        │                              │
   CDC / events           dbt / Spark transforms         serving layer below

• Bronze — raw, immutable, append-only landing (CDC change logs, raw events). Full fidelity so you can always reprocess. Never let consumers query it directly.
• Silver — cleaned, deduplicated, typed, conformed. The trustworthy single source of truth most transforms build on.
• Gold — business-ready marts and aggregates, modeled for consumption (star schemas for BI, feature tables for ML).

The MERGE/upsert mechanics that move CDC change logs from bronze to a current-state silver table, and the incremental-model patterns behind gold, live in the companion guide — see Production Data Pipelines · Incremental Processing. Reference the layout here; don't re-teach the merge.

Serving layers

Storage is not consumption. The gold layer feeds several distinct serving patterns, each with its own access shape — a staff-level answer names the serving layer explicitly rather than stopping at "the warehouse."

Storage/compute separation is the enabler for most of this: because data sits once in open object storage, many independent compute engines (a BI warehouse, a Spark ML job, a Trino ad-hoc cluster) read the same tables without copying, and you scale and pay for each separately. That decoupling is the economic argument for the lakehouse — and the reason "one copy, many engines" is worth saying out loud.

The decision matrix

The artifact for the whiteboard. Weight the axes by the requirements you pinned in step 1, then let the weighted winner pick the architecture.

Warehouse-first, lake-first, lakehouse

Key the decision to team maturity and workload mix, not to fashion.

Which of these you build versus buy — a managed warehouse versus a self-run lakehouse, Fivetran versus custom ingestion — is the next fork, and it hinges on cost, team, and lock-in. Continue to 07 · Build vs Buy & the Platform Landscape.

✓ Check yourself

Answer from memory before opening each one.

1. A query sums one column of a 40-column table across a billion rows. Why is a columnar layout a better fit than a row layout, and what workload reverses the answer?

   Show answer

   A columnar store reads only the column it touches, and similar values in one column compress well, so the I/O is a small slice of the table. A row layout keeps a record together, which wins for fetching one user's full row by key. That is why analytics sits in a columnar store fed by CDC from the row-oriented source. See OLTP vs OLAP.

2. A job commits to an Iceberg table every 10 seconds. A month later the same queries are much slower, though the data volume has barely changed. What happened and what do you do?

   Show answer

   Frequent small writes produced thousands of tiny files, and the engine pays a fixed cost per file to open, read the footer and plan. Run compaction on a schedule so the table holds fewer, larger files, and consider a longer commit interval. See partitioning, clustering and small files.

3. What does an open table format add to a directory of Parquet files on object storage, and why does that make time travel and safe concurrent commits possible?

   Show answer

   It adds a metadata layer that records which files make up the table at each version. A commit swaps in a new version of that metadata, so readers see a consistent snapshot, and older versions stay available for time travel. See open table formats.

4. Three analysts run Looker dashboards on 200 GB of data, and a new architect wants an Iceberg lakehouse with its own compaction jobs and query engine. What do you recommend, and when would you revisit it?

   Show answer

   Start warehouse-first with a managed warehouse. The workload is SQL and BI at modest volume, and the lakehouse adds operational weight with no payoff. Revisit when ML workloads need direct file access, or storage cost and lock-in start to hurt. See warehouse-first, lake-first, lakehouse.

5. A data science team trains on raw images and event logs in a lake, while BI reads a warehouse copy. The two disagree on revenue figures. How does a lakehouse address this, and what does it cost?

   Show answer

   It keeps one open, governed copy that both BI and ML read, through a table format that gives ACID commits. You remove the copy and the drift between copies. The cost is more parts to run: the format, compaction and several engines. See three shapes of storage.

6. Why must the bronze tier be append-only and off limits to consumers?

   Show answer

   Bronze is the full-fidelity record you replay from when a silver or gold transform has a bug. If consumers read it directly, its layout becomes a contract, and any rewrite risks losing the history you rely on. Silver is the trusted source for consumers. See the medallion layout.

Takeaway

• The lakehouse puts warehouse guarantees (ACID, time travel, schema evolution) on cheap open object storage, so BI and ML can share one copy.
• Analytics is scan-heavy so it is stored columnar, and the source is lookup-heavy so it is row-oriented. CDC bridges the two.
• A table format is metadata over Parquet files. Pick Iceberg as the neutral default, Delta on Databricks, and Hudi for upsert-heavy record-level work, and say why.
• Layout decides whether a lake works. Partition to prune, cluster to skip files, and compact to stop the small-files problem that streaming ingest creates.
• Bronze is raw and replayable, silver is the trusted source of truth, and gold is modelled for consumption.
• Storage is not consumption. Name the serving layer for each access pattern: BI, interactive OLAP, key-value, feature store, reverse ETL.
• Match the architecture to the team and workload. Warehouse-first is the default early, and a lakehouse is earned by ML and cost.

The same choice also depends on who runs it and what it costs over time. → Build vs Buy & the Platform Landscape

Further reading

• Apache Iceberg table spec — Apache Software Foundation. How snapshots, manifests and metadata files define a table and its atomic commits.
• Delta Lake: High-Performance ACID Table Storage over Cloud Object Stores — Armbrust et al., VLDB, 2020. The transaction log design and the problems it solves on object storage.
• Lakehouse: A New Generation of Open Platforms that Unify Data Warehousing and Advanced Analytics — Armbrust et al., CIDR, 2021. The argument for ending the lake and warehouse split.
• Iceberg table maintenance — Apache Iceberg documentation. Compaction and snapshot expiry, the cures for the small-files problem.
• Apache Parquet documentation — Apache Software Foundation. File layout, encodings and the metadata that lets engines skip data.