The Lakehouse

Recap from Foundations

In Foundations Chapter 07 you met the storage primitives. Quick refresher, because the lakehouse is built entirely out of them:

• Parquet — an open, binary file format for tables. Not human-readable like CSV, but self-describing (it carries the schema and column statistics inside the file).
• Columnar layout — Parquet stores all the values of one column together, rather than row-by-row. An analytics query that touches 3 of 40 columns reads only those 3. That's why it's fast for the wide scans BI and ML do.
• Compression — because a column holds values of the same type (and often similar values), it compresses far better than mixed rows. Files are smaller, so less data moves off disk or network.
• Object storage — S3 and friends: effectively infinite, cheap, durable buckets of files, accessed by key. The default home for large data.

Each of these is great on its own. The trouble is that a pile of Parquet files in a bucket isn't a table — nothing tracks which files belong together, whether a half-written file should be ignored, or what the schema is supposed to be. This chapter is about fixing that gap.

Why a lakehouse

For years you had two choices, and they pulled in opposite directions.

A data warehouse (Snowflake, BigQuery, Redshift) gives you real tables: a schema, ACID transactions, fast SQL, and the trust that comes with them. The catch is that the data lives inside the warehouse's proprietary storage. To use it you first load (copy) it in, and to get it out for, say, a machine-learning job, you copy it out again. You pay for that storage at warehouse prices, and you're somewhat locked to that vendor's engine.

A data lake is the opposite: just files (often Parquet) in object storage. Dirt cheap, infinitely scalable, open formats any engine can read. But it has no tables — it's a swamp of files. No transactions, no enforced schema, no guarantee a reader won't see a half-written file. People called the failed version of this a "data swamp" for good reason.

The lakehouse is the merger: keep the lake's cheap, open, infinite file storage, and add a thin metadata layer on top that makes those files behave like warehouse tables — schema, ACID transactions, the works.

Table formats: what turns files into tables

The "thin metadata layer" has a name: a table format. The three you'll hear are Apache Iceberg, Delta Lake, and Apache Hudi. They all do the same essential job — sit on top of your loose Parquet files and add the database guarantees the lake was missing:

• A manifest — a tracked list of exactly which Parquet files make up the table right now. Engines read the manifest, not the directory, so a half-written or leftover file is simply not listed and never seen.
• ACID transactions — a write either fully commits (the manifest flips to the new file set) or it doesn't. Readers always see a complete, consistent version, never a write in progress.
• Schema evolution — add, rename, or drop columns safely, with the format tracking the change instead of breaking old files.
• Time travel — because each commit produces a new manifest version, you can query the table as of last Tuesday, or roll back a bad write.

Here's the comparison that matters for understanding what they buy you:

Hands-on: build a local lakehouse

We'll take a gold table — daily GPU-rental revenue from our running marketplace — and write it into the lake as partitioned Parquet. Partitioning means DuckDB splits the rows into subfolders by a column's value (here, the month), so a query filtered to one month only opens that month's files. That skip is called partition pruning, and it's the lakehouse's main performance lever.

Step 1 — make a gold table and write it to the lake, partitioned by month. Save this as build_lake.py and run it:

import duckdb

con = duckdb.connect()

# A small gold table: daily revenue per GPU type across 3 months.
# (In your real repo this would be the output of a dbt model.)
con.sql("""
CREATE TABLE gold_daily_revenue AS
SELECT
    d::DATE                         AS day,
    strftime(d, '%Y-%m')           AS month,        -- partition key
    ['A100','H100','L4'][1 + (i % 3)] AS gpu_type,
    round(50 + random() * 200, 2)  AS revenue_usd
FROM range(0, 90) t(i),
     LATERAL (SELECT DATE '2026-01-01' + i AS d);
""")

# Write to the lake as Parquet, one subfolder per month.
con.sql("""
COPY (SELECT * FROM gold_daily_revenue)
TO 'lake/gold_daily_revenue'
(FORMAT parquet, PARTITION_BY (month), OVERWRITE_OR_IGNORE);
""")

print("Wrote partitioned Parquet to lake/gold_daily_revenue/")

