Data Lake vs Data Warehouse
IntermediateA data warehouse stores structured, transformed data optimised for analytics queries. A data lake stores raw data in any format at massive scale. Modern lakehouses combine both approaches.
Overview
A data warehouse (Snowflake, Redshift, BigQuery) stores structured, cleansed, and transformed data in a star/snowflake schema optimised for analytical queries (OLAP). Data is extracted from sources, transformed (ETL), and loaded into the warehouse. Schema-on-write means data conforms to the schema before storage. A data lake (S3 + Spark, Databricks) stores raw data in any format (JSON, CSV, Parquet, images, logs) at massive scale. Schema-on-read means data is interpreted when queried. Data lakes are cheaper and more flexible but can become "data swamps" without governance. The lakehouse architecture (Delta Lake, Apache Iceberg, Apache Hudi) adds ACID transactions, schema enforcement, and time-travel to data lakes, combining the flexibility of lakes with the reliability of warehouses.
Data Warehouse
Data warehouses store structured, transformed data in a schema optimised for fast analytical queries. ETL pipelines transform raw data before loading.
// Data warehouse architecture
//
// Source Systems → ETL Pipeline → Data Warehouse → BI Tools
// (MySQL, APIs) (Airflow, (Snowflake, (Tableau,
// dbt) Redshift) Looker)
//
// Star schema:
// Fact table: sales (sale_id, date_id, product_id, amount)
// Dimension: dim_date (date_id, year, quarter, month)
// Dimension: dim_product (product_id, name, category)
// Snowflake query — fast analytics on structured data
SELECT d.year, d.quarter, p.category,
SUM(s.amount) AS revenue, COUNT(*) AS transactions
FROM sales s
JOIN dim_date d ON s.date_id = d.date_id
JOIN dim_product p ON s.product_id = p.product_id
WHERE d.year = 2025
GROUP BY d.year, d.quarter, p.category
ORDER BY revenue DESC;
// Characteristics:
// ✅ Fast queries (columnar storage, pre-optimised)
// ✅ Schema-on-write (data is clean and structured)
// ❌ Expensive storage
// ❌ Only handles structured dataData Lake & Lakehouse
Data lakes store raw data in any format cheaply. Lakehouses add warehouse features (ACID, schema) to data lakes, combining the best of both.
// Data lake architecture
//
// Source Systems → Ingestion → Data Lake (S3) → Processing → Analytics
// (Kafka, Raw zone: (Spark, (Presto,
// Firehose) Parquet/JSON Databricks) Athena)
// Curated zone:
// cleaned data
//
// Data Lake zones:
// Raw: landing zone, data as-is from sources
// Cleaned: validated, deduplicated, standardised
// Curated: aggregated, enriched, ready for analytics
// Lakehouse — best of both (Delta Lake / Apache Iceberg)
// ACID transactions on S3-stored Parquet files
// Schema enforcement + evolution
// Time travel (query data as of yesterday)
// Delta Lake example (Spark/Databricks)
spark.read.format("delta").load("s3://lake/orders")
.where("created_at > '2025-01-01'")
.groupBy("status")
.count()
// Time travel
spark.read.format("delta")
.option("versionAsOf", 5) // query snapshot at version 5
.load("s3://lake/orders")
// Comparison:
// Feature | Warehouse | Lake | Lakehouse
// ──────────────────────────────────────────────────────
// Data format | Structured | Any | Any
// Schema | On-write | On-read | Both
// ACID | ✅ | ❌ | ✅
// Cost | High | Low | Medium
// Query speed | Fast | Moderate | Fast
// Time travel | Limited | ❌ | ✅Key Points to Remember
- 1Data warehouse: structured, schema-on-write, fast queries, expensive (Snowflake, Redshift, BigQuery).
- 2Data lake: raw data, schema-on-read, cheap, any format (S3 + Spark/Athena).
- 3Lakehouse: combines lake flexibility with warehouse reliability — ACID, schema, time-travel (Delta Lake, Iceberg).
- 4ETL (Extract-Transform-Load) for warehouses; ELT (Extract-Load-Transform) for lakes.
- 5Data lakes without governance become "data swamps" — implement data cataloguing and access controls.
Interview Questions
Sign in to ask AriaWhat is the difference between a data lake and a data warehouse?
What is a lakehouse and why is it gaining popularity?
When would you choose a data lake over a data warehouse?
How does Delta Lake provide ACID transactions on top of S3?
Design the analytics data platform for a large e-commerce company.
Ask Aria about Data Lake vs Data Warehouse
Your personal AI tutor — ask anything about this concept
Revision Status
Personal Notes
Sign in to save personal notes for this topic.
Discussion
Sign in to join the discussion.