# Data Warehouses vs Lakes, Plainly

> What a data warehouse actually is, what a data lake (and lakehouse) actually is, and how to choose or combine them without ending up with an expensive bill or a data swamp.


---

# Data Warehouses vs Lakes, Plainly

Somebody on your team says "put it in the warehouse." Somebody else says "no, land it in the lake
first." A third person mentions a "lakehouse" and now you're nodding along while quietly wondering
whether these are three different things, two things, or marketing for the same thing.

Here's the plain version: they're real, distinct ideas, and the confusion is fair because the lines
have genuinely blurred over the last few years. By the end of this guide you'll know what each one
*actually is*, what it's good and bad at, and - the part nobody tells you - how most real organizations
use **both**, on purpose. No hype, no vendor pitch, just where your data lands and why.

## How to read this

- **Need the quick comparison?** Jump to the comparison table at the top of
  [Phase 3: Choosing & Combining](03-choosing-and-combining.md).
- **Want it to finally make sense?** Read in order. Each phase builds one clear mental model before
  comparing anything, so the table in Phase 3 lands instead of just looking like trivia.

## The phases

1. **[The Warehouse](01-the-warehouse.md)** - a database built for *analytics*, not for running your app.
   Structured, schema-on-write, columnar storage that chews through billion-row aggregations. What it's
   great at, and what it costs you.
2. **[The Lake (and Lakehouse)](02-the-lake-and-lakehouse.md)** - store *everything*, raw and cheap, as
   files in object storage. Schema-on-read, brilliant for flexibility and ML - and how it quietly rots into
   a "data swamp" without governance. Plus the lakehouse, where the two ideas converge.
3. **[Choosing & Combining](03-choosing-and-combining.md)** - the straight guidance: it's rarely either/or.
   A fair side-by-side comparison, the common "lake first, then warehouse" pattern, and the failure mode
   (the swamp) that governance exists to prevent.

> This guide is about *where data lands and why*. How data actually gets moved and reshaped between these
> places is its own topic - see [ETL & ELT Pipelines](/guides/etl-elt-pipelines) for that.


---

# The Warehouse - A Database Built for Analytics

You already know what a database is - the thing behind your app that stores users, orders, and sessions.
So when someone says "data warehouse," it's tempting to picture the same thing, just bigger. That
picture will quietly lead you wrong.

A warehouse *is* a database. But it's tuned for a completely different job: answering big analytical
questions across your whole history, fast. Once you see what that job demands, every design choice a
warehouse makes - and every bill it hands you - starts to make sense.

## Two different jobs: running the app vs. understanding the app

**What it actually is.** There are two fundamentally different kinds of database work, and the industry has
names for them.

📝 **OLTP** (Online Transaction Processing) is your app's database: lots of tiny, fast operations - "insert
this order," "fetch user 4827," "update this row." It cares about reading and writing *individual records*
quickly and safely.

📝 **OLAP** (Online Analytical Processing) is the warehouse's job: a few enormous questions over millions or
billions of rows - "total revenue per country per month for the last three years." It cares about *scanning
and aggregating huge ranges* quickly.

**Why people get this wrong.** The instinct is "just run the analytics query on the app database." For a
while you can, and then one day a `SELECT SUM(...) ... GROUP BY ...` over the whole orders table locks
things up and the app slows for real users. The two jobs compete for the same machine. A warehouse exists
so heavy analytical questions live somewhere they can't hurt production - on hardware shaped for exactly
that work.

```text
   OLTP (your app DB)                    OLAP (the warehouse)
   ─────────────────                     ────────────────────
   "fetch order #4827"                   "sum revenue by month, 3 years"
   millions of tiny reads/writes         a few huge scans + aggregations
   row-by-row, low latency               column-by-column, high throughput
   optimized for: keep the app running   optimized for: answer big questions
```

## Schema-on-write: structure decided up front

**What it actually is.** A warehouse is **schema-on-write**: you define the structure - table names, columns,
types - *before* the data goes in. Data that doesn't fit the shape gets rejected or transformed to fit at
load time. The structure is the price of admission.

