Data Lakes & Warehouses

9 min read

In a Nutshell

When you need to store and analyze large volumes of data for business intelligence, machine learning, and reporting, two architectures dominate. A data warehouse stores structured, cleaned, modeled data optimized for fast analytical queries — think curated tables ready for dashboards and reports. A data lake stores raw data of any type (structured, semi-structured, unstructured) cheaply at massive scale, schema applied later when you read it. The classic distinction: a warehouse is schema-on-write (structure it before storing), a lake is schema-on-read (dump it now, structure it when you query). Modern lakehouse architectures blend both. Understanding these is essential for any system with analytics, ML, or reporting needs.

2D minimalistic diagram split in two: left labeled "Data Warehouse" shows clean, structured tables (rows and columns) that were transformed and modeled before loading, feeding dashboards; right labeled "Data Lake" shows a large pool holding mixed raw data of many shapes (JSON, images, logs, tables) stored cheaply, with schema applied only when read for analysis

How It Actually Works

Warehouse vs Lake: The Core Contrast

	Data Warehouse	Data Lake
Data	Structured, cleaned, modeled	Raw, any format (structured → unstructured)
Schema	Schema-on-write (before load)	Schema-on-read (at query time)
Storage	Optimized columnar store	Cheap object storage (S3)
Cost	Higher per TB	Very low per TB
Query speed	Fast (optimized)	Varies (raw data)
Users	Analysts, BI tools	Data scientists, engineers
Flexibility	Rigid (defined schema)	Flexible (store anything)
Examples	Snowflake, BigQuery, Redshift	S3 + Spark/Presto, Hadoop

The Data Warehouse: Structure First

A warehouse ingests data through ETL (Extract, Transform, Load) — you clean and model the data before storing it, so it's query-ready. Warehouses use columnar storage (see Data Storage) optimized for analytical queries that scan many rows but few columns (SUM(revenue) GROUP BY month). The rigidity is the point: well-modeled, trustworthy tables that BI tools and analysts can query fast.

Sources → EXTRACT → TRANSFORM (clean, join, model) → LOAD → Warehouse
                     └─ schema-on-write: structure defined UP FRONT ─┘
Query: fast analytical SQL over clean, columnar tables.

The Data Lake: Store Everything, Decide Later

A lake stores raw data as-is in cheap object storage, deferring structure until you read it. This flexibility is powerful for data science and ML, where you may not know upfront how you'll use the data, and for unstructured data (images, text, logs) that doesn't fit a warehouse's tables.

Sources → LOAD raw (any format) → Data Lake (S3) → TRANSFORM on read
          └─ schema-on-read: structure applied AT QUERY TIME ─┘
Store first, figure out schema later. Cheap and flexible.

The risk: the "data swamp." Without governance, cataloging, and quality controls, a lake becomes a dumping ground where no one can find or trust anything.

ETL vs ELT

The order of operations differs (see Data Pipelines):

	ETL (warehouse-classic)	ELT (lake/modern-warehouse)
Order	Transform before load	Load raw, transform in place
Where transform runs	Separate ETL system	The warehouse/lake engine
Fits	Structured warehouses	Lakes, cloud warehouses (cheap compute/storage)

Modern cloud platforms favor ELT — load raw data, then transform using the warehouse's own scalable compute — because storage and compute are cheap and decoupled.

The Lakehouse: Best of Both

The lakehouse (Databricks Delta Lake, Apache Iceberg, Hudi) adds warehouse-like features — ACID transactions, schema enforcement, and fast SQL — on top of cheap data-lake object storage. It aims to eliminate the need to maintain both a lake and a separate warehouse:

Lakehouse = cheap object storage (lake) + a table format layer that adds:
  - ACID transactions        - schema enforcement/evolution
  - time travel (versioning)  - fast SQL query performance
→ one system for both raw storage AND reliable analytics.

Where They Fit Together

Many organizations use all layers: a lake for cheap raw storage and ML, a warehouse for curated BI, often with the lake feeding the warehouse.

Raw sources → DATA LAKE (raw, cheap, all formats, ML/exploration)
                 │  (curate + model the valuable subset)
                 ▼
              DATA WAREHOUSE (clean, structured, fast BI/dashboards)

2D minimalistic diagram showing a layered modern data architecture: raw data flowing into a data lake (cheap object storage, all formats) at the bottom, a curated subset promoted up into a data warehouse (clean structured tables) for BI dashboards, and a lakehouse layer shown as a table-format overlay that adds ACID and fast SQL directly on the lake

Seeing It in Action

Scenario: Designing the analytics stack for a growing e-commerce company.

