Lakehouse

A lakehouse adds a table layer on top of data-lake storage, giving files in object storage the guarantees of a database: transactions, schemas, updates and deletes, and fast SQL queries. The same copy of the data serves BI dashboards, data engineering and machine learning.

The goal is to stop maintaining two separate systems, a lake for data science and a warehouse for reporting, with pipelines copying data between them.

Diagram

flowchart LR
    src[Sources] --> bronze[(Bronze: raw)]
    bronze --> silver[(Silver: cleaned and joined)]
    silver --> gold[(Gold: business-level tables)]
    subgraph lh [Open table format on object storage]
        bronze
        silver
        gold
    end
    gold --> bi[BI and SQL]
    silver --> ml[Data science and ML]
    gold --> ml

How it works

• Open table formats. Formats such as Delta Lake (opens in a new tab), Apache Iceberg (opens in a new tab) and Apache Hudi (opens in a new tab) add a transaction log and metadata on top of Parquet (opens in a new tab) files.
• ACID transactions. Concurrent reads and writes see consistent data; failed jobs don’t leave half-written tables.
• Schema enforcement and evolution. Tables have schemas that are checked on write and can change in controlled ways.
• Medallion layers. Data commonly flows from bronze (raw) to silver (cleaned) to gold (business-ready).
• Multiple engines, one copy. SQL engines, Spark (opens in a new tab) and ML frameworks read the same tables.

Pros

• One platform for BI and ML: fewer copies and pipelines.
• Low-cost storage: data stays in object storage.
• Open formats: less lock-in than proprietary warehouse storage.
• Reliability of a database: transactions, updates, deletes and time travel.

Cons

• Still maturing: tooling, performance tuning and best practices are evolving.
• Operational work: compaction, file sizing and table maintenance need attention.
• BI performance can trail a dedicated warehouse for highly concurrent dashboard workloads.
• Format choice matters: engines differ in which table formats they support well.

When to use it

• Organizations running both BI and data science on large datasets.
• Teams replacing a lake-plus-warehouse setup that copies data between the two.
• When open formats and avoiding lock-in are priorities.

When to avoid it

• Small or simple reporting needs, where a managed cloud warehouse is less work.
• Teams without data engineering capacity to run the platform.

Common pitfalls

• Skipping table maintenance. Without compaction, small files pile up and queries slow down.
• Treating bronze as a dumping ground with no retention, ownership or quality checks.
• Mixing table formats without a plan, leaving engines unable to read each other’s tables.

Reference implementation

A production-shaped blueprint. Components are described generically first, then with one concrete example stack; the cloud mapping shows equivalents on each major provider.

Runnable code in Java and Python

This design is implemented end to end, from Postgres (opens in a new tab) through Debezium (opens in a new tab), Kafka (opens in a new tab) and Spark to Iceberg tables, with an automated demo. See Run it yourself or go straight to the source on GitHub (opens in a new tab).

Scenario

An e-commerce company wants revenue dashboards updated within minutes and a churn-prediction model trained on the same data. Orders live in a PostgreSQL application database; website clickstream arrives as events. Finance needs figures that reconcile, and customer emails must be hidden from most analysts.

Component blueprint

flowchart LR
    appdb[(Orders DB)] --> cdc[Change data capture]
    cdc --> kafka{{Event stream}}
    clicks[Clickstream events] --> kafka
    kafka --> ingest[Streaming ingest job]
    subgraph lake [Object storage + Iceberg tables]
        bronze[(Bronze:<br/>raw changes)]
        silver[(Silver:<br/>current, cleaned)]
        gold[(Gold:<br/>business metrics)]
    end
    ingest --> bronze
    bronze --> merge[Incremental merge job] --> silver
    silver --> agg[Aggregation jobs] --> gold
    catalog[Table catalog<br/>+ access policies] -.- lake
    gold --> sql[SQL engine] --> bi[Dashboards]
    silver --> ml[ML training]
    orch[Orchestrator:<br/>schedules, checks, maintenance] -.- merge & agg

Component	Responsibility	Vendor-neutral choice	Example stack
Change data capture	Stream every insert, update and delete from the app database	Log-based CDC	Debezium
Event stream	Buffer changes and clickstream events	Durable event log	Apache Kafka
Storage	Hold all table data cheaply	Object storage	Any S3-compatible store
Table format	Transactions, schemas, time travel on files	Open table format	Apache Iceberg
Catalog	Track tables, versions and permissions	Iceberg REST catalog	Apache Polaris (opens in a new tab) or Nessie (opens in a new tab)
Processing	Streaming ingest, merges, aggregations	Distributed engine	Apache Spark
SQL engine	Interactive queries for analysts and BI	Distributed SQL	Trino (opens in a new tab)
Orchestration	Schedules, data-quality checks, maintenance	Workflow scheduler	Apache Airflow (opens in a new tab)

Data flow: from order to dashboard