**What it does in real life.** When you load data, you're committing to a contract: this column is a
`DATE`, that one is an `INTEGER`, this one can't be null. Once it's in, every query can trust that shape.

```console
$ bq query --use_legacy_sql=false \
  'SELECT country, SUM(amount) AS revenue
   FROM `shop.analytics.orders`
   WHERE order_date >= "2026-01-01"
   GROUP BY country
   ORDER BY revenue DESC'
+---------+-----------+
| country | revenue   |
+---------+-----------+
| US      | 1842300.5 |
| DE      |  612885.0 |
| JP      |  498120.0 |
+---------+-----------+
```
*What just happened:* Because every row in `orders` was forced into the same known structure on the way in,
the warehouse could scan the whole table and aggregate it with confidence - no guessing what `amount` means
or whether `country` is even there. That up-front discipline is what makes the query both fast and
trustworthy. (This example uses BigQuery's `bq` CLI; Snowflake and Redshift run the same SQL through their
own clients.)

⚠️ **Gotcha - schema-on-write makes change slow.** The flip side of that contract: when the business adds a
new field, or a source system starts sending data in a new shape, you have to evolve the schema and often
backfill or reload. Structure that's great for querying is friction when reality shifts. Hold onto this
tension - it's exactly what the lake in [Phase 2](02-the-lake-and-lakehouse.md) trades away.

## Columnar storage: why aggregations are fast

**What it actually is.** This is the single most important idea in the phase, so it earns its own section. A
normal app database stores data **row by row** - all of order #4827's fields sit together on disk. A
warehouse stores data **column by column** - every `amount` sits together, every `country` sits together.

**Why people get this wrong.** It sounds like a trivial implementation detail. It isn't - it's the whole
reason warehouses are fast at analytics. Picture the question "what's the total `amount`?"

```text
   ROW STORE (app DB)                COLUMN STORE (warehouse)
   ──────────────────                ────────────────────────
   [id|date|country|amount]          id     : 1, 2, 3, 4, ...
   [id|date|country|amount]          date   : ..., ..., ...
   [id|date|country|amount]          country: US, DE, JP, ...
   [id|date|country|amount]          amount : 30, 12, 9, 45, ...  ◄── read ONLY this
   to sum amount you must touch      to sum amount you read one
   every row (and every column)      tightly-packed column
```

**What it does in real life.** To total `amount`, a column store reads only the `amount` column and skips
everything else - far less data off disk. And because every value in a column is the same type and often
similar (lots of repeated countries, dates in order), it compresses extremely well, shrinking what has to
be read even further.

💡 **Key point.** Columnar storage is *the* reason a warehouse can aggregate over billions of rows quickly.
It's not magic and it's not just "more servers" - it's that analytical queries usually touch a few columns
across many rows, and column storage is built for precisely that access pattern.

**Why this saves you later.** When a warehouse query over a giant table returns in seconds and the "same"
query on your app database would crawl, you'll know it's not luck. And you'll understand the trade-off:
column stores are *slow* at the OLTP job (fetching or updating one whole row means touching many separate
columns), which is exactly why you don't run your app on one.

## What it costs you

A warehouse isn't free, and the costs are worth naming plainly so the bill never surprises you.

- **Money, often per query or per compute-time.** Managed warehouses like BigQuery, Snowflake, and Redshift
  typically charge for the compute a query uses (and/or the data it scans). A careless `SELECT *` over a
  huge table, or a dashboard auto-refreshing every minute, can run up real cost. Concrete pricing changes
  often and varies by vendor - check the current pricing page before you commit; don't trust a number you
  half-remember.
- **Rigidity.** Schema-on-write means structural change has a tax (see the gotcha above).
- **It wants modeled, clean data.** A warehouse is happiest with structured, well-shaped tables. Raw,
  messy, or unstructured data (images, logs, free text, half-known JSON) is an awkward fit - which is the
  exact gap the lake fills next.

