Home > Docs > Best Practices > Lakehouse vs Warehouse vs SQL Database Decision Guide

🏗️ Lakehouse vs Warehouse vs SQL Database — Decision Guide¶

Last Updated: 2026-04-21 | Version: 1.0.0

📑 Table of Contents¶

• 🎯 Overview
• 📊 Feature Comparison Matrix
• 🔍 Deep Dive: Each Store
• 🌳 Decision Trees
• ⚡ Performance Characteristics
• 🔐 Security Model Comparison
• 🔄 Hybrid Patterns
• 🎰 Casino Implementation
• 🏛️ Federal Agency Implementation
• 🔀 Migration Paths
• ⚠️ Limitations
• 📚 References
• 🔗 Related Documents

🎯 Overview¶

Microsoft Fabric offers three distinct data stores — Lakehouse, Warehouse, and SQL Database — each optimized for different workload patterns. Choosing the wrong store leads to performance issues, governance gaps, or unnecessary complexity. This guide provides a structured decision framework.

The Three Stores at a Glance¶

flowchart TB
    subgraph Lakehouse["🏠 Lakehouse"]
        L1["Delta Lake / Parquet on OneLake"]
        L2["Spark + SQL analytics endpoint"]
        L3["Best for: Data engineering,<br/>medallion architecture, ML"]
    end

    subgraph Warehouse["🏢 Warehouse"]
        W1["Managed columnar storage"]
        W2["Full T-SQL DML/DDL"]
        W3["Best for: BI serving,<br/>star schema, ad-hoc SQL"]
    end

    subgraph SQLDB["🗄️ SQL Database"]
        S1["Transactional rowstore + columnstore"]
        S2["Full SQL Server engine"]
        S3["Best for: Operational apps,<br/>OLTP, microservices"]
    end

    OneLake["📦 OneLake<br/>Unified Storage"] --- Lakehouse
    OneLake --- Warehouse
    OneLake --- SQLDB

    style Lakehouse fill:#27AE60,stroke:#1E8449,color:#fff
    style Warehouse fill:#2471A3,stroke:#1A5276,color:#fff
    style SQLDB fill:#8E44AD,stroke:#6C3483,color:#fff
    style OneLake fill:#F39C12,stroke:#E67E22,color:#fff

📊 Feature Comparison Matrix¶

Core Capabilities¶

Query Language Surface¶

Data Engineering Features¶

🔍 Deep Dive: Each Store¶

🏠 Lakehouse¶

When to use: Data engineering, medallion architecture, ML/AI workloads, multi-format ingestion, data science exploration.

Architecture:

flowchart TB
    subgraph Lakehouse["🏠 Lakehouse"]
        subgraph Tables["Managed Tables (Delta)"]
            B["Bronze Tables"]
            S["Silver Tables"]
            G["Gold Tables"]
        end
        subgraph Files["Files Section"]
            RAW["Raw Files<br/>CSV, JSON, Parquet"]
        end
        subgraph Endpoints["Access Points"]
            SPK["Spark Engine<br/>Read + Write"]
            SQL["SQL Analytics Endpoint<br/>Read-Only T-SQL"]
        end
    end

    SPK --> Tables
    SPK --> Files
    SQL --> Tables

Key characteristics: - Open Delta Lake format — no vendor lock-in - Dual access: Spark (read/write) + SQL (read-only) - Shortcuts bring external data without copying - V-Order optimizes for both Spark and Direct Lake - File section supports arbitrary formats

🏢 Warehouse¶

When to use: BI serving layer, star/snowflake schema, ad-hoc SQL analytics, stored procedures, T-SQL-heavy workloads.

Architecture:

flowchart TB
    subgraph Warehouse["🏢 Warehouse"]
        subgraph Schema["Schemas"]
            DBO["dbo (default)"]
            AN["analytics"]
            STG["staging"]
        end
        subgraph Objects["Database Objects"]
            TBL["Tables<br/>(auto columnstore)"]
            VW["Views"]
            SP["Stored Procedures"]
            FN["Functions"]
        end
        subgraph Access["Access Points"]
            TSQL["T-SQL Engine<br/>Full DML/DDL"]
            DL["Direct Lake<br/>BI Access"]
        end
    end

    TSQL --> Schema --> Objects
    DL --> TBL

Key characteristics: - Familiar SQL Server experience (T-SQL, schemas, procedures) - Auto-managed columnstore — no index tuning needed - Full DML support (INSERT, UPDATE, DELETE, MERGE) - Custom schemas for logical organization - Workload isolation from Spark jobs

🗄️ SQL Database¶

