Lesson 3 of 4 in this module

3/14 in track

Data lakes versus warehouses

python12 min

Overview

A lake stores cheap files (bronze). A warehouse stores structured SQL tables. A lakehouse uses both in one design.

On this page8 sections

The decision

Where incoming order data lands

An online store in Bengaluru receives thousands of order records every day from checkout services.

  1. Raw checkout files land
  2. Choose storage tier
  1. Data Lake (files) vs Data Warehouse (tables)

Someone has to decide where those records go. Raw files and tidy report tables need different homes.

A data lake keeps any file cheaply. A data warehouse keeps tidy tables you can query with SQL.

Words you will see in this lesson

Six words. They are used in the rest of the lesson.

WordWhat it means
Data lakeA big cheap storage area for files exactly as they arrived (JSON, CSV, Parquet)
Data warehouseA database of tidy tables, built to answer SQL questions quickly
LakehouseA lake with a table layer on top, so you can run SQL on the files
SchemaThe list of columns in a table and the type of each one (text, number, date)
Bronze, silver, goldThree stages of tidiness: raw, cleaned, and ready for reports
GMVGross merchandise value. The total money from orders before returns and fees

What is at stake

Why picking only one storage layer creates problems

If every order goes straight into the warehouse and a buggy update runs, the original data can be lost for good. Warehouse storage also costs more per gigabyte.

Tradeoffs when choosing data lake versus data warehouse storage.

Storage strategyPrimary benefitDownstream operational risk
Warehouse onlyImmediate SQL access for analystsRaw payloads lost after buggy updates; higher storage costs
Lake onlyLow storage cost, raw payload preservedAnalysts cannot run direct SQL queries without extra processing jobs
Both (Medallion)Durable raw archive plus fast SQLRequires coordinating pipelines to keep both layers synchronized

If everything stays in the lake, analysts must write extra code before they can answer even simple questions. So most real platforms use both.

Lakes preserve raw payloads for audits; warehouses serve fast SQL queries for business dashboards.

Option A vs Option B

Loading dock versus retail sales floor

Picture a shop with a loading dock at the back. The data lake is the dock: boxes of whatever arrived, stacked cheaply without sorting.

The warehouse is the shop floor, with labelled aisles where customers find things quickly. A lakehouse is the dock with aisle labels stuck on the boxes.

Lake or warehouse
Data lakeData warehousevsStores files: JSON, CSV, ParquetNo schema forced on writePay for bytes storedNeeds a job to queryStores typed tablesColumns have typesPay for compute that scansAnalysts just write SQL

They answer different questions, which is why most companies run both.

The lake is flexible and cheap to fill, but to ask it a question you must run a job. The warehouse checks the columns when data goes in, and answers SQL straight away.

Watch outA folder of CSV files is not a warehouse

If the only structure is a header row inside each file, you have a lake or a shared folder. A warehouse requires typed columns, access controls, and an analytical query engine that executes SQL. Calling AWS S3, Azure Blob Storage, or Google Cloud Storage a warehouse is a common beginner mistake.

How data moves from lake to dashboard

Incoming files land in the lake exactly as upstream systems generated them before downstream pipelines reshape them.

Three data sources with arrows into one data lake, which has arrows out to a dashboard icon labelled visualization and to a person at a computer labelled transformation
Several sources feed one data lake. From the lake, data goes out to dashboards (visualization) or to jobs that reshape it (transformation).Source: Nekouš, Wikimedia Commons · CC0 (public domain)

Follow the arrows. Raw data goes into the lake first. After that, jobs reshape it or send summaries to dashboards.

Choose by who is asking. Dashboards want tables. Replays and audits want the original files.

ShapeWhat you storeHow you ask questionsWhat you pay for
LakeFiles in any formatSpark, pandas, or a warehouse reading the filesBytes stored (cheap)
WarehouseTyped tablesSQL: SELECT, JOIN, GROUP BYCompute that scans the tables
LakehouseFiles plus table metadataSQL on the lake, or other toolsLake storage plus some compute

