Data lake vs data warehouse (and the lakehouse)

Two bosses, two kinds of questions

What are a data lake and a data warehouse?

Words you'll meet in this lesson

Lake, warehouse and lakehouse at a glance

Lake vs warehouse at three levels

Side by side, and the three zones of a lake

The medallion zones

File formats you will meet

One messy order, from bronze to the finance report

1. Bronze: store it exactly as it arrived

   {"order_id": 2, "kitchen": "K12", "item": "biryani", "amount": "250"}
   {"order_id": 2, "kitchen": "K12", "item": "biryani", "amount": "250"}

   The same order arrived twice (an app retry) and the amount is text. The lake stores both lines anyway. Rejecting data at this stage would lose evidence we may need later.

2. Silver: clean while reading (schema-on-read)

   raw = [{"order_id": 2, "amount": "250"}, {"order_id": 2, "amount": "250"}]
   seen, silver = set(), []
   for r in raw:
       if r["order_id"] not in seen:
           seen.add(r["order_id"])
           silver.append({"order_id": r["order_id"], "amount": int(r["amount"])})
   print(silver)

   [{'order_id': 2, 'amount': 250}]
   The reader decides the structure: one row per order, amount as an integer. The result is written as Parquet in the silver zone.

3. Gold: load into a warehouse table (schema-on-write)

   CREATE TABLE fact_orders (
     order_id INTEGER PRIMARY KEY,
     kitchen  TEXT REFERENCES dim_kitchen(kitchen),
     amount   INTEGER NOT NULL CHECK (amount > 0)
   );

   The warehouse table has rules. A row with amount −90 is rejected at load time. Finance can trust every row that got in.

4. Model: facts and dimensions

   -- fact_orders(order_id, kitchen, date_key, amount)
   -- dim_kitchen(kitchen, kitchen_name, city)
   -- dim_date(date_key, day, month, is_weekend)

   A star schema: the fact table in the middle, small descriptive tables around it. Any “by city” or “by month” question is a simple join.

5. Query: the finance number

   SELECT k.city, SUM(f.amount) AS revenue
   FROM fact_orders f JOIN dim_kitchen k ON f.kitchen = k.kitchen
   GROUP BY k.city;

   Fast and trusted, because the hard work (de-duplicating, fixing types, rejecting bad rows) happened before. Meanwhile, the data scientist can still go back to bronze for the raw reviews and clicks.

A week of requests: what each team sees, and where the data lives

Step through six frames. Left: the requests Kabir receives. Right: what happens in the lake and the warehouse.

Real-time scenarios inside a data platform

Watch a single record being refined, see how a catalogue saves a new analyst's week, and (advanced) see why the right file layout makes a query 100 times cheaper.

The refinery: one order record from bronze to gold

At 12:31 an order event lands in the lake. Watch it move through the zones over the next hour, and see exactly what changes at each step.

Swamp vs catalogue: a new analyst's first day

Two companies, same lake size. A new analyst is asked: “How many km did riders travel last week?” Watch both days unfold.

Same question, 100× less data read: partitions + columns

The table orders holds one year of data: 1.1 TB, 20 columns. The question: “Revenue in Pune during October?” Watch how file layout changes how much data the engine must read (and how much you pay, on pay-per-scan engines).

Who uses what

Simplified examples; real platforms vary.

Schema-on-write vs schema-on-read

The same four order rows go to two places. The warehouse checks every row against a schema before storing it; the lake stores everything as it is. Later, someone reads the lake and applies their own rules. Compare which rows survive where. Then try the challenges: medallion zones, a star schema, columnar storage, partitions and schema changes.

rows = [
    {"order_id": 1, "kitchen": "K11", "amount": 120},
    {"order_id": 2, "kitchen": "K12", "amount": "250"},                    # text, not a number
    {"order_id": 3, "kitchen": "K13", "amount": -90},                      # impossible value
    {"order_id": 4, "kitchen": "K11", "amount": 120, "coupon": "LUNCH20"}, # new column
]
SCHEMA = {"order_id": int, "kitchen": str, "amount": int}

def warehouse_load(row):                    # schema-on-WRITE: check before saving
    extra = set(row) - set(SCHEMA)
    if extra:
        return f"REJECTED: unknown column {sorted(extra)}"
    for col, typ in SCHEMA.items():
        if not isinstance(row[col], typ):
            return f"REJECTED: {col} is not {typ.__name__}"
    if row["amount"] <= 0:
        return "REJECTED: amount must be > 0"
    return "stored"

lake = []
def lake_store(row):                        # schema-on-READ: store anything now
    lake.append(row)
    return "stored as-is"