When to use: Operational/transactional workloads, microservice backends, OLTP, applications requiring enforced constraints, CDC to Fabric.

Architecture:

flowchart TB
    subgraph SQLDB["🗄️ SQL Database"]
        subgraph Engine["SQL Server Engine"]
            TXN["Transaction Manager<br/>Full ACID"]
            QP["Query Processor"]
            IDX["Index Engine<br/>B-tree + Columnstore"]
        end
        subgraph Objects["Full SQL Server Objects"]
            TBL["Tables + Constraints"]
            TRG["Triggers"]
            SP["Stored Procedures"]
            CDC["Change Data Capture"]
        end
        subgraph Replication["OneLake Integration"]
            MIR["Auto-mirroring to<br/>OneLake Delta"]
        end
    end

    TBL --> MIR
    CDC --> MIR

Key characteristics: - Full SQL Server engine with enforced referential integrity - Triggers, constraints, foreign keys — all enforced - Native CDC for streaming changes to analytics - Auto-mirrors to OneLake for Lakehouse/Warehouse consumption - Best for sub-second transactional workloads

🌳 Decision Trees¶

Primary Decision Tree¶

flowchart TD
    Start([What's the primary workload?]) --> Q1{Data engineering<br/>or data science?}
    Q1 -->|Yes| LH["🏠 Lakehouse"]
    Q1 -->|No| Q2{Operational app<br/>or microservice?}
    Q2 -->|Yes| Q3{Need enforced<br/>constraints/triggers?}
    Q3 -->|Yes| SQL["🗄️ SQL Database"]
    Q3 -->|No| Q4{High write<br/>concurrency?}
    Q4 -->|Yes| SQL
    Q4 -->|No| WH["🏢 Warehouse"]
    Q2 -->|No| Q5{BI serving<br/>with T-SQL?}
    Q5 -->|Yes| WH
    Q5 -->|No| Q6{Mixed Spark<br/>+ SQL?}
    Q6 -->|Yes| LH
    Q6 -->|No| WH

    style LH fill:#27AE60,stroke:#1E8449,color:#fff
    style WH fill:#2471A3,stroke:#1A5276,color:#fff
    style SQL fill:#8E44AD,stroke:#6C3483,color:#fff

"Which Store for This Table?" Decision Tree¶

flowchart TD
    Start([Where should this table live?]) --> Q1{Does an app<br/>write to it<br/>transactionally?}
    Q1 -->|Yes| SQL["🗄️ SQL Database<br/>+ mirror to OneLake"]
    Q1 -->|No| Q2{Is it a raw<br/>or staging table?}
    Q2 -->|Yes| LH["🏠 Lakehouse<br/>(Bronze/Silver)"]
    Q2 -->|No| Q3{Is it a dimension<br/>or fact for BI?}
    Q3 -->|Yes| Q4{Need T-SQL<br/>stored procs<br/>for transforms?}
    Q4 -->|Yes| WH["🏢 Warehouse<br/>(Gold/Star Schema)"]
    Q4 -->|No| Q5{Need Spark<br/>for transforms?}
    Q5 -->|Yes| LH2["🏠 Lakehouse<br/>(Gold layer)"]
    Q5 -->|No| WH
    Q3 -->|No| Q6{ML feature<br/>store?}
    Q6 -->|Yes| LH3["🏠 Lakehouse"]
    Q6 -->|No| WH

    style LH fill:#27AE60,stroke:#1E8449,color:#fff
    style LH2 fill:#27AE60,stroke:#1E8449,color:#fff
    style LH3 fill:#27AE60,stroke:#1E8449,color:#fff
    style WH fill:#2471A3,stroke:#1A5276,color:#fff
    style SQL fill:#8E44AD,stroke:#6C3483,color:#fff

⚡ Performance Characteristics¶

Workload Profile Comparison¶

Concurrency Benchmarks (F64 SKU)¶

🔐 Security Model Comparison¶

🔄 Hybrid Patterns¶

Pattern 1: Lakehouse for Engineering + Warehouse for BI¶

The most common pattern. Use the Lakehouse for medallion processing and the Warehouse as a curated BI serving layer.

flowchart LR
    subgraph Sources["📥 Sources"]
        S1["Eventstreams"]
        S2["Pipelines"]
        S3["Dataflows"]
    end

    subgraph LH["🏠 Lakehouse"]
        B["Bronze"]
        SV["Silver"]
        G["Gold (Spark)"]
    end

    subgraph WH["🏢 Warehouse"]
        DIM["Dimensions"]
        FACT["Facts"]
        AGG["Aggregations"]
        VW["Views + Procs"]
    end

    subgraph BI["📊 Power BI"]
        DL["Direct Lake<br/>Semantic Model"]
    end

    Sources --> B --> SV --> G -->|"INSERT INTO"| WH --> DL

    style LH fill:#27AE60,stroke:#1E8449,color:#fff
    style WH fill:#2471A3,stroke:#1A5276,color:#fff
    style BI fill:#F39C12,stroke:#E67E22,color:#fff

Pattern 2: SQL Database for Ops + Lakehouse for Analytics¶

Operational applications write to SQL Database; data mirrors to OneLake for analytics processing.

flowchart LR
    subgraph App["📱 Application"]
        API["REST API"]
        UI["Web UI"]
    end

    subgraph SQLDB["🗄️ SQL Database"]
        OPS["Operational Tables"]
        CDC_["CDC Enabled"]
    end

    subgraph Mirror["🔄 Auto-Mirror"]
        DLT["Delta Tables<br/>in OneLake"]
    end

    subgraph LH["🏠 Lakehouse"]
        AN["Analytics Tables"]
    end

    App --> SQLDB --> Mirror --> LH

    style SQLDB fill:#8E44AD,stroke:#6C3483,color:#fff
    style LH fill:#27AE60,stroke:#1E8449,color:#fff

Pattern 3: All Three Together¶

Enterprise deployments often use all three stores, each for its optimal workload.

🎰 Casino Implementation¶

Recommended Store Allocation¶

Casino Architecture Summary¶

flowchart TB
    subgraph Floor["🎰 Casino Floor"]
        SLOT["5,000 Slot Machines"]
        TABLE["500 Table Games"]
        KIOSK["Self-Service Kiosks"]
    end

    subgraph LH["🏠 Lakehouse"]
        B["Bronze<br/>Raw telemetry"]
        S["Silver<br/>Cleansed sessions"]
        GC["Gold<br/>Compliance tables"]
    end

    subgraph WH["🏢 Warehouse"]
        DIM["dim_machine<br/>dim_player<br/>dim_date"]
        FACT["fact_slot_daily<br/>fact_table_daily"]
        AGG["agg_floor_hourly"]
    end

    subgraph SQL["🗄️ SQL Database"]
        CFG["machine_config"]
        PLR["player_accounts"]
        ALR["alert_rules"]
    end

    Floor --> B --> S --> GC
    S -->|"INSERT INTO"| FACT
    SQL -->|"Mirror"| LH
    WH --> BI["📊 Direct Lake"]
    GC --> BI

    style LH fill:#27AE60,stroke:#1E8449,color:#fff
    style WH fill:#2471A3,stroke:#1A5276,color:#fff
    style SQL fill:#8E44AD,stroke:#6C3483,color:#fff

🏛️ Federal Agency Implementation¶

Per-Agency Recommendations¶

Federal Hybrid Architecture¶

flowchart TB
    subgraph Agency["🏛️ Agency Workspace"]
        subgraph LH["🏠 Lakehouse"]
            AB["Bronze (raw API data)"]
            AS["Silver (validated)"]
            AG["Gold (analytics)"]
        end

        subgraph WH["🏢 Warehouse"]
            DIM["Agency Dimensions"]
            FACT["KPI Facts"]
            VW["Reporting Views"]
        end

        subgraph SQL["🗄️ SQL Database"]
            APP["Application Tables"]
            CDC_["CDC → OneLake"]
        end
    end

    subgraph CrossAgency["🔗 Cross-Agency"]
        XDB["Cross-Database Queries"]
        SM["Shared Semantic Model"]
    end

    AG --> WH --> SM
    SQL --> CDC_ --> LH
    WH --> XDB

    style LH fill:#27AE60,stroke:#1E8449,color:#fff
    style WH fill:#2471A3,stroke:#1A5276,color:#fff
    style SQL fill:#8E44AD,stroke:#6C3483,color:#fff

Federal Compliance Alignment¶

🔀 Migration Paths¶

Between Fabric Stores¶

From External Systems¶

⚠️ Limitations¶

📚 References¶

🔗 Related Documents¶

• Cross-Database Queries -- Query across all three stores
• Direct Lake -- BI connectivity for Lakehouse and Warehouse
• Fabric SQL Database -- SQL Database deep dive
• Mirroring -- Replicate external databases into Fabric
• Medallion Architecture Deep Dive -- Lakehouse layering patterns
• Migration Patterns -- Migrating from external platforms

Back to Best Practices Index | Back to Documentation

📝 Document Metadata - Author: Documentation Team - Reviewers: Data Engineering, Data Warehouse, Architecture, Security - Classification: Internal - Next Review: 2026-07-21