Dashboards need tidy tables. Re-running a job, training a model or answering an audit needs the original files.

Medallion layers across storage types

Teams often sort data into three stages: bronze, silver and gold. This is called the medallion pattern.

  1. Bronze: Raw JSON and CSV files in the lake
  2. Silver: Cleaned, typed Parquet files
  1. Gold: Aggregated metric tables in the warehouse

Bronze is usually files in the lake. Gold is usually tables in the warehouse, where analysts read the numbers.

Trace the storage shapes

Watch the same shop orders land as a file, become a clean row, then a daily number. Before each panel, guess whether it lives in the lake or the warehouse.

A worked comparison

Raw JSON in the lake versus typed rows in SQL

Take three orders. First look at how they sit in the lake: three JSON lines, where each amount is still text.

JSONbronze/orders/dt=2026-08-26/part.json (in the lake)
{"order_id": "A-1001", "amount": "499.00", "placed_at": "2026-08-26T09:14:02"}
{"order_id": "A-1002", "amount": "1250.50", "placed_at": "2026-08-26T10:02:41"}
{"order_id": "A-1003", "amount": "89", "placed_at": "2026-08-26T10:30:00"}

You cannot add up amount yet, because it is text like "499.00". A script turns it into a number and writes rows into a warehouse table:

silver.orders in the warehouse: typed columns, ready for SQL

order_idamountorder_date
A-1001499.002026-08-26
A-10021250.502026-08-26
A-100389.002026-08-26

Now the amounts are numbers, so an analyst can add them up with SQL:

SQLDaily GMV from the warehouse table
SELECT order_date, SUM(amount) AS gmv
FROM silver.orders
GROUP BY order_date;

499.00 + 1250.50 + 89.00

order_dategmv
2026-08-261838.50

The original JSON file is still safe in the lake. If the rules change later, the job can be run again from the raw files.

Modeling lake and warehouse layers in Python

A dict can say what each kind of storage holds, and a list can record where each layer lives.

PythonThree words, two layers
WHERE = {"lake": "files", "warehouse": "tables", "lakehouse": "both"}
print("bronze lands as", WHERE["lake"])
print("analysts query", WHERE["warehouse"])

INVENTORY = [
    {"layer": "bronze", "place": "lake", "object": "orders/dt=2026-08-26/part.json"},
    {"layer": "gold", "place": "warehouse", "object": "gold.daily_gmv"},
]
for row in INVENTORY:
    print(row["layer"], "in the", row["place"], "as", row["object"])
bronze lands as files
analysts query tables
bronze in the lake as orders/dt=2026-08-26/part.json
gold in the warehouse as gold.daily_gmv
RememberWhich destination for which request

Audits, raw replays, and unvalidated payloads go to the lake. Dashboards, reporting metrics, and ad hoc SQL queries go to the warehouse. Lakehouse formats like Delta Lake or Apache Iceberg help when multiple batch jobs share lake files and require ACID transactions.

Replaying the order lifecycle across storage tiers

From raw lake JSON to aggregated warehouse metrics
  1. Raw purchase events land as JSON files in the data lake bucket.

    1. Checkout event
    2. Data lake (Bronze)
  2. Lake storage preserves exact incoming payloads without enforcing a schema on write.

    1. Raw JSON preserved
    2. Audit trail
  3. A transformation job parses strings, converts numeric types, and loads warehouse tables.

    1. Batch parsing
    2. Data warehouse (Silver/Gold)
  4. BI dashboards run instant SQL aggregations on typed warehouse tables.

    1. SQL query
    2. Dashboard GMV

4 core takeaways to remember from this lesson:

  1. Data lakes store raw files in any format cheaply using schema-on-read.
  2. Data warehouses store structured, typed tables optimized for analytical SQL queries.
  3. Modern platforms use both: lakes for bronze ingestion and replays, warehouses for gold analytics.
  4. Lakehouses bridge the two by adding ACID table metadata over lake file storage.