python build_lake.py
find lake/gold_daily_revenue -type f      # see the partition layout

Step 2 — query the lake back, filtered to one month. Point DuckDB at the whole directory and let it stitch the partitions into one logical table. Save as query_lake.py:

import duckdb

con = duckdb.connect()

# hive_partitioning=true makes DuckDB read month= from the folder names
# and expose it as a real column we can filter on.
rel = "read_parquet('lake/gold_daily_revenue/**/*.parquet', hive_partitioning=true)"

# Total revenue for just February — note the WHERE on the partition column.
print(con.sql(f"""
    SELECT month, round(sum(revenue_usd), 2) AS feb_revenue
    FROM {rel}
    WHERE month = '2026-02'
    GROUP BY month;
""").fetchall())

python query_lake.py

Step 3 — prove partition pruning is happening. Ask DuckDB to explain how it runs the February query. It should report scanning only the February file, not all three months. You can do this in SQL directly:

-- Run in the DuckDB CLI:  duckdb
EXPLAIN ANALYZE
SELECT sum(revenue_usd)
FROM read_parquet('lake/gold_daily_revenue/**/*.parquet', hive_partitioning=true)
WHERE month = '2026-02';

A word on catalogs

You just hard-coded a path: lake/gold_daily_revenue. That works for one table on your laptop, but how does a BI tool, a notebook, and a Spark job all discover that this folder is a table named gold_daily_revenue in a schema named gold, with these columns and this current manifest? They need a phone book.

That phone book is a catalog. It maps human names (analytics.gold.daily_revenue) to physical locations and to the table format's current metadata. When an engine wants a table, it asks the catalog "where is it and what's its latest version?" and the catalog answers. In production you don't pass file paths around — you pass table names, and the catalog resolves them.

You'll meet these names: the Iceberg REST catalog (an open standard), AWS Glue Data Catalog, and Databricks' Unity Catalog. They differ in features and governance (Unity adds access control and lineage, for instance), but the core job is the same: let any engine find and agree on the same tables. DuckDB pointing at a directory is the laptop-scale version of what a catalog does for a whole organization.

Warehouse, lakehouse, or both

A lakehouse is powerful, but it's not automatically the right choice. The honest answer is "it depends on scale, openness, and who else needs the data."

Notice "both" is increasingly the norm: the warehouse-vs-lakehouse war is ending in a truce where the warehouse is the query engine over open lakehouse tables. For the deeper version of this trade-off at production scale — partition strategy, file sizing, catalog choice — see the senior Systems Design reference, which designs storage for exactly the GPU-marketplace world we've been shrinking onto your laptop.

✓ Check yourself

• Can you explain, in one sentence each, what the lake contributes and what the warehouse contributes to a lakehouse?
• What does a table format (Iceberg/Delta/Hudi) add on top of loose Parquet — and why is the manifest the key piece?
• Why does a filter on the partition column run faster than a filter on a regular column?
• What's the danger of partitioning by a high-cardinality column?

import duckdb
con = duckdb.connect()

# Reuse the gold table, this time partition by gpu_type
con.sql("""
CREATE TABLE g AS
SELECT (DATE '2026-01-01' + (i % 90)) AS day,
       ['A100','H100','L4'][1 + (i % 3)] AS gpu_type,
       round(50 + random()*200, 2) AS revenue_usd
FROM range(0, 300) t(i);
""")
con.sql("""
COPY (SELECT * FROM g) TO 'lake/by_gpu'
(FORMAT parquet, PARTITION_BY (gpu_type), OVERWRITE_OR_IGNORE);
""")

# A query filtered on the partition column -> prunes to one folder
print(con.sql("""
EXPLAIN ANALYZE
SELECT sum(revenue_usd)
FROM read_parquet('lake/by_gpu/**/*.parquet', hive_partitioning=true)
WHERE gpu_type = 'H100';
""").fetchall())

Next

Your gold tables now rest on open, queryable, partitioned storage. But everything so far has been batch — data that arrives in chunks. Next we handle data that never stops arriving. → Streaming Fundamentals