Data Lake vs Data Warehouse — Architecture Guide

Overview

A Data Lake is a centralized storage repository that holds structured, semi-structured, and unstructured data at any scale. Unlike a Data Warehouse, which stores processed, schema-on-write data in rigid tables, a Data Lake stores raw data in its native format on cheap object storage (S3, GCS, ADLS) and applies schema on read.

What typically lands in a lake: clickstream and product events, application and access logs, CDC streams exported from OLTP databases, IoT telemetry, and raw files from partners or vendors — everything too large, too messy, or too early in its lifecycle for a curated schema.

That flexibility comes with a catch: the same lake that lets a data scientist explore raw clickstream data also lets every team dump whatever they have without a catalog, owner, or retention policy. Without governance, lakes degrade into “data swamps” — collections of files nobody can find, trust, or delete.

This guide stays on the practical side of all that: deciding when a lake is the right call, how the Bronze/Silver/Gold layering works, which storage format and partitioning strategy to pick, and how the pieces come together in a real event pipeline. For the evolution that adds ACID tables on top of lake storage, see the Lakehouse Architecture guide.

When to Use a Data Lake

• You need to store diverse data types: JSON, CSV, Parquet, images, videos, logs
• Machine learning workloads require raw, unprocessed data for feature engineering
• Data volume exceeds what a warehouse can hold economically: object storage is an order of magnitude cheaper per terabyte
• You want to defer schema design until data is consumed
• Historical data must be retained cheaply for future analysis or compliance

When NOT to use one: if your data is already structured and your consumers are BI analysts running SQL dashboards, a warehouse is the better default: curated tables, fast OLAP queries, governance built in. A lake in front of that workload adds a transformation layer you don’t need. And never use a lake for OLTP: it has no transactions, no row-level updates (unless you add a lakehouse layer), and no latency guarantees.

Data Lake vs Data Warehouse

Dimension	Data Lake	Data Warehouse
Data types	All (structured, semi, unstructured)	Structured only
Schema	Schema-on-read	Schema-on-write
Users	Data scientists, ML engineers, analysts	Business analysts, BI tools
Query performance	Variable, optimized for batch	Fast, optimized for OLAP
Cost	Lower storage, higher compute	Higher storage, optimized compute
Data quality	Raw, may be unvalidated	Curated, validated, trusted
Scale	Petabyte+	Terabyte to petabyte

Most organizations end up with both: the lake holds raw and ML data, the warehouse serves curated BI workloads. The deciding factor tends to be who consumes the data: teams of analysts who only know SQL are usually better served by a warehouse, while engineers who need raw access are where a lake pays off.

The cost picture favors the lake for retention: S3 runs roughly $23/TB-month for standard storage (less with lifecycle tiers to Infrequent Access or Glacier), while warehouse storage is typically several times that; warehouse compute is billed for performance, not for sitting idle. The trade inverts on query-heavy workloads: ad-hoc lake queries scan more data, so a busy BI workload on Athena or Trino can cost more per month than a right-sized warehouse. Rule of thumb: cheap to keep, expensive to query; put hot analytical surfaces in Gold or the warehouse.

The Lakehouse

The lake-versus-warehouse split used to be a hard either/or decision; it’s been blurring for a while now. Table formats like Delta Lake, Apache Iceberg, and Apache Hudi add ACID transactions, schema enforcement, and time travel directly on top of lake storage, turning object files into real tables that query engines (Trino, Spark, Athena) can update and merge.

The result is a “lakehouse”: lake storage economics with warehouse-grade reliability. It doesn’t eliminate the warehouse (curated serving layers still exist) but it fixes the two worst lake problems: no transactions and no schema guarantees. The Lakehouse Architecture guide covers the pattern in depth.

Between the three formats: Delta Lake has the smoothest Spark integration and is the default on Databricks; Iceberg has the widest engine support (Trino, Flink, Snowflake read it natively) and better partition evolution; Hudi targets upsert-heavy CDC workloads. Unless you have a specific constraint, pick the one your query engine reads best.

Architecture Layers

The medallion architecture organizes a lake into zones, each with a different level of processing:

