Data Lake vs Data Warehouse — When to Use Each

Data Warehouse

A data warehouse is a centralized repository of structured, cleaned data optimized for SQL analytics.

Characteristics:

• Structured data (tables with fixed schemas)
• Cleaned and transformed (via ETL/ELT pipelines)
• Optimized for queries (indexed, partitioned, columnar storage)
• Used by analysts (for dashboards, reports, BI tools)

Examples: BigQuery, Snowflake, Redshift, Azure Synapse

Analogy: A data warehouse is like a library with organized bookshelves, catalogs, and librarians. You can find any book quickly.

Data Lake

A data lake is a storage system for raw, unprocessed data in any format (structured, semi-structured, unstructured).

Characteristics:

• Any format (CSV, JSON, Parquet, images, videos, logs)
• Raw, unprocessed (no schema enforcement, no cleaning)
• Cheap storage (store everything, decide later what to use)
• Used by data engineers/scientists (for ETL, ML, exploration)

Examples: AWS S3, Azure Data Lake Storage (ADLS), Google Cloud Storage (GCS)

Analogy: A data lake is like a storage unit where you dump all your stuff in boxes. It's cheap, but you need to dig through boxes to find anything useful.

| Aspect | Data Lake | Data Warehouse | |--------|-----------|----------------| | Data format | Any (CSV, JSON, Parquet, images, videos) | Structured tables only | | Schema | Schema-on-read (no schema until queried) | Schema-on-write (enforced when data is loaded) | | Data quality | Raw, unprocessed (may have errors) | Cleaned, validated, transformed | | Storage cost | Very cheap ($0.023/GB/month on S3) | Moderate ($0.02-0.04/GB/month) | | Query speed | Slow (must read files, parse formats) | Fast (optimized for SQL, columnar) | | Users | Data engineers, data scientists | Analysts, business users | | Query language | Spark, Python, or SQL (via tools) | SQL | | Use cases | Raw data storage, ML feature engineering, archival | Dashboards, reports, business analytics | | Flexibility | High (store anything, decide later) | Low (must define schema upfront) | | Governance | Hard (no schema, no validation) | Easy (schema enforced, data quality tests) |

Schema-on-Read vs Schema-on-Write

Data Lake (Schema-on-Read):

• Data is stored as-is (no schema enforced)
• Schema is applied when you read/query the data
• Flexible (can reinterpret the same data in multiple ways)
• Example: JSON logs stored in S3 — you parse fields when querying

Data Warehouse (Schema-on-Write):

• Schema is enforced when data is loaded
• Data must match the table schema (column types, constraints)
• Rigid (if schema changes, you must alter the table)
• Example: Orders table in BigQuery — you must specify columns and types upfront

Modern data architectures use both data lakes and warehouses:

The Modern Data Stack Pattern

Example — Swiggy's Data Architecture

Step 1: Ingest to Lake

• Raw delivery GPS logs (JSON, 5 GB/hour) → Stream to S3
• Restaurant menu updates (JSON) → Load to S3
• Order transaction logs (CSV) → Load to S3

Why lake first?