Raw data lake (S3) — store everything cheaply:
  - Clickstream events (JSON), app logs, product images, support chat
    transcripts, third-party feeds, DB change streams.
  - Stored raw, as-is, at very low cost. No upfront schema needed.
  - Used by data scientists for ML (recommendations, fraud) and
    exploratory analysis where the schema isn't known in advance.

Data warehouse (Snowflake/BigQuery) — curated BI:
  - The valuable, well-understood subset (orders, revenue, customers,
    inventory) is cleaned and modeled into star-schema tables.
  - Analysts and BI dashboards run fast SQL: "revenue by region by month",
    "top products", "cohort retention" — over trusted, structured data.
  - ELT: raw data loaded, then transformed in-warehouse (dbt) into models.

Governance to avoid a data swamp:
  - A data catalog documents what's in the lake and who owns it.
  - Quality checks and schema contracts on the curated tables.
  - Access controls + PII handling (privacy) on sensitive datasets.

Why not just one?
  ✗ Warehouse only → too rigid/expensive for raw logs, images, and
    exploratory ML; you'd discard data you might later need.
  ✗ Lake only → analysts can't run fast, trustworthy BI over a swamp of
    raw files; dashboards need clean, modeled, performant tables.
  ✓ Both (or a lakehouse) → lake for cheap raw storage + ML flexibility,
    warehouse for fast governed BI. The lake feeds the warehouse.

Lakehouse alternative:
  Use Delta Lake/Iceberg on S3 to get ACID tables + fast SQL directly on
  the lake, potentially serving both needs from one platform.

Why the layered approach is standard: data warehouses and data lakes optimize for opposite ends of a trade-off, so most organizations need both. The warehouse gives analysts fast, reliable queries over clean, modeled data — but its schema-on-write rigidity and cost make it a poor place to dump raw logs, images, and half-understood data you might want later. The lake gives you cheap, flexible storage for everything, ideal for ML and exploration where you can't predict the schema — but raw data alone can't power a trustworthy executive dashboard, and without governance it degrades into an unusable swamp. The common pattern lands the raw data in the lake, then curates and models the valuable subset into the warehouse for BI, getting cheap flexibility and fast governed analytics. The lakehouse trend collapses these into one platform by layering warehouse guarantees (ACID, schema, fast SQL) onto lake-cheap object storage — but whether you use two systems or one, the underlying need is the same: raw flexibility for data science, structured reliability for business intelligence.

Interview Questions

Q: What's the core difference between a data warehouse and a data lake? Hint: A warehouse stores structured, cleaned, modeled data optimized for fast analytical queries — schema-on-write (structure defined before loading). A lake stores raw data of any format cheaply at scale — schema-on-read (structure applied at query time). Warehouse = rigid, curated, fast BI for analysts; lake = flexible, cheap, raw storage for data scientists and ML. They optimize opposite ends of a flexibility-vs-structure trade-off.
Q: What is schema-on-write vs schema-on-read? Hint: Schema-on-write (warehouse): you define and enforce the schema before loading, so data is clean and query-ready but rigid and requires upfront modeling. Schema-on-read (lake): you store raw data as-is and apply structure only when you read/query it, giving flexibility to store anything and decide usage later, at the cost of needing to interpret raw data at query time and weaker guarantees.
Q: What is a "data swamp" and how do you avoid it? Hint: A data lake that has degraded into an ungoverned dumping ground where data is undocumented, untrusted, and undiscoverable, so no one can effectively use it. Avoid it with governance: a data catalog (what's there, who owns it), metadata/documentation, quality checks and schema contracts on curated data, and access controls/PII handling. Flexibility without governance turns a lake into a swamp.
Q: What is a lakehouse and what problem does it solve? Hint: A lakehouse (Delta Lake, Iceberg, Hudi) layers warehouse-like features — ACID transactions, schema enforcement/evolution, time travel, and fast SQL — directly on top of cheap data-lake object storage. It solves the cost and complexity of maintaining both a lake and a separate warehouse by providing one platform that offers cheap raw storage and reliable, performant analytics.
Q: How do ETL and ELT differ, and why do modern platforms favor ELT? Hint: ETL transforms data before loading it (in a separate system) — classic for structured warehouses. ELT loads raw data first, then transforms it in place using the warehouse/lake's own engine. Modern cloud platforms favor ELT because storage and compute are cheap and decoupled, so you can store everything raw and transform on demand with scalable in-warehouse compute — more flexible and often cheaper than pre-transforming.

References

AWS: Data lake vs data warehouse — the distinction explained
Databricks: What is a lakehouse? — the blended architecture
The Data Warehouse Toolkit by Ralph Kimball — dimensional modeling

Dive Deeper

Delta Lake / Apache Iceberg — open table formats for the lakehouse
The modern data stack explained — ELT, warehouses, and transformation
Google BigQuery architecture — a serverless cloud data warehouse