🪖 **War story.** A team pointed a popular BI tool at their warehouse and left the dashboards on
auto-refresh for a launch. The dashboards were lovely; the end-of-month bill was not - every viewer
refresh fired a fresh scan over their largest table. The fix was caching and scheduled refreshes, but the
lesson stuck: in a pay-per-query warehouse, *who runs what, how often* is a cost decision, not just UX.

## Recap

1. A warehouse is a **database tuned for OLAP** (big analytical questions), not OLTP (your app's tiny
   reads and writes) - so heavy analytics can't slow down production.
2. It's **schema-on-write**: structure is decided up front, which makes queries fast and trustworthy but
   makes change slow.
3. **Columnar storage** is why aggregations over huge tables are fast - it reads only the columns a query
   needs and compresses them well.
4. The costs are **money** (often per query/compute), **rigidity**, and a **poor fit for raw or
   unstructured data**.

That last cost - the awkwardness with raw, messy, everything-data - is the reason the data lake exists.
That's next.

Watch it animated: [OLTP vs. OLAP](/explainers/OLTPvsOLAP.dc.html)


---

# The Lake (and Lakehouse) - Store Everything, Decide Later

The warehouse in [Phase 1](01-the-warehouse.md) asked you to decide the shape of your data before you
could store it - a reasonable deal for clean, structured tables. But a lot of valuable data doesn't
arrive clean or structured: server logs, clickstreams, images, sensor readings, half-documented JSON from
some third-party API. Sometimes you don't yet know which questions you'll ask, so committing to a schema
up front feels like guessing.

The data lake is the answer to a simple, slightly rebellious idea: *what if we just kept everything,
cheaply, exactly as it arrived, and figured out the structure later?* It's a powerful idea - and a
dangerous one if nobody's tending it.

## What a lake actually is: files in cheap object storage

**What it actually is.** A data lake is, at its core, a big pile of **files in object storage** - services
like Amazon S3, Google Cloud Storage, or Azure Blob Storage. Not a database engine; *storage*. You drop
files in: CSVs, JSON, logs, Parquet, images, whatever. The lake doesn't insist they share a shape.

📝 **Object storage** is cloud storage built for huge numbers of files ("objects"), addressed by a
path/key, designed to be cheap and effectively unlimited - the same kind that backs file uploads and
backups, which is exactly why it's so inexpensive to park enormous amounts of data there.

**Why people get this wrong.** People hear "lake" and picture a fancy queryable system. It's the opposite:
the lake's whole trick is that *storing* is dumb and cheap, and the cleverness happens later, at query
time. Separating storage from compute is the lake's defining move.

```text
        DATA LAKE = object storage (S3 / GCS / Azure Blob)
        ┌──────────────────────────────────────────────┐
        │  /raw/orders/2026-06-19.json                   │
        │  /raw/clickstream/part-0001.parquet            │
        │  /raw/support-tickets/*.csv                     │
        │  /raw/product-images/*.jpg                      │
        │  /raw/iot/sensor-dump.log                       │
        └──────────────────────────────────────────────┘
          one cheap bucket, any format, no shared schema
```

## Schema-on-read: decide the structure when you ask

**What it actually is.** This is the lake's mirror-image of the warehouse's schema-on-write. A lake is
**schema-on-read**: the files have no enforced structure sitting still in storage. You impose a structure
*at the moment you query*, by telling the query engine how to interpret the files.

**What it does in real life.** A query engine (for example, AWS Athena, which runs SQL over files in S3)
reads the raw files and applies the columns and types you declare, right then:

```console
$ aws athena start-query-execution \
  --query-string \
  "SELECT user_id, COUNT(*) AS events
   FROM raw_clickstream
   WHERE event_date = '2026-06-19'
   GROUP BY user_id"
{
    "QueryExecutionId": "b1b2c3d4-e5f6-7890-abcd-1234567890ef"
}
```
*What just happened:* The engine went out to the raw clickstream files sitting in object storage and
interpreted them through the `raw_clickstream` definition *at query time* - structure applied on the way
out, not forced on the way in. The files themselves never had to be reshaped to be stored. That's
schema-on-read in one sentence: structure is a lens you put on at read time, not a cage you build at write
time.

