Database vs Data Warehouse vs Data Lakehouse

Understanding the differences between databases, data warehouses, data lakes, and data lakehouses is fundamental for data architecture.

Quick Comparison

Database (OLTP)

For running applications. Optimized for transactions — fast reads and writes of individual records.

App ──→ INSERT INTO orders VALUES (...)
App ──→ SELECT * FROM orders WHERE id = 123
App ──→ UPDATE orders SET status = 'shipped' WHERE id = 123

Examples: PostgreSQL, MySQL, MongoDB, DynamoDB

Characteristics:

• Row-oriented storage
• ACID transactions
• Low latency (milliseconds)
• Current state of data

Data Warehouse (OLAP)

For business analytics. Optimized for complex queries across large datasets.

Analyst ──→ SELECT region, SUM(revenue), AVG(order_value)
            FROM fact_sales
            JOIN dim_date ON ...
            WHERE year = 2025
            GROUP BY region

Examples: Snowflake, BigQuery, Redshift, Azure Synapse

Characteristics:

• Column-oriented storage (fast aggregations)
• Structured data with schema enforcement
• Historical data (months/years)
• ETL/ELT to load data
• Expensive for large volumes

Data Lake

A centralized repository for all raw data — structured, semi-structured, and unstructured.

Data Lake (S3, ADLS, GCS)
├── raw/
│   ├── logs/          ← JSON files
│   ├── images/        ← PNG, JPEG
│   ├── csv_exports/   ← CSV files
│   └── events/        ← Parquet files
├── processed/
│   └── cleaned_data/
└── curated/
    └── ready_for_analytics/

Examples: AWS S3, Azure Data Lake Storage, Google Cloud Storage

Characteristics:

• Store everything cheaply
• Schema-on-read (define schema when you query)
• Good for data science and ML
• Risk of becoming a “data swamp” without governance

Data Lakehouse

Combines the best of data lakes and data warehouses.

┌─────────────────────────────────────┐
│         Data Lakehouse              │
│                                     │
│  ┌─────────────────────────────┐   │
│  │  Metadata / Governance Layer │   │ ← ACID, Schema, Quality
│  └─────────────────────────────┘   │
│  ┌─────────────────────────────┐   │
│  │  Open File Formats          │   │ ← Parquet, Delta, Iceberg
│  └─────────────────────────────┘   │
│  ┌─────────────────────────────┐   │
│  │  Cheap Object Storage       │   │ ← S3, ADLS, GCS
│  └─────────────────────────────┘   │
└─────────────────────────────────────┘

Examples: Databricks (Delta Lake), Apache Iceberg, Apache Hudi

Key features:

• ACID transactions on a data lake
• Schema enforcement and evolution
• Time travel (query historical versions)
• Works for both BI and ML
• Uses open file formats (Parquet, Delta, Iceberg)
• Cheaper than a data warehouse

Architecture Evolution

2000s: Database → ETL → Data Warehouse → BI Reports

2010s: Database → Data Lake → Data Warehouse → BI Reports
                      └──→ ML / Data Science

2020s: Database → Data Lakehouse → BI Reports
                       └──→ ML / Data Science
                       └──→ Real-time Analytics

When to Use What

Summary