Raw Zone (Bronze)
    ├── Unprocessed data from sources
    ├── Retained in native format
    └── Cheap, long-term storage

Cleaned Zone (Silver)
    ├── Deduplicated, validated data
    ├── Basic transformations applied
    └── Typed schemas enforced

Curated Zone (Gold)
    ├── Business-ready aggregates
    ├── Optimized for query performance
    └── Used by BI tools and applications

The rules that make this work:

• Bronze is append-only. Never modify or delete raw data: it’s your replay source when a downstream bug is discovered. Retain it indefinitely (or per compliance policy).
• Silver is typed and deduplicated. This is where you enforce schemas, drop malformed rows, and dedupe on natural keys. Most analytical queries should hit Silver, not Bronze.
• Gold is purpose-built. Each Gold table exists for a consumer: a dashboard, an ML feature set, a regulatory report. A Gold table nobody queries is wasted work.

Promotion between zones is a scheduled job, not an automatic flow: an ingestion process (Firehose, a CDC connector, a producer app) writes Bronze continuously, then batch or streaming jobs run Bronze→Silver on a schedule (hourly or daily for batch, micro-batches for streaming) and Silver→Gold on the reporting cadence. Each promotion is a quality gate; data only moves up after it passes validation, which is why “bad data in Gold” almost always means a validation rule was skipped, not that the architecture failed.

Querying the Lake

The lake itself is just files; the query engine is what makes it usable:

Engine	Model	Best For
Athena	Serverless, pay-per-scan	AWS shops, ad-hoc and scheduled queries
Trino	Federated distributed SQL	Joining lake data with warehouses, APIs, other lakes
Spark SQL	Batch compute	Heavy transformations feeding zones
Dremio	SQL + reflections	Accelerated dashboards straight on the lake
DuckDB	Embedded, local	Prototyping, single-node analytics on Parquet

Athena or Trino pointed at Silver covers occasional exploration just fine. If a dashboard hits the lake every few seconds, promote that data to Gold, or to the warehouse, rather than paying per scan forever.

ETL vs ELT

Pattern	Flow	Best For
ETL	Extract → Transform → Load	Data warehouses, strict schema requirements
ELT	Extract → Load → Transform	Data lakes, schema-on-read flexibility

Lakes tilt toward ELT: you land the raw data first and transform it into zones when it’s actually needed. The transform step is where most of the compute cost sits; the ETL Pipeline guide goes deeper on the orchestration side.

# ELT pattern — load raw, transform on demand
from pyspark.sql import SparkSession

spark = SparkSession.builder.appName("DataLakeELT").getOrCreate()

# Extract: Read raw JSON logs from S3
raw_df = spark.read.json("s3://datalake/bronze/events/2026/10/")

# Load: Store as Parquet in the Silver zone
raw_df.write.parquet("s3://datalake/silver/events/", mode="overwrite")

# Transform: Apply schema and aggregations on read
cleaned_df = spark.read.parquet("s3://datalake/silver/events/")
cleaned_df.createOrReplaceTempView("events")

daily_metrics = spark.sql("""
    SELECT
        DATE(timestamp) AS date,
        event_type,
        COUNT(*) AS event_count,
        COUNT(DISTINCT user_id) AS unique_users
    FROM events
    WHERE timestamp >= '2026-10-01'
    GROUP BY DATE(timestamp), event_type
""")

daily_metrics.write.parquet("s3://datalake/gold/daily_metrics/")

Storage Formats

Format	Type	Use Case
CSV	Text	Interchange, human-readable
JSON	Semi-structured	APIs, nested data
Parquet	Columnar	Analytical queries, compression
Avro	Row-based	Streaming, schema evolution
ORC	Columnar	Hive/Spark workloads
Delta Lake	Table layer	ACID transactions on lakes

The defaults that cover most cases:

• Parquet for anything analytical. Columnar layout means queries read only the columns they need: a SELECT date, event_type skips the rest of the file. It compresses well and every major engine reads it natively.
• Avro at the ingestion edge. Row-based, compact, and built for schema evolution: the standard for Kafka-to-lake pipelines.
• Delta or Iceberg when you need updates. Deletes, merges, and UPDATE statements require a table format on top of the files; plain Parquet is immutable.