• Cheap storage (don't know yet what data is useful)
• Preserve raw data (can reprocess if needed)
• Flexible (no schema decisions yet)

Step 2: Transform with Spark

• Read JSON logs from S3
• Parse, clean, aggregate (calculate delivery times by zone)
• Write transformed Parquet files back to S3

Step 3: Load to Warehouse

• Load cleaned Parquet from S3 to BigQuery
• Create tables: fact_deliveries, dim_restaurants, dim_zones
• Analysts query BigQuery (fast SQL queries)

Step 4: Dashboards

• Looker Studio connects to BigQuery
• Real-time dashboards: "Average delivery time by city"

Use a Data Lake When:

1. Storing unstructured data

   • Images, videos, PDFs, audio files
   • Example: Netflix stores movie files in S3

2. Raw data you're not ready to analyze yet

   • Logs, events, sensor data
   • Example: Flipkart stores raw clickstream logs (may analyze later for ML)

3. Data science / machine learning

   • Training datasets (images for computer vision, text for NLP)
   • Example: Ola stores ride GPS traces in S3 for route optimization models

4. Archival / compliance

   • Store historical data cheaply (access rarely)
   • Example: Banking transactions older than 7 years → archive to S3 Glacier ($0.004/GB/month)

5. Exploratory analysis / experimentation

   • Data engineers experimenting with new data sources
   • Example: Parse Twitter API data to see if it's useful for sentiment analysis

Use a Data Warehouse When:

1. Structured data for analytics

   • Orders, customers, products, transactions
   • Example: Zomato stores cleaned order data in BigQuery

2. Business intelligence / dashboards

   • Analysts querying for reports
   • Example: "Monthly revenue by product category" dashboard

3. Ad-hoc SQL queries

   • Business users exploring data without code
   • Example: Marketing team queries BigQuery to analyze campaign ROI

4. Aggregated metrics

   • Daily/monthly summaries for fast dashboards
   • Example: Swiggy pre-aggregates delivery metrics by hour/city in Redshift

5. Data governance / quality

   • Schema enforcement, validation, tests
   • Example: Razorpay's warehouse has strict schema + dbt tests to ensure payment data accuracy

Hybrid Use Cases

Use both:

• Raw data in lake (cheap, flexible) → Transform with Spark → Load to warehouse (fast queries)
• Example: Flipkart stores 10 TB of logs daily in S3, processes with Spark, loads 100 GB of summaries to BigQuery

Data lakes store files, not tables. To query them, you need a query engine:

1. AWS Athena (Query S3 with SQL)

Athena is a serverless query service that reads files in S3 and applies SQL.

-- Define a table schema over S3 files
CREATE EXTERNAL TABLE clickstream (
  user_id STRING,
  event_type STRING,
  page_url STRING,
  timestamp TIMESTAMP
)
STORED AS PARQUET
LOCATION 's3://flipkart-data-lake/clickstream/';

-- Query the data
SELECT event_type, COUNT(*) AS event_count
FROM clickstream
WHERE DATE(timestamp) = '2026-03-22'
GROUP BY event_type;

Performance: Slower than BigQuery (no indexing, reads files every time).

Cost: $5 per TB scanned (same as BigQuery).

2. Apache Spark (Programmatic Queries)

from pyspark.sql import SparkSession

spark = SparkSession.builder.appName("query-lake").getOrCreate()

# Read Parquet files from S3
df = spark.read.parquet("s3://flipkart-data-lake/clickstream/2026-03-22/")

# Query with Spark SQL
df.createOrReplaceTempView("clickstream")
result = spark.sql("""
  SELECT event_type, COUNT(*) AS event_count
  FROM clickstream
  GROUP BY event_type
""")

result.show()

3. Databricks / Delta Lake (Enhanced Lake with ACID)

Delta Lake adds warehouse-like features to data lakes:

• ACID transactions (no partial writes)
• Schema enforcement
• Time travel (query historical versions)

Example — Delta Lake on S3:

# Write data to Delta Lake
df.write.format("delta").save("s3://flipkart-lake/orders-delta/")

# Query Delta Lake (fast, ACID, schema enforced)
orders = spark.read.format("delta").load("s3://flipkart-lake/orders-delta/")
orders.filter("order_date = '2026-03-22'").show()

Benefit: Combines lake flexibility (store raw files) with warehouse performance (fast queries, schema enforcement).

Data Lakehouse is a new architecture that combines the flexibility of data lakes with the performance of data warehouses.

Problem with Traditional Lakes

• No schema enforcement → data quality issues
• Slow queries (read/parse files every time)
• No ACID transactions (partial writes, inconsistency)

Lakehouse Features

1. ACID transactions (via Delta Lake, Iceberg, Hudi)
2. Schema enforcement (validate data on write)
3. Time travel (query historical versions)
4. Fast queries (metadata indexing, data skipping)
5. Cheap storage (still just files on S3/ADLS/GCS)

Example — Databricks Lakehouse

# Create a Delta table (lakehouse) on S3
df.write.format("delta") \
  .mode("overwrite") \
  .saveAsTable("orders")

# Query with SQL (fast, like a warehouse)
spark.sql("""
  SELECT city, SUM(amount) AS revenue
  FROM orders
  WHERE order_date = '2026-03-22'
  GROUP BY city
""").show()

# Time travel (query yesterday's data)
spark.read.format("delta") \
  .option("versionAsOf", 1) \
  .table("orders") \
  .show()

Benefit: Analysts query with SQL (like a warehouse), but data is stored cheaply in S3 (like a lake).

When to Use a Lakehouse

• You want warehouse performance (fast SQL queries)
• But you need lake flexibility (store any format, change schemas easily)
• And you want lake pricing (cheap S3 storage)

Example companies using lakehouses:

• Netflix: Stores petabytes of user data in S3, queries with Iceberg
• Uber: Uses Delta Lake on S3 for ride data
• Booking.com: Migrated from Hive (slow lake) to Iceberg (fast lakehouse)