Data Lake vs. Data Warehouse vs. Lakehouse

Load the same two files three ways — as a lake, a warehouse, and a lakehouse — and watch each one handle a schema change and a bad write differently.

Two files, one with an extra field

import duckdb, os, json

os.makedirs("lake", exist_ok=True)
with open("lake/day1.json", "w") as f:
    json.dump([{"id": 1, "name": "Ada", "amount": 25.5},
               {"id": 2, "name": "Grace", "amount": 12.0}], f)
with open("lake/day2.json", "w") as f:
    # the source team added a field mid-stream, with no warning
    json.dump([{"id": 3, "name": "Alan", "amount": 8.75, "currency": "GBP"}], f)

con = duckdb.connect()

Data lake: schema-on-read

rows = con.execute(
    "select * from read_json_auto('lake/*.json', union_by_name=true) order by id"
).df()
print(rows.to_string(index=False))

$ python lake_warehouse.py
== data lake: schema-on-read ==
 id  name  amount currency
  1   Ada   25.50      NaN
  2 Grace   12.00      NaN
  3  Alan    8.75      GBP

No table definition existed before this query ran — DuckDB inferred the union of both files’ shapes on the fly and filled the missing currency values with NaN. That is schema-on-read: maximally flexible, and every reader has to independently decide what a missing column means.

Data warehouse: schema-on-write

con.execute("create table warehouse_orders (id bigint, name varchar, amount decimal(10,2))")
con.execute("insert into warehouse_orders select id, name, amount from read_json_auto('lake/day1.json')")
try:
    con.execute("insert into warehouse_orders select * from read_json_auto('lake/day2.json')")
except Exception as e:
    print(f"insert of day2 into the fixed-schema table failed: {type(e).__name__}: {e}")

== data warehouse: schema-on-write ==
insert of day2 into the fixed-schema table failed: BinderException: Binder Error: table warehouse_orders has 3 columns but 4 values were supplied

The warehouse’s fixed, three-column schema rejects day2’s extra currency column outright — the opposite trade-off. Nothing ambiguous ever lands in the table, but loading it requires an explicit decision (add the column, or drop it) the moment the source shape changes.

Lakehouse: lake-style storage with transactions

con.execute("""create table lakehouse_orders
    (id bigint primary key, name varchar, amount decimal(10,2), currency varchar default 'USD')""")
con.execute("insert into lakehouse_orders select id, name, amount, 'USD' from read_json_auto('lake/day1.json')")
try:
    con.execute("begin transaction")
    con.execute("insert into lakehouse_orders select id, name, amount, currency from read_json_auto('lake/day2.json')")
    con.execute("insert into lakehouse_orders values (2, 'Duplicate Grace', 1.0, 'USD')")  # id 2 already exists
    con.execute("commit")
except Exception as e:
    con.execute("rollback")
    print(f"transaction rolled back: {type(e).__name__}: {e}")

count = con.execute("select count(*) from lakehouse_orders").fetchone()[0]
print(f"rows in lakehouse_orders after the failed transaction: {count}")

== lakehouse: table-format transactions over the same kind of files ==
transaction rolled back: ConstraintException: Constraint Error: Duplicate key "id: 2" violates primary key constraint.
rows in lakehouse_orders after the failed transaction: 2 (day2's valid row never landed either - the whole transaction is atomic)

The currency column exists here — it has a schema like a warehouse table — but day2’s valid row and the bad duplicate-key row were part of one transaction. Because the second statement failed, the whole transaction rolled back, including the first statement that would otherwise have succeeded on its own. That all-or-nothing guarantee across multiple writes is exactly what a plain data lake (raw files, no transaction log) does not give you, and it is the specific problem lakehouse table formats — Delta Lake, Iceberg, Hudi — were built to solve at the scale of many files across distributed storage, not a single local connection.

Practice

 id  name  amount currency region
  1   Ada   25.50      NaN    NaN
  2 Grace   12.00      NaN    NaN
  3  Alan    8.75      GBP    NaN
  4 Isaac    5.00      NaN     EU

con.execute("alter table warehouse_orders add column currency varchar")
con.execute("insert into warehouse_orders select * from read_json_auto('lake/day2.json')")
print(con.execute("select count(*) from warehouse_orders").fetchone()[0])

3

con.execute("begin transaction")
con.execute("insert into lakehouse_orders select id, name, amount, currency from read_json_auto('lake/day2.json')")
con.execute("commit")
print(con.execute("select count(*) from lakehouse_orders").fetchone()[0])

3

Next: turning these raw, event-shaped rows into a queryable star schema.