Common beginner questions

Is a lake just a cheaper warehouse?

No. A warehouse expects structure and is built to run SQL fast. A lake accepts anything and leaves the structure for later. It is cheaper per byte, and you pay the difference in extra work every time you query it.

Do I need a lakehouse tool on day one?

No. Landing files in AWS S3, Azure Blob Storage, or Google Cloud Storage and loading Amazon Redshift, Azure Synapse, or Google Cloud BigQuery is already a lake plus a warehouse. Lakehouse formats such as Delta Lake and Apache Iceberg start paying off when many jobs need reliable tables over the same files.

Where does pandas fit in?

pandas processes data on one machine. It can read a file from the lake or a result from the warehouse, and it is neither of them. The pandas track teaches the processing. This lesson is about where its input comes from.

What comes next

A lake full of customer files with open permissions invites serious data leaks. Next, Cloud security basics covers the three vital habits: least privilege, encryption at rest, and keeping secrets out of git.

Practice

Run Sample, then do the exercise. 3 requests are listed. For each one, decide if it belongs in a lake, a warehouse, or a lakehouse, and store the 3 words in a list called result. The comparison table in the lesson provides guidance.

Going further: when the schema is checked

A warehouse checks the shape of data when it is written (schema on write). A lake often stores files as they arrive and checks the shape when someone reads them (schema on read). Both are valid. The difference is when you find out a record is wrong, and who has to deal with it.

Four incoming JSON events are shown, one missing its amount and one with the amount as text. Predict which ones a strict reader accepts.

PythonChecking at read time
import json

lines = [
    '{"order_id": 1, "amount": 100}',
    '{"order_id": 2}',
    '{"order_id": 3, "amount": "250"}',
    '{"order_id": 4, "amount": 40}',
]

good, problems = [], []
for line in lines:
    event = json.loads(line)
    amount = event.get("amount")
    if isinstance(amount, (int, float)):
        good.append(event["order_id"])
    else:
        problems.append((event["order_id"], "missing" if amount is None else "text"))

print("accepted:", good)
print("problems:", problems)
accepted: [1, 4]
problems: [(2, 'missing'), (3, 'text')]

The lake accepted all 4 lines when they landed. The problems appeared only when we read: order 2 has no amount and order 3 has text. A warehouse with a typed amount column would have refused those rows at load time, and someone would have had to fix them before the data was usable.

Schema on write (warehouse)Schema on read (lake)
Bad rows foundAt load timeAt query time, possibly months later
FlexibilityLower: new fields need a changeHigher: store first, decide later
RiskLoads fail or reject dataSilent mess unless you validate
Good forTrusted reporting tablesRaw landing, experiments, unknown future uses

Many teams keep both: raw files land in the lake as they arrive, and a validated, typed version is built from them for reporting. Table formats such as Delta Lake and Iceberg add schema checks and transactions to files in a lake, which is why the lakehouse idea exists.

Try a new scenario

Your lake receives a new field, discount, from one producer starting today. Old files do not have it. What happens in a schema-on-write warehouse table and in a lake read, and what do you do about old rows?

Work it out, then open the answer

The warehouse table rejects or ignores the field until you add a column, after which old rows hold NULL for discount. The lake reader sees the field only in new files, and old files return missing values for it unless the reader allows for that. In both cases decide what a missing discount means (none, or unknown) and handle it on purpose, for example with a default.

Interview: lake or warehouse for raw data?

A lake suits raw, varied files you want to keep cheaply and decide about later. A warehouse suits cleaned, typed tables that many people query. Many teams use both, with the lake as the landing zone.

Close the lesson and explain it

Without scrolling up, explain the difference between schema on write and schema on read, and when you find out about a bad record in each.

Practicals · load into the editor

After you read the theory, run these in the pane on the right. They execute in this tab, no cluster.

Finished this lesson?

Mark it as read when you could explain it to someone else.

Rate:
Was this useful?