for r in rows:
    print(f"order {r['order_id']}: warehouse -> {warehouse_load(r):36} lake -> {lake_store(r)}")

# Later, an analyst READS the lake and applies a schema for their own question
revenue = sum(int(r["amount"]) for r in lake if int(r["amount"]) > 0)
print("Revenue read from the lake (cleaned while reading): Rs", revenue)

• SCHEMA = {"order_id": int, "kitchen": str, "amount": int}The warehouse's contract: exactly these columns, with these types.
• extra = set(row) - set(SCHEMA)A new coupon column is rejected: strict schemas protect quality but make change slower.
• lake.append(row)The lake keeps every row, even broken ones. Nothing is lost, but nothing is checked either.
• int(r["amount"]) for r in lake if int(r["amount"]) > 0Schema-on-read: the reader converts types and filters bad rows at query time, for this question only.

Run it on your PC: build a mini lake and a SQL warehouse

This program creates a real folder-based data lake (lake/bronze, lake/silver), then a real SQL warehouse using SQLite, which comes built into Python. The warehouse table has a CHECK rule, so it rejects the impossible order at load time, while the lake kept it. Save it as mini_lake.py in an empty folder and run it; then open the folders it creates.

import csv, json, os, sqlite3

# ---------- BRONZE: the data lake keeps raw data exactly as it arrived ----------
raw_dir = os.path.join("lake", "bronze", "orders", "date=2026-10-19")
os.makedirs(raw_dir, exist_ok=True)
raw_events = [
    {"order_id": 1, "kitchen": "K11", "item": "thali", "amount": 120},
    {"order_id": 2, "kitchen": "K12", "item": "biryani", "amount": "250"},   # amount as text
    {"order_id": 2, "kitchen": "K12", "item": "biryani", "amount": "250"},   # duplicate (retry)
    {"order_id": 3, "kitchen": "K13", "item": "dosa", "amount": -90},        # impossible amount
    {"order_id": 4, "kitchen": "K11", "item": "thali", "amount": 120, "coupon": "LUNCH20"},  # new field!
]
with open(os.path.join(raw_dir, "part-0001.json"), "w") as f:
    for e in raw_events:
        f.write(json.dumps(e) + "\n")
with open(os.path.join("lake", "bronze", "review_0001.txt"), "w") as f:
    f.write("Thali was hot and tasty, delivery 5 min late.")
print("BRONZE :", len(raw_events), "raw order events + 1 review file saved, nothing rejected")

# ---------- SILVER: schema-on-READ - impose structure when we read ----------
clean, seen = [], set()
with open(os.path.join(raw_dir, "part-0001.json")) as f:
    for line in f:
        r = json.loads(line)
        if r["order_id"] in seen:
            continue
        seen.add(r["order_id"])
        clean.append({"order_id": r["order_id"], "kitchen": r["kitchen"], "amount": int(r["amount"])})
os.makedirs(os.path.join("lake", "silver"), exist_ok=True)
with open(os.path.join("lake", "silver", "orders.csv"), "w", newline="") as f:
    w = csv.DictWriter(f, fieldnames=["order_id", "kitchen", "amount"])
    w.writeheader(); w.writerows(clean)
print("SILVER :", len(clean), "rows after de-duplication and type fixes")

# ---------- GOLD: a warehouse with schema-on-WRITE (rules checked on insert) ----------
if os.path.exists("warehouse.db"):
    os.remove("warehouse.db")
db = sqlite3.connect("warehouse.db")
db.execute("CREATE TABLE dim_kitchen (kitchen TEXT PRIMARY KEY, city TEXT NOT NULL)")
db.execute("""CREATE TABLE fact_orders (order_id INTEGER PRIMARY KEY,
              kitchen TEXT REFERENCES dim_kitchen(kitchen),
              amount INTEGER NOT NULL CHECK (amount > 0))""")
db.executemany("INSERT INTO dim_kitchen VALUES (?, ?)", [("K11", "Pune"), ("K12", "Pune"), ("K13", "Mumbai")])
for r in clean:
    try:
        db.execute("INSERT INTO fact_orders VALUES (?, ?, ?)", (r["order_id"], r["kitchen"], r["amount"]))
    except sqlite3.IntegrityError as err:
        print("GOLD   : rejected order", r["order_id"], "->", err)
db.commit()

query = """SELECT k.city, COUNT(*) AS orders, SUM(f.amount) AS revenue
           FROM fact_orders f JOIN dim_kitchen k ON f.kitchen = k.kitchen
           GROUP BY k.city ORDER BY revenue DESC"""
for city, orders, revenue in db.execute(query):
    print(f"QUERY  : {city:7} {orders} orders  Rs {revenue}")
db.close()

# In a terminal:  python mini_lake.py