💡 **Key point.** Schema-on-write (warehouse) front-loads the discipline; schema-on-read (lake) defers it.
Neither is "better" - they move the same work to different moments. The warehouse pays up front for
fast, trustworthy queries; the lake stays cheap and flexible now and pays later, every time it has to
make sense of the raw files.

## What the lake is great at

**Flexibility.** Store data whose structure you don't fully know yet, and decide how to read it once you
understand the question - nothing is rejected at the door for not fitting a schema.

**Cost at scale.** Object storage is cheap, so keeping years of raw history is affordable in a way that
loading all of it into a warehouse usually isn't.

**Unstructured and ML-friendly.** Images, audio, free text, raw event logs - the stuff that doesn't fit
neat columns - lives comfortably in a lake. Machine-learning workflows specifically *want* the raw,
unaggregated data: training a model often means reaching for the full-resolution original, not a tidy
summary table.

**Why this saves you later.** When someone asks "can we analyze a signal we weren't tracking on purpose
six months ago?", a warehouse-only shop often can't - the data was never kept. A lake that quietly
retained the raw events can. Keeping the raw material is itself a kind of insurance.

## The risk: the data swamp

The same trait that makes a lake flexible - *anything goes in, no structure enforced* - is exactly how
it rots.

⚠️ **Gotcha - without governance, a lake becomes a "data swamp."** A **data swamp** is a lake nobody can
use: thousands of files with no catalog of what they are, no owners, no documentation, duplicate and
contradictory copies, no idea which dataset is current or trustworthy. The data is technically *there*,
but finding and trusting the right thing becomes so hard that people give up.

**Why it happens.** Schema-on-write forced *someone* to think about structure and meaning at load time.
The lake removes that forcing function. If no one deliberately replaces it with discipline, entropy wins - 
quietly, file by file, until no one can answer "where's the authoritative orders data?"

**What prevents it.** Governance and cataloging - the deliberate discipline a lake doesn't enforce for you:

- A **data catalog** (for example, AWS Glue Data Catalog) recording what each dataset is, its schema, and
  where it lives.
- **Clear ownership** - every important dataset has a team responsible for it.
- **Documented zones** - a raw/landing zone for untouched data and a cleaned/curated zone for trusted,
  ready-to-use data, so "raw mess" and "trusted data" don't blur together.

We come back to governance as *the* deciding failure mode in [Phase 3](03-choosing-and-combining.md).

## The lakehouse: where the two ideas converge

For years this was a real either/or: cheap-but-undisciplined lake, or fast-but-rigid warehouse. The
**lakehouse** is the industry's attempt to get both at once.

**What it actually is.** A lakehouse keeps your data in cheap lake storage (object storage) but adds a
**table layer** on top that gives you warehouse-like behavior - defined tables, schemas, and reliable
updates - directly over those files. Open table formats such as **Apache Iceberg**, **Delta Lake**, and
**Apache Hudi** make this work: they sit over the files and track which files make up a table, its
schema, and how it changes over time.

**What it does in real life.** Instead of "dump raw files and hope" *or* "load everything into a separate
warehouse," you get structured, queryable, governed tables living on the same cheap storage as the raw
data - one place, two behaviors.

```mermaid
flowchart TD
  table["Table layer - Iceberg / Delta Lake / Hudi<br/>schemas, reliable tables, updates"]
  store[("Object storage - S3 / GCS / Azure Blob<br/>cheap raw files")]
  table -->|warehouse-like tables on top of| store
```

📝 **A dose of reality.** "Lakehouse" is also a marketing term, and it isn't automatically the right
answer - it adds its own moving parts and operational complexity. The mental model to keep is the
*architecture*: warehouse-like tables layered over lake-cheap storage. Whether that's the right call for
you is the subject of the final phase.

## Recap

