Warehouses vs Data Lakes

Your Data Storage Dilemma

Imagine your company is growing fast. You’re collecting data from everywhere: web analytics tracking user clicks, mobile apps sending session events, your CRM logging customer interactions, payment systems recording transactions, and IoT sensors streaming device telemetry. It’s a deluge of data.

Now the requests start coming in:

• The CEO wants a dashboard showing sales by region, customer lifetime value, and churn trends—delivered every morning at 6 AM.
• The data science team wants raw, unfiltered data to train machine learning models that predict customer behavior.
• The finance team needs structured, auditable reports for compliance and revenue recognition.
• The product team wants to experiment with real-time recommendations based on user behavior.

You sit in the architecture meeting and someone asks: “Do we build a data warehouse, a data lake, or both?”

This is one of the most consequential decisions in data infrastructure. Get it wrong, and you’ll either build an inflexible system that can’t evolve, or a chaotic swamp where nobody knows where data lives or what it means. Get it right, and you’ve unlocked insights that competitors can’t match.

Let me break down what each approach offers, and when to use them.

What is a Data Warehouse?

A data warehouse is a purpose-built system optimized for analytical queries on structured, historical data. Think of it as a factory floor: raw materials (transactions, events) come in, they’re carefully processed, refined, and organized into finished goods (reports, dashboards) that business stakeholders consume.

Key Characteristics

Schema-on-Write: Data is transformed before it enters the warehouse. A raw transaction event gets parsed, validated, deduplicated, and enriched. Only clean, conforming data lives in the warehouse.

Structured Format: Everything is organized into tables with predefined columns, types, and relationships. A sales transaction has columns like order_id, customer_id, product_id, amount, timestamp.

OLAP Optimization: The warehouse is built for analytical queries (OLAP = Online Analytical Processing). Queries like “total revenue by product category for Q4” scan millions of rows and aggregate. Warehouses use columnar storage (store data column-by-column, not row-by-row) because analytical queries only touch a few columns, not entire rows.

Star and Snowflake Schemas: Data is organized into fact tables (events, transactions) and dimension tables (customers, products, dates). This structure makes queries fast and reporting intuitive.

-- Fact table: Sales
CREATE TABLE sales_fact (
  sale_id INT,
  date_id INT,
  customer_id INT,
  product_id INT,
  amount DECIMAL(10, 2),
  quantity INT
);

-- Dimension table: Customer
CREATE TABLE customer_dim (
  customer_id INT PRIMARY KEY,
  name VARCHAR(255),
  segment VARCHAR(50),
  country VARCHAR(100),
  signup_date DATE
);

-- Analytical query
SELECT
  c.segment,
  DATE_TRUNC('month', d.date) AS month,
  SUM(f.amount) AS total_revenue
FROM sales_fact f
JOIN customer_dim c ON f.customer_id = c.customer_id
JOIN date_dim d ON f.date_id = d.date_id
WHERE d.date >= '2024-01-01'
GROUP BY c.segment, DATE_TRUNC('month', d.date)
ORDER BY month DESC, total_revenue DESC;

ETL Pipeline: Data flows through Extract (pull from source systems), Transform (clean, deduplicate, enrich), Load (write to warehouse). The Transform step is heavyweight—it’s where data quality is enforced.

Popular Warehouse Solutions

• Snowflake: Cloud-native, separates compute and storage, excellent for scaling, strong for multi-tenant scenarios.
• Amazon Redshift: Columnar, MPP (Massively Parallel Processing), integrates with AWS ecosystem.
• Google BigQuery: Serverless, strong SQL engine, built-in machine learning functions.
• Azure Synapse: Integrates with Microsoft cloud, good for enterprises already in Azure.

What is a Data Lake?

A data lake is a centralized repository for storing any type of data—structured, semi-structured, or unstructured—in its raw form, at massive scale.

Instead of forcing data into a predefined structure, a data lake says: “Store everything first, figure out what it means later.”

Key Characteristics

Schema-on-Read: Data lands as-is (schema is inferred when you query it). A raw JSON event from your mobile app gets stored exactly as it arrived. When an analyst wants to use it, they parse the JSON, extract relevant fields, and handle variations in the schema.

Any Format: Parquet files, ORC, Avro, JSON, XML, images, videos, raw logs. A data lake doesn’t care. This makes it perfect for organizations with diverse data sources.

ELT Pipeline: Data flows through Extract (pull from sources), Load (store raw data), Transform (when you query it). The Transform is lightweight—data is transformed on-demand.

Flexible and Evolving: New data sources? Add them to the lake. New fields in your event schema? They land in the lake without breaking anything. Later, when you understand the data better, you can catalog and organize it.

Cost Efficient: Storage is cheap (S3 costs ~$0.023 per GB/month). Compute is decoupled—you pay for it only when you query. You can store 10 years of raw data and only pay for the gigabytes used.

# Reading raw JSON events from a data lake using Spark
from pyspark.sql import SparkSession

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

# Load raw JSON events
events_df = spark.read.json("s3://my-data-lake/mobile-app-events/2024/*/events.json")

# Flexible schema-on-read: parse nested JSON
user_events = events_df.filter(
    (events_df.event_type == "purchase") &
    (events_df.timestamp > "2024-01-01")
).select(
    "user_id",
    "event_type",
    "timestamp",
    "details.product_id",
    "details.amount"
)

user_events.groupBy("product_id").agg({"amount": "sum"}).show()

Architecture: Distributed Storage + Catalog

