Lakehouses & Warehouses in Microsoft Fabric

Lakehouse Architecture

A Fabric lakehouse has three layers that are automatically kept in sync:

# Create a Delta table from raw CSV files
df = spark.read.csv("Files/raw/customers.csv", header=True, inferSchema=True)

# Clean the data
df_clean = (df
    .dropDuplicates(["customer_id"])
    .withColumn("created_date", df["created_date"].cast("date"))
    .filter(df["customer_id"].isNotNull())
)

# Write as managed Delta table — auto-appears in SQL endpoint
df_clean.write.format("delta").mode("overwrite").saveAsTable("dim_customers")

Warehouse Deep Dive

The Fabric warehouse is a fully managed T-SQL data warehouse. Unlike the lakehouse SQL endpoint, it supports full DML operations:

-- Create a table in the warehouse
CREATE TABLE dbo.fact_sales (
    sale_id     BIGINT NOT NULL,
    customer_id BIGINT NOT NULL,
    product_id  INT NOT NULL,
    sale_date   DATE NOT NULL,
    amount      DECIMAL(18,2) NOT NULL,
    region      VARCHAR(50)
);

-- Insert data (not possible in lakehouse SQL endpoint!)
INSERT INTO dbo.fact_sales
SELECT * FROM bronze_lakehouse.dbo.raw_sales
WHERE sale_date >= '2024-01-01';

-- Cross-database query (warehouse exclusive)
SELECT w.sale_id, l.customer_name
FROM dbo.fact_sales w
JOIN lakehouse_db.dbo.dim_customers l
    ON w.customer_id = l.customer_id;

Lakehouse vs Warehouse: Decision Matrix

Delta Tables in Fabric

Every table in Fabric (lakehouse or warehouse) is stored as a Delta table — Parquet data files plus a JSON transaction log (_delta_log/). This gives you:

• ACID transactions — no corrupt data from failed writes
• Time travel — query data as it was at any point in time
• Schema enforcement — reject writes that don’t match the table schema
• Z-Order optimization — co-locate related rows for faster queries

# Time travel — read the table as it was 2 versions ago
df_old = spark.read.format("delta").option("versionAsOf", 2).table("fact_sales")

# Or by timestamp
df_yesterday = (spark.read.format("delta")
    .option("timestampAsOf", "2026-03-30")
    .table("fact_sales"))

# Optimize with Z-Order for common query patterns
spark.sql("OPTIMIZE fact_sales ZORDER BY (region, sale_date)")

Direct Lake Mode

Direct Lake is Fabric’s breakthrough connection mode for Power BI. Instead of importing data or sending live queries, Power BI reads Delta Parquet files directly from OneLake into the VertiPaq engine’s memory on demand.

How Direct Lake Works

1. A user opens a Power BI report
2. The VertiPaq engine checks which Delta table columns are needed
3. It reads the Parquet files from OneLake directly (no copy, no query to Spark/SQL)
4. Data is loaded into columnar memory for lightning-fast DAX calculations
5. When Delta tables are updated, the next report interaction picks up the new data

Direct Lake Requirements

• Data must be in Delta tables in a Fabric lakehouse or warehouse
• Tables must be in the default semantic model (auto-created) or a custom one
• Capacity must be F64 or higher for production-scale Direct Lake
• Row count and column limits depend on SKU (F64: 300M rows per table)

OneLake Shortcuts

Shortcuts let you reference external data without copying it. They appear as folders in your lakehouse, but the data stays in its original location.

Medallion Architecture in Practice

Here’s a concrete implementation of the Bronze → Silver → Gold pattern in Fabric:

# === BRONZE: Raw ingestion (notebook in bronze_lakehouse) ===
# Ingest raw CSV files into a Delta table as-is
df_raw = spark.read.csv("Files/landing/sales_*.csv", header=True)
df_raw.write.format("delta").mode("append").saveAsTable("raw_sales")

# === SILVER: Clean + conform (notebook in silver_lakehouse) ===
# Read from bronze via shortcut, clean, write to silver
df_bronze = spark.read.format("delta").table("bronze_lakehouse.raw_sales")
df_silver = (df_bronze
    .dropDuplicates(["transaction_id"])
    .filter("amount > 0")
    .withColumn("sale_date", df_bronze["sale_date"].cast("date"))
    .withColumnRenamed("cust_id", "customer_id")
)
df_silver.write.format("delta").mode("overwrite").saveAsTable("clean_sales")

# === GOLD: Business aggregates (notebook in gold_lakehouse) ===
df_gold = spark.sql("""
    SELECT region, sale_date, COUNT(*) as txn_count, SUM(amount) as revenue
    FROM silver_lakehouse.clean_sales
    GROUP BY region, sale_date
""")
df_gold.write.format("delta").mode("overwrite").saveAsTable("daily_revenue")