Partitioning and File Layout

Whether a query ends up reading 50 files or 50,000 comes down almost entirely to partitioning. The usual convention is hive-style directories (s3://lake/silver/events/dt=2026-10-18/) so engines can prune partitions at query time.

• Partition by the column people filter on, almost always the event date. High-cardinality columns (user_id, country in a global product) create thousands of tiny partitions and make things worse.
• Watch file sizes. Thousands of files under 1–10 MB destroy performance: every file is an open/read/close in Spark or Trino. Compact with OPTIMIZE (Delta) or a rewrite job weekly; target 128–512 MB files.
• Keep paths deterministic. zone/entity/dt=YYYY-MM-DD/ is enough; deeper nesting buys little and complicates lifecycle policies.

A worked example: 50M events/day at ~1 KB each is ~50 GB/day of raw JSON. Partitioned by dt, a “last 7 days” query prunes to ~350 GB instead of scanning the whole lake. If each daily partition is written as ~200 MB Parquet files, that’s ~250 files per day, well inside the healthy range. The same data written as 5 MB files means ~10,000 opens per partition, which is exactly the small-files trap.

Delta Lake Example

from delta import configure_spark_with_delta_pip
from pyspark.sql import SparkSession

builder = SparkSession.builder.appName("DeltaLakeExample") \
    .config("spark.sql.extensions", "io.delta.sql.DeltaSparkSessionExtension")
spark = configure_spark_with_delta_pip(builder).getOrCreate()

# Write with ACID guarantees
df.write.format("delta").mode("overwrite").save("/datalake/silver/orders")

# Time travel — query as of a specific version
spark.read.format("delta").option("versionAsOf", 5).load("/datalake/silver/orders")

# Schema evolution
df_with_new_column.write.format("delta") \
    .mode("append") \
    .option("mergeSchema", "true") \
    .save("/datalake/silver/orders")

mergeSchema handles additive changes. For breaking changes — renames, type changes — version the table (orders_v1, orders_v2) and migrate consumers explicitly rather than mutating in place.

Governance and the Data Swamp

Left alone, a lake without governance tends to go swamp-shaped in roughly six months. Five controls keep the swamp at bay.

1. A data catalog (AWS Glue, Unity Catalog, DataHub): every dataset registered there carries an owner, a schema, and a human-readable description. Datasets that never make it into the catalog effectively don’t exist. A minimal catalog entry looks like: silver/events — owner: analytics-team, format: delta, partition: dt, pii: user_id hashed, retention: 2y.
2. Ownership per dataset — each dataset gets a named person who answers for its quality and lifecycle. Data nobody owns is data that eventually gets deleted.
3. Retention policies per zone (Bronze held for compliance windows, Silver a year or two, Gold while consumers use it). Storage is cheap; figuring out what’s still valid is not.
4. Quality gates at ingestion: schema validation plus null checks, so nothing reaches Silver unverified. Tools like Great Expectations or dbt tests (see the dbt model transformations recipe) automate this.
5. Access control per zone: Bronze often contains PII in raw form; restrict it to ingestion jobs and a small data engineering group. Silver and Gold are where analysts and applications get access, ideally to tables where sensitive fields are already hashed or dropped. Granting broad read on Bronze “for convenience” is how leaks happen.

Governance is also the connection to the Data Mesh guide: once teams own their domains, the catalog and zone conventions are what keep domain data discoverable across the org.

Detailed Scenario: Event Pipeline with Spark and Delta Lake

Picture an e-commerce platform doing 50M events a day: clickstream and purchase events come in through Kinesis, land in S3, get promoted through the zones by Spark jobs, and BI queries run against Gold via Athena.

System: E-commerce analytics platform
Volume: 50M events/day (clicks, views, purchases)
Storage: S3 with Bronze/Silver/Gold zones
Engine: EMR with Spark 3.5

Architecture:
  Sources -> Kinesis -> S3 Bronze (raw JSON)
  S3 Bronze -> Spark job -> S3 Silver (Parquet + Delta)
  S3 Silver -> Spark job -> S3 Gold (aggregates)
  S3 Gold -> Athena/Trino -> BI tools

Step 1: Ingest to Bronze
  Kinesis Firehose writes raw JSON to s3://lake/bronze/events/dt=2026-07-11/
  Partition by date (dt) for efficient queries
  No transformation: data exactly as it arrives

Step 2: Bronze to Silver (cleaning)
  $ spark-submit --master yarn \
      --num-executors 20 \
      --executor-memory 8g \
      bronze_to_silver.py

  # bronze_to_silver.py
  raw = spark.read.json("s3://lake/bronze/events/dt=2026-07-11/")
  cleaned = raw.filter("user_id is not null") \
      .withColumn("event_time", to_timestamp("timestamp")) \
      .withColumn("event_date", to_date("event_time")) \
      .dropDuplicates(["event_id"])
  cleaned.write.format("delta") \
      .mode("append") \
      .partitionBy("event_date") \
      .save("s3://lake/silver/events/")

Step 3: Silver to Gold (aggregates)
  silver = spark.read.format("delta").load("s3://lake/silver/events/")
  daily = silver.groupBy("event_date", "event_type") \
      .agg(count("*").alias("total_events"),
           countDistinct("user_id").alias("unique_users"))
  daily.write.format("delta") \
      .mode("overwrite") \
      .option("overwriteSchema", "true") \
      .save("s3://lake/gold/daily_events/")

Step 4: Optimization
  # Compact small files weekly
  delta_table = DeltaTable.forPath(spark, "s3://lake/silver/events/")
  delta_table.optimize().compact(minFileSize="10MB")

  # Vacuum: remove old versions (retain 7 days)
  delta_table.vacuum(retentionHours=168)

Monitoring:
  - CloudWatch: job duration, memory usage, output file count
  - Alert if Silver has > 1000 small files (< 1MB)
  - Alert if Gold not updated in 24h
  - Data quality: Great Expectations validates schema and nulls in Silver

Estimated monthly cost:
  S3 storage (50TB): ~$1,150
  EMR (20 executors x 4h/day): ~$800
  Athena (BI queries): ~$200
  Total: ~$2,150/month

For scheduling jobs like this, the Airflow DAG scheduling recipe covers the orchestration layer, and Spark groupBy aggregation has more transformation patterns.

The first time a promotion job fails in production, the ability to replay from Bronze is what keeps the incident small. If a Silver transform ships a bug (a wrong dedupe key, a timezone mix-up), you fix the job, delete the bad Silver partitions, and re-run from Bronze. Nothing downstream has to be re-ingested because the raw events are still sitting there untouched. It’s also why Bronze stays immutable, with a retention window set by compliance rather than storage cost.

What Works

• Land raw first, transform later: keep Bronze immutable so any downstream bug is replayable
• One format per zone: JSON/Avro in Bronze, Parquet or Delta in Silver and Gold; mixing formats per zone multiplies tooling
• Partition by query pattern, not by source: dt= daily partitions for event data; skip partitioning under ~1 GB/day
• Catalog everything: a dataset without an owner and a schema is tomorrow’s swamp
• Compact on a schedule: weekly OPTIMIZE for hot Silver tables, monthly for Gold
• Register the runnable version: the companion repo has the working pipeline scripts, linked in Further Reading below

Common Mistakes

• The data swamp: dumping everything without cataloging, governance, or retention policies
• No partitioning strategy: querying unpartitioned data lakes is painfully slow; partition by date and/or region
• Using lakes for OLTP: lakes are for analytics, not transactional workloads
• Ignoring data governance: without metadata catalogs and access controls, lakes become unusable
• Small files problem: writing thousands of tiny files kills query performance; compact regularly
• Promoting raw data straight to dashboards: Gold tables exist so consumers never touch unvalidated data
• Deleting Bronze to save costs: the raw zone is the only replay source; use lifecycle policies on old partitions instead

Further Reading

• Runnable companion — medallion pipeline scripts, Delta Lake example, and partitioning layout
• Delta Lake documentation: ACID tables, OPTIMIZE, and time travel on lake storage
• Apache Iceberg: the other major open table format
• Trino documentation: federated SQL over lakes and everything else
• AWS Prescriptive Guidance — Data Lakes: zone naming and S3 layout conventions