Data Warehouses vs Data Lakes in Azure

The fundamental difference

A data warehouse is an opinionated, structured environment. Data is modelled before it enters the warehouse — schemas are defined, types are enforced, and the data is stored in a format optimised for SQL query engines (columnar compression, indexes, statistics). The warehouse knows exactly what data it contains. This structure provides fast, reliable SQL performance at the cost of flexibility: loading new data requires it to fit a predefined schema.

A data lake is a schema-on-read storage layer. Files are stored in their original or converted format (JSON, CSV, Parquet, Avro, Delta) with minimal constraints. Any structure is imposed at query time. This flexibility means you can store data before you know how you will use it, but it also means queries bear the cost of schema inference and have no benefit from indexes or pre-computed statistics.

When the data warehouse wins

The dedicated SQL pool (data warehouse) outperforms a data lake query in these scenarios:

-- Scenario: 200 concurrent Power BI users hitting a dashboard
-- that queries 5 TB of sales data and needs sub-second response

-- On a Synapse dedicated pool (DW1000c):
-- Result-set caching returns this in < 100ms for repeated queries
-- Even without cache, materialised views serve it in ~2 seconds

SELECT
    r.region_name,
    p.category,
    SUM(f.order_total)      AS total_revenue,
    COUNT(f.order_id)       AS order_count,
    AVG(f.order_total)      AS avg_order_value
FROM
    dbo.FactOrders      f
    JOIN dbo.DimRegion  r ON f.region_id = r.region_id
    JOIN dbo.DimProduct p ON f.product_id = p.product_id
WHERE
    f.order_date BETWEEN '2025-01-01' AND '2025-12-31'
GROUP BY
    r.region_name,
    p.category
ORDER BY
    total_revenue DESC;

-- Same query on serverless SQL against ADLS Parquet:
-- First run: ~45 seconds (no cache, reads from lake files)
-- For 200 concurrent users, the serverless pool may throttle or slow significantly
-- No materialised views; no result-set caching across sessions

The warehouse wins when: query concurrency is high (50+ simultaneous users), response time SLAs are strict (sub-second), queries are repeated frequently (Power BI dashboard refresh), and data is structured and stable (star schema, known dimensions).

When the data lake wins

The data lake wins in scenarios that do not fit the rigid warehouse model:

# Scenario: training an ML model on 3 years of raw clickstream events
# The clickstream is JSON with varying schemas across versions
# Total data: 2 TB of JSON in ADLS bronze layer

from pyspark.sql import SparkSession
from pyspark.sql.functions import col, explode, get_json_object
from pyspark.ml.feature import VectorAssembler
from pyspark.ml.classification import RandomForestClassifier

spark = SparkSession.builder.getOrCreate()

# Read 2 TB of JSON with schema variation - impossible in a rigid warehouse
# In a warehouse you would need to know every field in advance
events = spark.read.json(
    "abfss://bronze@mydatalake.dfs.core.windows.net/clickstream/year=202*/",
    multiLine=False
)

# Handle schema variation: use get_json_object for flexible field extraction
features = events.select(
    col("user_id"),
    col("session_id"),
    get_json_object(col("properties"), "$.page_category").alias("page_category"),
    get_json_object(col("properties"), "$.time_on_page").cast("double").alias("time_on_page"),
    get_json_object(col("properties"), "$.scroll_depth").cast("double").alias("scroll_depth"),
    col("converted").cast("int").alias("label")
).na.drop()

# A 2 TB ML training job is impractical in a dedicated SQL pool
# Spark distributes this across the cluster efficiently
print(f"Training on {features.count()} events")

The lake wins when: data is semi-structured or unstructured, data schemas vary or evolve rapidly, workloads are exploratory or one-off (not repeated production queries), machine learning feature engineering requires complex Python transformations, and data volumes are very large but query concurrency is low.

The lakehouse: one storage layer, multiple query engines

The lakehouse pattern uses Delta Lake in ADLS Gen2 as the single storage layer, with multiple query engines reading from it:

                    ADLS Gen2 (Delta Lake)
                    ┌──────────────────┐
                    │   bronze layer   │
                    │   silver layer   │
                    │   gold layer     │
                    └────────┬─────────┘
                             │ (same files, same Delta tables)
          ┌──────────────────┼──────────────────────┐
          ▼                  ▼                       ▼
  Synapse serverless   Databricks SQL          Spark (Synapse
  SQL pool             (Photon)                or Databricks)
  (ad-hoc SQL)         (BI queries)           (ML, ETL)
          │                  │
          ▼                  ▼
   Power BI (direct     Power BI (import
   query or DirectLake)  or DirectLake)

In this architecture, there is no ETL copy step between lake and warehouse. The gold Delta tables in ADLS serve both the BI users (via serverless SQL or Databricks SQL) and the ML engineers (via Spark). Microsoft calls this the “open data lakehouse” and it is the direction both Synapse and Databricks are moving toward.

The practical advantage is cost: you pay for one copy of the data (ADLS storage), and you choose the query engine based on the workload. Ad-hoc exploration uses serverless SQL ($5/TB). Production BI uses a dedicated pool or Databricks SQL warehouse (provisioned, consistent). ML training uses Spark (per vCore-hour, auto-pausing).

Migrating from a warehouse-only to a lakehouse architecture

Many organisations start with only a Synapse dedicated SQL pool and later want to add data lake capabilities for ML and exploration. Here is the migration approach:

-- Step 1: Export existing warehouse tables to ADLS as Parquet
-- Run this in the dedicated SQL pool to export via CETAS
CREATE EXTERNAL TABLE ext_FactOrders
WITH (
    LOCATION = '/gold/fact_orders/',
    DATA_SOURCE = MyADLSDataSource,
    FILE_FORMAT = ParquetFormat
)
AS
SELECT * FROM dbo.FactOrders;

-- Step 2: Convert those Parquet files to Delta Lake
-- Run this in a Spark notebook
df = spark.read.parquet(
    "abfss://gold@mydatalake.dfs.core.windows.net/fact_orders/"
)
df.write.format("delta").mode("overwrite").save(
    "abfss://gold@mydatalake.dfs.core.windows.net/fact_orders_delta/"
)

-- Step 3: Create external tables in the serverless SQL pool
-- pointing to the Delta tables, so SQL users can query them
CREATE EXTERNAL TABLE dbo.FactOrders_Lake
WITH (
    LOCATION = '/gold/fact_orders_delta/',
    DATA_SOURCE = MyADLSDataSource,
    FILE_FORMAT = DeltaFormat
);