Data Warehouse vs Data Lake vs Data Lakehouse: Which One to Choose?

Scenario context

Warehouse = structured, clean data for BI and SQL dashboards (Snowflake, BigQuery). Lake = raw, messy data for ML and data science (S3, HDFS). Lakehouse = open table formats (Delta Lake, Iceberg) that bring SQL performance to raw storage — the best of both.

flowchart TD
    SRC[Data Sources APIs  Databases  Event Logs]

    SRC --> ETL_T[Transform & Clean ETL Pipeline]
    ETL_T -->…

This walkthrough focuses on **Real-World Application: Netflix, Airbnb, and Spotify**.

Before making a design choice, clarify:

- Airbnb and the SQL-First Warehouse Airbnb runs Snowflake to power thousands of internal BI reports.
- Analysts write SQL to query pre-aggregated booking, pricing, and host data.
- Because the data is clean and typed, a complex multi-table aggregation across millions of listings completes in seconds.

**Concepts in play:** data-warehousing-lakehouse, spark-data-engineering, caching, transaction-isolation-distributed-acid

## Stage 1 — Choosing Your Architecture: A Three-Question Decision Walk

- You do not need to memorize the theory — answering three questions about your actual use case gets you to the right answer every time.
- Question 1 — Who consumes the data?
- - Business analysts running SQL dashboards → lean toward Warehouse .
- - Data scientists training ML models → lean toward Lake .

*Applies [**data-warehousing-lakehouse**](/concept/data-warehousing-lakehouse) to the decision above.*

```architecture
{
  "version": 1,
  "title": "Choosing Your Architecture: A Three-Question Decision Walk - Subsystem Overview",
  "description": "Decoupled system architecture isolating ingress, stateless logic, and persistent datastores.",
  "view": "high-level",
  "pattern": "microservices",
  "direction": "LR",
  "groups": [
    { "id": "edge", "label": "Edge Traffic Ingress", "tone": "slate" },
    { "id": "services", "label": "Stateless Microservices", "tone": "blue" },
    { "id": "data", "label": "Datastore Infrastructure", "tone": "green" }
  ],
  "nodes": [
    { "id": "gw", "component": "api-gateway", "label": "API Gateway", "group": "edge", "badge": "TLS & Auth" },
    { "id": "app", "component": "workflow-service", "label": "Domain Service", "group": "services", "badge": "Stateless" },
    { "id": "cache", "component": "redis", "label": "Redis Cache", "group": "data", "badge": "Sub-ms Reads" },
    { "id": "db", "component": "postgres", "label": "Transactional DB", "group": "data", "badge": "ACID Log" }
  ],
  "edges": [
    { "from": "gw", "to": "app", "kind": "request", "label": "gRPC Request" },
    { "from": "app", "to": "cache", "kind": "read", "label": "Cache Lookups" },
    { "from": "app", "to": "db", "kind": "write", "label": "Durable Write" }
  ],
  "takeaway": {
    "title": "Architecture Boundary Invariant",
    "body": "Stateless compute nodes scale horizontally while shielding underlying database clusters behind fast cache layers."
  }
}

Observable outcome: - Data scientists training ML models → lean toward Lake .

Stage 2 — Apache Spark + Delta Lake: SQL and ML on the Same Data with PySpark

• Apache Spark is the dominant distributed compute engine for big data analytics and ML pipelines; Delta Lake is an open-source storage layer (built by Databricks) that adds ACID transactions, schema enforcement, time tra…
• Together they form the canonical Python lakehouse stack.
• The core advantage over a plain data lake: multiple PySpark jobs can write to the same Delta table concurrently without corrupting each other, BI analysts can query it with SQL, and ML engineers can read the same Parque…
• dbt (data build tool) is the transformation companion in production lakehouse pipelines: SQL-based dbt models run on top of the Delta table, applying business-logic transformations with full lineage tracking and increme…

from pyspark.sql import SparkSession
from pyspark.sql.functions import col, current_timestamp
from delta import configure_spark_with_delta_pip

# 1. Create a Spark session with Delta Lake extensions
builder = (
    SparkSession.builder
    .appName("LakehouseDemo")
    .config("spark.sql.extensions",
            "io.delta.sql.DeltaSparkSessionExtension")
    .config("spark.sql.catalog.spark_catalog",
            "org.apache.spark.sql.delta.catalog.DeltaCatalog")
)
spark = configure_spark_with_delta_pip(builder).getOrCreate()

# 2. ELT pattern — store raw clickstream events as a Delta table (no pre-transformation)
events_df = spark.read.json("s3://data-lake/raw/clickstream/2024/")

(
    events_df
    .withColumn("ingested_at", current_timestamp())
    .write
    .format("delta")
    .mode("append")                        # ACID-safe: concurrent writers never corrupt files
    .partitionBy("event_date")             # partition pruning speeds up date-range queries
    .save("s3://data-lake/delta/clickstream")
)

# 3. SQL analytics on the same Delta table — BI dashboard query
spark.read.format("delta").load("s3://data-lake/delta/clickstream") \
    .createOrReplaceTempView("clickstream")

spark.sql("""
    SELECT   product_id,
             COUNT(*)              AS page_views,
             COUNT(DISTINCT user_id) AS unique_visitors
    FROM     clickstream
    WHERE    event_date >= '2024-01-01'
    GROUP BY product_id
    ORDER BY page_views DESC
    LIMIT    10
""").show()

# 4. Time travel — query the table as it existed 7 versions ago (ML debugging / audits)
historical_df = (
    spark.read.format("delta")
    .option("versionAsOf", 5)              # or .option("timestampAsOf", "2024-01-15")
    .load("s3://data-lake/delta/clickstream")
)
print(f"Historical version row count: {historical_df.count()}")

# 5. MERGE (upsert) — update existing records, insert new ones
#    This operation is impossible on plain S3 Parquet without Delta Lake
spark.sql("""
    MERGE INTO clickstream AS target
    USING     updates      AS source
    ON        target.event_id = source.event_id
    WHEN MATCHED     THEN UPDATE SET *
    WHEN NOT MATCHED THEN INSERT *
""")

Observable outcome: dbt (data build tool) is the transformation companion in production lakehouse pipelines: SQL-based dbt models run on top of the Delta table, applying business-logic transformations with full lineage tracking and increme…

End-to-end outcome

Airbnb and the SQL-First Warehouse Airbnb runs Snowflake to power thousands of internal BI reports.

Alternatives considered

The source focuses on one primary design. A second architecture is not compared, so no alternative is invented here.

Failure and degraded modes

Trade-offs & Failure Modes: Trade-offs, Failure Modes & Decision Guide: Which One to Pick

• Detection: In practice, many large organizations run all three: a warehouse for reporting, a lake for raw archival and ML, and a lakehouse for the intersection.
• User or operator impact: In practice, many large organizations run all three: a warehouse for reporting, a lake for raw archival and ML, and a lakehouse for the intersection.

Five Lessons Learned From Real Data Architecture Failures

• Detection: A data lake without a catalog becomes a data swamp.
• User or operator impact: A data lake without a catalog becomes a data swamp.