sequenceDiagram
    participant DB as Orders DB
    participant D as Debezium
    participant K as Kafka
    participant B as Bronze table
    participant S as Silver table
    participant G as Gold table
    participant BI as Dashboard
    DB->>D: order row updated (write-ahead log)
    D->>K: change event: op=u, after={...}
    K->>B: streaming append, every minute
    B->>S: MERGE latest version per order
    S->>G: aggregate revenue by day
    BI->>G: query through Trino

End-to-end latency is a few minutes; bronze keeps every change, so any later table can be rebuilt from it.

Key implementation details

1. Define the tables. Bronze keeps every change event; silver holds one current row per order; both are partitioned by day.

CREATE TABLE lake.bronze.orders_cdc (
    op           STRING,          -- r = snapshot, c = create, u = update, d = delete
    order_id     STRING,
    customer_id  STRING,
    status       STRING,
    amount       DECIMAL(12,2),
    currency     STRING,
    created_at   TIMESTAMP,
    source_ts    TIMESTAMP,       -- when the change was committed in the source
    kafka_offset BIGINT,          -- orders changes to the same order
    raw          STRING,          -- full original event, for replay
    ingested_at  TIMESTAMP
) USING iceberg
PARTITIONED BY (days(ingested_at));

CREATE TABLE lake.silver.orders (
    order_id     STRING,
    customer_id  STRING,
    status       STRING,
    amount       DECIMAL(12,2),
    currency     STRING,
    created_at   TIMESTAMP,
    updated_at   TIMESTAMP
) USING iceberg
PARTITIONED BY (days(created_at))
TBLPROPERTIES ('format-version' = '2', 'write.merge.mode' = 'merge-on-read');

2. Stream changes into bronze. Parse each Debezium event (JSON, schemas disabled) and append it, with a checkpoint so the job resumes exactly where it stopped.

from pyspark.sql import functions as F

after = lambda field: F.get_json_object("value", f"$.after.{field}")

events = (spark.readStream.format("kafka")
          .option("kafka.bootstrap.servers", KAFKA_BROKERS)
          .option("subscribe", "shop.public.orders")
          .option("startingOffsets", "earliest")
          .load()
          .select(F.col("value").cast("string").alias("value"), "offset"))

bronze = events.select(
    F.get_json_object("value", "$.op").alias("op"),
    # Deletes carry the key in "before", everything else in "after"
    F.coalesce(after("order_id"), F.get_json_object("value", "$.before.order_id")).alias("order_id"),
    after("customer_id").alias("customer_id"),
    after("status").alias("status"),
    after("amount").cast("decimal(12,2)").alias("amount"),
    after("currency").alias("currency"),
    after("created_at").cast("timestamp").alias("created_at"),
    F.timestamp_millis(F.get_json_object("value", "$.source.ts_ms").cast("long")).alias("source_ts"),
    F.col("offset").alias("kafka_offset"),
    F.col("value").alias("raw"),
    F.current_timestamp().alias("ingested_at"),
)

(bronze.writeStream.format("iceberg")
    .outputMode("append")
    .option("checkpointLocation", "s3://lake/checkpoints/bronze_orders")
    .trigger(processingTime="1 minute")
    .toTable("lake.bronze.orders_cdc"))

source.ts_ms is the commit time in the source database; the top-level ts_ms is only when Debezium processed the change.

3. Merge into silver incrementally. Each micro-batch keeps only the latest change per order, then applies it. Out-of-order events can’t overwrite newer data.

MERGE INTO lake.silver.orders AS t
USING (
    SELECT * FROM (
        SELECT *, row_number() OVER (
                   PARTITION BY order_id ORDER BY source_ts DESC, kafka_offset DESC) AS rn
        FROM bronze_batch                       -- the current micro-batch
    ) WHERE rn = 1
) AS s
ON t.order_id = s.order_id
WHEN MATCHED AND s.op = 'd' AND s.source_ts >= t.updated_at THEN DELETE
WHEN MATCHED AND s.op <> 'd' AND s.source_ts > t.updated_at THEN UPDATE SET
    status = s.status, amount = s.amount, currency = s.currency, updated_at = s.source_ts
WHEN NOT MATCHED AND s.op <> 'd' THEN INSERT
    (order_id, customer_id, status, amount, currency, created_at, updated_at)
    VALUES (s.order_id, s.customer_id, s.status, s.amount, s.currency, s.created_at, s.source_ts);

Run it from a streaming job that reads bronze incrementally and calls the merge in foreachBatch, so silver is never more than a few minutes behind.

4. Build gold tables for the business.

start = date.today() - timedelta(days=3)          # recompute recent days only

daily = spark.sql(f"""
    SELECT CAST(created_at AS DATE) AS revenue_date,
           currency,
           count(*)                 AS orders,
           sum(amount)              AS revenue
    FROM lake.silver.orders
    WHERE status IN ('PAID', 'SHIPPED', 'DELIVERED')
      AND created_at >= DATE '{start}'
    GROUP BY CAST(created_at AS DATE), currency""")

# Quality gate: publish nothing if any row is wrong
if daily.where("revenue_date IS NULL OR revenue < 0").count() > 0:
    raise ValueError("gold.daily_revenue failed checks; not published")