Bronze to gold, one line at a time

Three raw rows (one duplicate) move through the zones. Watch the seen set block the duplicate, the amount change from text to a number in silver, and the gold dictionary fill up.

Worked examples: real problems, solved and traced in memory

Four tasks a data engineer does with lakes and warehouses, from deciding where data goes to estimating query savings. Each runs live and is traced.

orders (structured, report), clicks (semi), reviews (unstructured), photos (unstructured), payments (structured, report)

orders    -> lake (raw) + warehouse (clean)
clicks    -> lake
reviews   -> lake
photos    -> lake
payments  -> lake (raw) + warehouse (clean)

O1 placed@10, O1 delivered@40, O2 placed@15, O1 refunded@90, O2 delivered@50

O1 refunded at 90
O2 delivered at 50

5 facts, 3 kitchens, 2 dates

Mumbai Sun 90
Pune Sat 370
Pune Sun 200

365 × 3 GB, 31 days, 2/20 columns, compression 3×, Rs 400/TB

CSV, one folder    scans  1095.0 GB  costs Rs  427.73
CSV, by date       scans    93.0 GB  costs Rs   36.33
Parquet, by date   scans     3.1 GB  costs Rs    1.21

Check yourself

Quick revision

Interview and viva questions

Practice problems

Storage and modelling exercises, from file counting to schema evolution. The last two are business tasks.

a.json, b.json, c.parquet, d.jpg, e.parquet, f.parquet

{'json': 2, 'parquet': 3, 'jpg': 1}

files = ["a.json", "b.json", "c.parquet", "d.jpg", "e.parquet", "f.parquet"]
count = {}
for f in files:
    ext = f.rsplit(".", 1)[1]
    count[ext] = count.get(ext, 0) + 1
print(count)

3 rows

1 OK
2 MISSING {'kitchen'}
3 OK

required = {"order_id", "kitchen", "amount"}
rows = [{"order_id": 1, "kitchen": "K1", "amount": 9},
        {"order_id": 2, "amount": 5},
        {"order_id": 3, "kitchen": "K2", "amount": 7, "note": "x"}]
for r in rows:
    print(r["order_id"], "OK" if required <= set(r) else "MISSING " + str(required - set(r)))

50 TB

Lake      : Rs 90000
Warehouse : Rs 115000

tb = 50
print("Lake      : Rs", tb * 1800)
print("Warehouse : Rs", tb * 2300)

2026-10-19, 2026-10-20

orders/date=2026-10-19/
orders/date=2026-10-20/
orders/date=2026-10-19/

for d in ["2026-10-19", "2026-10-20", "2026-10-19"]:
    print(f"orders/date={d}/")

(O1,v1,placed), (O1,v2,delivered), (O2,v1,placed)

{'O1': 'delivered', 'O2': 'placed'}

updates = [("O1", 1, "placed"), ("O1", 2, "delivered"), ("O2", 1, "placed")]
latest = {}
for oid, version, status in updates:
    if oid not in latest or version > latest[oid][0]:
        latest[oid] = (version, status)
print({oid: s for oid, (v, s) in latest.items()})

old: id, amount · new: id, amount, coupon

['id', 'amount', 'coupon']
[1, 120, None]
[2, 90, 'C10']

rows = [{"id": 1, "amount": 120}, {"id": 2, "amount": 90, "coupon": "C10"}]
cols = []
for r in rows:
    cols += [c for c in r if c not in cols]
print(cols)
for r in rows:
    print([r.get(c) for c in cols])

50,000 × 100 KB, target 128 MB

Total: 4.77 GB
After compaction: 39 files

import math
total_kb = 50_000 * 100
target_kb = 128 * 1024
print("Total:", round(total_kb / 1024 / 1024, 2), "GB")
print("After compaction:", math.ceil(total_kb / target_kb), "files")

facts: K1 300, K2 200, K1 100 · K1 in Pune, K2 in Nashik

{'Pune': 400, 'Nashik': 200}

facts = [("K1", 300), ("K2", 200), ("K1", 100)]
dim = {"K1": "Pune", "K2": "Nashik"}
rev = {}
for k, amt in facts:
    rev[dim[k]] = rev.get(dim[k], 0) + amt
print(rev)

bronze 200 GB × 90 d · gold 2 GB × 1095 d

Bronze: 17.6 TB
Gold  : 2.1 TB

bronze_tb = 200 * 90 / 1024
gold_tb = 2 * 1095 / 1024
print("Bronze:", round(bronze_tb, 1), "TB")
print("Gold  :", round(gold_tb, 1), "TB")

Real-world scenario questions (asked in company interviews)

Lake and warehouse decisions you will face in real teams. Decide your answer first, then open the model answer.