1. A data lake is **files in cheap object storage** (S3/GCS/Azure Blob) - storage, not a database engine.
2. It's **schema-on-read**: structure is applied at query time, not enforced at storage time - the mirror
   image of the warehouse.
3. Lakes are great at **flexibility, cost at scale, and unstructured/ML data**, because they keep raw
   material cheaply and don't reject anything at the door.
4. The risk is the **data swamp** - an ungoverned, uncataloged lake nobody can trust - which is why
   **governance and cataloging** are non-negotiable.
5. The **lakehouse** layers warehouse-like tables (Iceberg/Delta/Hudi) over lake storage, converging the
   two - useful, but not automatically the right choice.

You now understand both landing spots and the hybrid. Last question: which do *you* use, and how do
they fit together?


---

# Choosing & Combining - It's Rarely Either/Or

If you've read the first two phases, you might be bracing for a verdict: warehouse or lake, pick a side.
The real answer is that the question is mostly a false choice. The two solve different problems, and
the most common real-world setup uses **both**, deliberately, each doing the part it's good at.

This phase gives you the fair comparison first, then the pattern that ties them together, then the one
failure that quietly sinks lake projects.

## The straight comparison

This table covers *both* sides fairly - neither is the hero.

| | **Data Warehouse** | **Data Lake** |
|---|---|---|
| **Stores** | Structured, modeled tables | Anything: raw files, JSON, logs, images, Parquet |
| **Structure decided** | On write (schema-on-write), up front | On read (schema-on-read), at query time |
| **Storage** | Managed inside the warehouse | Cheap object storage (S3 / GCS / Azure Blob) |
| **Best at** | Fast aggregations, BI, trustworthy curated data | Flexibility, cheap raw history, ML, unstructured data |
| **Query speed** | Fast on big structured aggregations (columnar) | Depends on file layout and engine; can be slower on raw files |
| **Cost shape** | Higher per stored/queried unit; often pay-per-query/compute | Cheap storage; cost shifts to compute and to *making sense* of the mess |
| **Main risk** | Rigidity; change has a tax; awkward with raw/unstructured data | Becomes a "data swamp" without governance |
| **Who reaches for it** | Analysts, BI teams, finance/exec reporting | Data engineers, data scientists, ML teams |

💡 **Key point.** Read the "Best at" and "Main risk" rows together and the relationship is obvious: the
warehouse's strength (trustworthy, fast structured tables) is the lake's weakness, and the lake's strength
(cheap, flexible raw everything) is the warehouse's weakness. That complementarity is *why* combining
them is so common - not indecision, design.

## The common pattern: land in the lake, model in the warehouse

You don't have to choose - most mature data teams don't. The dominant pattern reads top to bottom:

```mermaid
flowchart LR
  src[sources<br/>app DB · APIs · logs · events] -->|land raw| lake[(Lake - raw<br/>cheap object storage)]
  lake -->|model clean| wh[(Warehouse - curated<br/>structured, fast)]
  wh --> bi[BI dashboards / exec reporting]
  lake -->|raw, full-resolution| ml[Data science / ML]
```

**How it works in practice.**

1. **Land raw in the lake first.** Everything arrives in cheap object storage in roughly its original
   form - cheap insurance against questions you can't predict yet.
2. **Model curated tables in the warehouse.** From that raw material you build clean, structured, trusted
   tables - the ones BI dashboards and finance reports run on, fast and shaped for the questions the
   business actually asks.
3. **Let ML drink from the lake.** Data scientists often skip the curated warehouse tables and work
   directly from the raw lake, because models want full-resolution raw data, not pre-aggregated summaries.

*What this gets you:* the lake's cheap, flexible, keep-everything storage **and** the warehouse's fast,
trustworthy curated tables - each used for what it's genuinely good at, instead of forcing one tool to do
both jobs badly.

📝 **Where the lakehouse fits.** The lakehouse from [Phase 2](02-the-lake-and-lakehouse.md) collapses this
two-system pattern into one: warehouse-like curated tables living directly on lake storage, so you don't
run two separate systems. It can be a great fit, and it can also be more moving parts than a small team
needs - an option, not an obligation.