Data lakes typically sit on distributed file systems (HDFS, S3, Azure Data Lake Storage). A catalog service (AWS Glue, Apache Hive Metastore) tracks what data exists, its schema, and metadata.

graph LR
    A["Data Sources<br/>Apps, APIs, Logs, IoT"] -->|ELT| B["Distributed Storage<br/>S3, HDFS, Azure Blob"]
    B -->|Query| C["Compute Engines<br/>Spark, Presto, Athena"]
    B -->|Catalog| D["Metadata Service<br/>Hive, Glue, Atlas"]
    C -->|Results| E["Analytics<br/>BI Tools, ML Pipelines"]
    D -->|Schema Info| C

The Lakehouse: Best of Both Worlds

Here’s the problem: data warehouses are governed but inflexible. Data lakes are flexible but chaotic.

What if you could have the governance and performance of a warehouse with the flexibility and cost of a lake?

This is the lakehouse pattern. Technologies like Delta Lake (Databricks), Apache Iceberg (Netflix), and Apache Hudi (Uber) add warehouse-like features to data lakes:

• ACID Transactions: Multiple writers and readers, no corruption.
• Time Travel: Query data as it was at any point in the past (“as of 2024-02-01”).
• Schema Enforcement and Evolution: Define a schema, enforce it, evolve it safely.
• Unified Batch and Streaming: Same table for batch and streaming data.

# Delta Lake: Lake with warehouse features
from delta.tables import DeltaTable
from pyspark.sql import functions as F

# Create a Delta table (ACID, time-travel capable)
df.write.format("delta").mode("overwrite").save("s3://my-lake/customer_events")

# Later: read as of a specific timestamp
historical_df = spark.read.option("versionAsOf", "2024-02-01").delta("s3://my-lake/customer_events")

# Schema enforcement: this write fails if columns don't match
new_events = spark.read.json("s3://raw-events/mobile-app")
new_events.write.format("delta").mode("append").save("s3://my-lake/customer_events")

Architecture in Depth

Data Warehouse: MPP and Columnar Storage

Warehouses use Massively Parallel Processing (MPP). Queries are split across many nodes; each processes a partition of data and returns results that are combined.

They store data in columnar format: all values for a single column are stored contiguously. This is optimal for analytical queries that touch a few columns but many rows.

Row-oriented (used by databases, transactional systems):
CustomerID | Name    | Segment    | Revenue
1          | Alice   | Premium    | $5000
2          | Bob     | Standard   | $1200

Columnar (used by warehouses):
CustomerID: [1, 2, 3, 4, 5, ...]
Name: [Alice, Bob, Carol, Dave, Eve, ...]
Segment: [Premium, Standard, Premium, Basic, Standard, ...]
Revenue: [$5000, $1200, $8300, $600, $2100, ...]

Query: "Sum of revenue by segment"
Columnar: Only read Segment and Revenue columns
Row-oriented: Read all columns, ignore Name and CustomerID

This compression works because columnar data is often repetitive. Segment column might have only 5 distinct values; these can be heavily compressed.

Governance and Data Quality

This is where many data lakes fail. A data lake without governance becomes a data swamp:

• Nobody knows what data lives where.
• The same “customer” is defined 10 different ways across tables.
• Data quality issues propagate downstream.
• Compliance teams can’t trace PII.

To avoid this:

• Data Catalog: Maintain an inventory.
• Lineage: Track where data comes from.
• Data Quality Checks: Automated tests before data lands.

Comparison: Warehouse vs Lake vs Lakehouse

Dimension	Warehouse	Data Lake	Lakehouse
Data Format	Structured, predefined schema	Any format (JSON, images, etc.)	Structured with schema flexibility
Schema Approach	Schema-on-write	Schema-on-read	Schema-on-read, enforced on-write
Processing Model	ETL (transform before load)	ELT (transform after load)	Both ETL and ELT
Query Performance	Very fast (optimized, indexed)	Variable (depends on query engine)	Fast (optimized columnar storage)
Cost Model	Expensive compute, reasonable storage	Cheap storage, variable compute	Cheap storage, optimized compute
Governance	Built-in, enforced	Must be added (hard to retrofit)	Built-in (ACID, lineage, schema)
Flexibility	Low (schema changes are hard)	High (new data types easily added)	High (with enforcement)
ACID Transactions	Yes	No (until recently)	Yes

Key Takeaways

• Data warehouses are optimized for known analytical patterns (schema-on-write, ETL, star schemas).
• Data lakes store raw data cheaply and flexibly (schema-on-read, ELT, Parquet/ORC).
• Lakehouses (Delta Lake, Apache Iceberg) combine the ACID governance of warehouses with the cheap storage and streaming capabilities of data lakes.
• Columnar storage is why analytical query engines scan petabytes in seconds by skipping unneeded columns.

---

In the next chapter, we’ll dive into Data Partitioning strategies to scale databases across multiple nodes.

# PySpark Delta Lakehouse Read with Time Travel
from pyspark.sql import SparkSession

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

# Read historical snapshot of table as of a specific date (Time Travel)
df_historical = spark.read \
    .format("delta") \
    .option("timestampAsOf", "2026-01-01 00:00:00") \
    .load("s3://analytics-lakehouse/events_fact")

❌ Error: Anti-Pattern (Dumping millions of tiny 1KB JSON files into S3 without partitioning or catalog)
Result: Query engines (Spark/Athena) spend 99% of query execution time opening S3 metadata handles (File Listing Overhead).

✅ Safe: Idiomatic Pattern (Compact into large Parquet files ~128MB, partitioned by date `year=2026/month=08/`)