# Replace exactly the recomputed range, in one atomic commit
daily.writeTo("lake.gold.daily_revenue").overwrite(F.col("revenue_date") >= F.lit(start))

Replacing the whole date range, rather than only the partitions the query returned, also clears a day that no longer has any revenue. In production the orchestrator adds checks against the source, such as today’s order count falling within an expected range of the source database’s count.

5. Maintain the tables. Streaming creates many small files and snapshots. A nightly maintenance job keeps queries fast and storage under control.

-- Combine small files into larger ones
CALL lake.system.rewrite_data_files(table => 'silver.orders');

-- Drop old table versions (keep enough for time travel and recovery)
CALL lake.system.expire_snapshots(
    table => 'silver.orders',
    older_than => TIMESTAMP '2026-11-04 00:00:00',   -- set by the job: now minus 7 days
    retain_last => 20);

-- Delete files no longer referenced by any snapshot
CALL lake.system.remove_orphan_files(table => 'silver.orders');

6. Govern access. The catalog grants analysts read access to gold and to silver views that mask customer_email. Only the data platform team can read raw bronze, because it contains every historical value.

Cloud service mapping

Component	AWS (opens in a new tab)	Azure (opens in a new tab)	Google Cloud (opens in a new tab)
Object storage	Amazon S3 (opens in a new tab)	ADLS Gen2 (opens in a new tab)	Cloud Storage (opens in a new tab)
Event stream	Amazon MSK (opens in a new tab)	Event Hubs (opens in a new tab) (Kafka protocol)	Managed Service for Apache Kafka (opens in a new tab)
Spark	Amazon EMR (opens in a new tab) or AWS Glue (opens in a new tab)	Azure Databricks (opens in a new tab)	Dataproc (opens in a new tab)
Catalog	AWS Glue Data Catalog (opens in a new tab)	Unity Catalog (opens in a new tab) (Azure Databricks)	BigLake (opens in a new tab) metastore
SQL engine	Amazon Athena (opens in a new tab)	Databricks SQL (opens in a new tab)	BigQuery (opens in a new tab) (BigLake tables)
Orchestration	Amazon MWAA (opens in a new tab)	Azure Data Factory (opens in a new tab)	Cloud Composer (opens in a new tab)

On Azure Databricks, Delta Lake is the native table format; the same design applies with Delta tables. Service names change over time; check each provider’s current catalog.

Run it yourself

The lakehouse reference implementation (opens in a new tab) runs the whole flow on a laptop: PostgreSQL, Debezium Server (opens in a new tab), Kafka, Spark 4.1 with Apache Iceberg 1.12, an Iceberg REST catalog, and SeaweedFS (opens in a new tab) as the S3-compatible store. The pipeline is written twice against one shared contract (opens in a new tab). A separate checker reads the same tables with PyIceberg (opens in a new tab), without Spark, to show that any engine can read an open table format.

Language	Stack	Source
Python (opens in a new tab)	PySpark (opens in a new tab), Structured Streaming (opens in a new tab)	python (opens in a new tab)
Java (opens in a new tab) 17	Spark Java API, Structured Streaming	java (opens in a new tab)

Why only two languages? Spark’s streaming and Iceberg support is first-class only on the JVM and in Python. Pipelines are usually written in those; the tables they produce can be read from anything.

git clone https://github.com/architecture-compass/reference-implementations
cd reference-implementations/lakehouse
./demo.sh python    # or: ./demo.sh java

The demo loads the initial orders, then pays one, deletes one and adds one in Postgres, and checks that bronze keeps all 11 change events while silver and gold match the source exactly. It then reruns the pipeline to show nothing is processed twice, reads an older silver snapshot (time travel), and runs table maintenance. Both languages pass all nine checks.

Want to go further? The README’s Break it yourself (opens in a new tab) section has hands-on experiments that show each pattern at work, including what happens when things fail.

Production readiness checklist

• Bronze keeps the full raw event so any table can be rebuilt
• Merges are idempotent: replaying a batch produces the same silver table
• Data-quality checks gate every gold table before dashboards see it
• Daily reconciliation of row counts and totals against the source system
• Compaction, snapshot expiry and orphan-file cleanup scheduled per table
• Personal data masked or excluded outside restricted tables
• Schema changes in the source are detected and alerted, not silently dropped
• Freshness alerts: someone is paged if gold falls more than an agreed time behind

In practice

• Uber’s incremental data lake: adding upserts and incremental reads to cut latency from a day to under an hour.
• LinkedIn builds Kafka: the change-log backbone that feeds many lakehouses.

Related patterns

• Data lake
• Data warehouse
• Data lake vs lakehouse

Glossary: lakehouse

Further reading

• Armbrust et al., “Lakehouse: A New Generation of Open Platforms that Unify Data Warehousing and Advanced Analytics” (opens in a new tab) (CIDR 2021)
• Apache Iceberg documentation (opens in a new tab) and Delta Lake documentation (opens in a new tab)