## A rough rule of thumb (judgment, not law)

This part is opinion, flagged as such - your context can override it.

- **Mostly structured data and BI/reporting needs, a smaller team?** A **warehouse alone** is often
  plenty, the simplest thing that works. Don't build a lake you don't need.
- **Lots of raw, varied, or unstructured data; ML ambitions; or large cheap history to keep?** You'll want
  a **lake**, very likely **feeding a warehouse** for the BI layer.
- **Already running both and tired of two systems?** That's when a **lakehouse** earns a serious look.

Start with the simplest setup that answers your actual questions, and add the other piece when a real
need shows up - not because an architecture diagram online had both boxes.

## The failure that sinks lakes: no governance

We've named the data swamp twice. Here's why it gets the last word: it's the single most common way
these projects fail, and it's entirely preventable.

⚠️ **Gotcha - a lake without governance becomes a data swamp, and a swamp is worse than no lake at all.**
When data lands freely with no catalog, no owners, and no documentation, the lake fills with files nobody
can identify or trust. People can't find the right dataset, can't tell which copy is current, and
eventually stop trusting *any* of it. You've now paid to store data and *also* lost the ability to use
it - strictly worse than never having built the lake.

**Why governance is the deciding factor.** Recall the asymmetry from
[Phase 2](02-the-lake-and-lakehouse.md): the warehouse's schema-on-write *forces* someone to think about
meaning and structure at load time. The lake removes that forcing function in exchange for flexibility.
If you don't consciously replace it with discipline, nothing else does. Governance isn't bureaucracy you
bolt on later; it's the thing that makes a lake a lake instead of a swamp.

**What "governance" concretely means** (not abstract - these are the moves):

- **A data catalog** - a searchable record of every dataset: what it is, its schema, where it lives, who
  owns it (for example, AWS Glue Data Catalog). If you can't search "what data do we have about orders?",
  you don't have governance yet.
- **Clear ownership** - every important dataset has a named team on the hook for its quality and meaning.
- **Defined zones** - at minimum a *raw* zone (untouched, as-landed) and a *curated* zone (cleaned,
  documented, trusted), so nobody mistakes raw mess for production-ready data.
- **Documentation and lifecycle** - what's current, what's deprecated, what can be deleted. Entropy is
  the default; documentation is how you push back.

🪖 **War story.** The saddest data project isn't the one that never got built - it's the lake that got
built *without* a catalog. Years of events faithfully captured, terabytes of genuinely valuable history,
and an analyst who needs "the orders data" facing forty folders named things like `orders_final_v2_REAL`.
The data was all there. Nobody could trust it, so in practice it may as well not have been. The cost of
governance is small and ongoing; the cost of skipping it is a swamp you can't drain.

## Recap

1. Warehouse vs lake is **rarely either/or** - they're complementary, and combining them is a design
   choice, not indecision.
2. The **warehouse** wins on fast, trustworthy, structured BI; the **lake** wins on cheap, flexible, raw,
   ML-friendly storage. Each is the other's weak spot.
3. The dominant pattern: **land raw in the lake, model curated tables in the warehouse for BI**, and let
   **ML work from the raw lake**. The **lakehouse** collapses this into one system - an option, not a
   requirement.
4. Pick the **simplest setup that answers your real questions**, and add the other piece when a genuine
   need appears.
5. **Governance is the deciding factor.** Without a catalog, owners, and zones, a lake rots into a **data
   swamp** that's worse than no lake at all.

You can now hold your own in the "warehouse or lake?" conversation - and point out the real answer is
usually "both, on purpose, with governance."

Watch it animated: [data warehouses vs. data lakes](/explainers/DataWarehouseLake.dc.html)

**Related guides:** [ETL & ELT Pipelines](/guides/etl-elt-pipelines) · [What Is Data Engineering?](/guides/what-is-data-engineering) · [BI Dashboards That Work](/guides/bi-dashboards-that-work)