#046: Data Warehouse vs Data Lake, explained

architecture Jul 19, 2023

A data lake and a data warehouse solve two different problems in the same pipeline. A data lake is the central landing zone where raw source data arrives in whatever shape it comes in: JSON, CSV, text files, video files, database extracts. A data warehouse comes after it, taking that raw pile and turning it into a single source of clean, organized, modeled data that people can actually report on. The lake is optimized for getting everything into one place, constantly and cheaply. The warehouse is optimized for making that data consistent, relatable and fast to query. In most architectures the question is not which one, it is the order you build them in.

Key takeaways

A data lake is a central landing zone for raw source data. It is unstructured by design and holds files in every format your sources produce.
A data warehouse is the organized layer built from that raw data: separate schemas and tables, clean models, business logic applied.
If you think about it linearly, the lake comes first and the warehouse comes after. Data lands raw, then gets transformed on a cadence.
The lake is loaded continuously. The warehouse is typically refreshed on a schedule, hourly or daily, by pulling from the lake.
You cannot report off a lake in any reliable way. Nothing in it is relatable until a warehouse gives it structure and keys.
The storage layer itself is a choice, not a definition. An S3 bucket, Azure storage or a cloud database like Snowflake can all host a lake.
Both exist for one reason: consolidating scattered data so the business can make better decisions from it.

Why we build any of this in the first place

Before comparing the two, it helps to back up and ask what a data architecture is even for. I come back to three reasons, and every design decision downstream traces to one of them.

Consolidate. Almost every company generates data from a pile of different places: internal applications, third party tools, spreadsheets, vendor exports. Step one is getting it into one place.
Understand what happened. Once it is together, you can see the events, trace them, and analyze them as one picture instead of a dozen disconnected ones.
Make better decisions. This is the one that summarizes the other two. If the business is not making better calls because of this work, there is no reason to do it.

A data lake and a data warehouse are each answering a different part of that list. Keep the list in mind and the difference between them stops being abstract.

What a data lake is

The data warehouse is the better known term, but I like starting with the lake because it comes first in the flow.

A data lake is the central landing zone for all of your source system data. Everything gets dumped there, in whatever format it arrives, with no requirement that it be consistent or structured.

What ends up in it

In practice a lake holds a genuine mix of formats. JSON payloads from APIs, CSV exports, flat text files, images and video, raw table extracts from an operational database.

None of it is normalized against anything else. That is the point. You are not making decisions about shape yet, you are making sure nothing is lost.

Where it physically lives

The lake is a role in the architecture, not a specific product. I have seen it built on all of these:

An Amazon S3 bucket, probably the most common version
Azure storage or the equivalent on another cloud
A cloud database like Snowflake or BigQuery, with the raw data loaded into landing tables

Pick whichever fits the rest of your stack. The function is identical.

Why the lake became standard

Volume is the short answer. We generate more data than we used to, from more systems, and the old pattern of waiting an hour or a day for a batch ETL job to transform everything on the way in stopped making sense.

The modern pattern inverts it. Load constantly into the lake, then pull from the lake on whatever cadence you need and transform there. Ingestion no longer waits on transformation.

What a data warehouse is

Everything in the lake is unstructured and in different forms. It is not relatable in any real sense, so you cannot report off it.

That gap is what the data warehouse fills. Its job is to take that raw data and turn it into a single source of clean, formatted, organized data.

What it actually looks like

Usually a separate database with its own schemas and tables, deliberately kept apart from the raw layer. Inside it, you decide the structure rather than inheriting whatever the source happened to send.

You update it on a cadence: once an hour, once a day, whatever the business needs. Each run pulls from the lake, runs transformations and applies business logic.

How people structure it

There is no shortage of opinions here, and you can spend a long time arguing about which is best. The common approaches include:

Dimensional modeling and the Kimball star schema
Normalized designs borrowed from transactional database thinking
Denormalized or flattened wide tables built for query speed

Whichever you pick, the goal is the same: one organized form of your raw data that everybody reads from.

The side benefit people forget

Organization is the obvious win. Consistent naming, a predictable shape, something an analyst can interpret without asking you what a column means.

Performance usually improves too. You now have real keys and relationships instead of loosely related files, so the engine has something to work with.

This is also where concepts like facts, dimensions and slowly changing dimensions enter the picture. Those are modeling decisions, and they only become possible once a warehouse layer exists.

What happens on each refresh

A warehouse run is not a copy. Each cycle pulls the raw data from the lake, applies transformations to it, and layers in the business logic that turns source fields into company definitions.

That is where a revenue number stops being whatever the source system called it and becomes the number your finance team recognizes.

How the two fit together

The cleanest way to hold this is as a sequence rather than a choice.

Lake. Raw data from every source, landed continuously, untouched.
Warehouse. Cleaned, modeled, business-ready tables built from that raw data on a schedule.
Reporting. Dashboards and analysis that read from the warehouse, never from the lake.

Both are critical components of a modern data stack, and they are not competing with each other. One preserves everything, the other makes a usable subset of it trustworthy.

Why the order matters

Keeping them separate means a change in a source system cannot break your reporting on its own. The raw data still lands, and you fix the transformation afterward on your own schedule.

Collapse the two into one step and every source change becomes an ingestion outage. That separation is the real reason the pattern caught on, more than any storage cost argument.

Key terms

Data lake

A central landing zone that holds raw source data in whatever format it arrives, with no requirement that it be structured or consistent.

Data warehouse

A structured database layer built from raw data, with clean schemas, applied business logic, and models designed for reporting.

Landing zone

The destination where source data is written on arrival, before any transformation has been applied to it.

Single source of truth

One organized, agreed-upon version of a company's data that every report and every team reads from instead of recalculating their own.

Load cadence

How often a layer refreshes. A lake is usually loaded continuously, while a warehouse is rebuilt on a schedule such as hourly or daily.

Common questions

Do I need a data lake if I already have a data warehouse?

Not always, but it helps. Without a landing zone you are transforming data on the way in, which means a schema change or a bad load can break ingestion itself. A lake separates the act of capturing data from the act of shaping it.

Can Snowflake or BigQuery be both the lake and the warehouse?

Yes, and plenty of teams run it that way. You load raw data into landing tables in one schema and build your modeled tables in another. The two layers are still logically separate even though they share a platform.

Is a data lake the same as a data swamp?

A data swamp is what a lake becomes with no governance. The data is still all there, but nobody knows what is in it, who owns it or whether it is current. The fix is cataloging and ownership, not abandoning the lake.

Which comes first when you are building from scratch?

The lake, because it is simpler and it stops data loss immediately. Get your sources landing somewhere reliable, then build warehouse models on top of the ones the business is asking about first.

Can business users query the data lake directly?

I would not plan on it. Raw files are inconsistently shaped and not relatable to each other, so any answer out of them is fragile. Point people at the warehouse, which exists precisely so they do not have to interpret raw data.

Final takeaway

When I walk a small team through their architecture, the confusion is almost never about which tool to buy, it is about which layer a given problem belongs to. Land everything raw in the lake, shape only what the business asks for in the warehouse, and most of those arguments resolve themselves.

Additional Free Resources

Starter Guides & Checklists

Explore additional free resources built on the same patterns I use with real clients so you can build your own with structure and confidence. Topics include data architecture, modeling and more specifically for small data teams.

Browse Resources