Microsoft Fabric Lakehouse vs Warehouse: Engine, T-SQL Compatibility, Performance & Selection Criteria

The engines and development styles are fundamentally different. Lakehouse: Apache Spark engine, developed with PySpark / Spark SQL / Scala, notebook-based interactive development, Delta Lake format, flexible Schema-on-Read, optimized for Big Data / ML / Streaming. Warehouse: Microsoft SQL engine, T-SQL based, developed in SSMS / Azure Data Studio, Distributed Query Processing, strict Schema-on-Write, optimized for BI / reporting. Both run on the same OneLake (sharing the Delta Parquet format) and can be joined via Cross-warehouse Query. Data engineers (Lakehouse) and BI developers / SQL analysts (Warehouse) can collaborate on the same data foundation — the core of Microsoft Fabric's design philosophy. For new projects, a hybrid 'Lakehouse (Bronze/Silver) + Warehouse (Gold)' architecture is the standard pattern.

Cross-warehouse Query is a Fabric feature that lets you JOIN and reference Lakehouse SQL Endpoint and Warehouse together. Within the same workspace, you can reference tables in other data sources via a 3-part name (database.schema.table) or fully qualified name. Typical examples: 1) JOIN Bronze data in Lakehouse with Reference Data in Warehouse, 2) read Gold data from Warehouse in a Lakehouse Notebook, 3) hybrid analysis of Lakehouse Delta tables and Warehouse tables. This enables seamless collaboration where data engineers prepare data in Lakehouse and BI developers analyze it via integrated Warehouse + Lakehouse queries. Traditionally, Synapse required separate SQL Pool / Spark Pool deployments with ETL-based data duplication, but Fabric delivers architectural innovation by using Cross-warehouse Query for logical integration only — no duplication needed.

Warehouse T-SQL compatibility: full support for basic SELECT / INSERT / UPDATE / DELETE / MERGE, JOIN, Window Functions, and CTE, plus Stored Procedures, User-defined Functions, Sequence Objects, and Triggers. Lakehouse SQL Endpoint T-SQL: only read-only queries supported (SELECT / JOIN / CTE / Window Function), INSERT / UPDATE / DELETE not allowed (writes only via Spark Notebook), no Stored Procedures. Decision: if you need T-SQL DML operations (INSERT / UPDATE), choose Warehouse; for read-only analytics, Lakehouse SQL Endpoint is sufficient. Migration from SQL Server to Warehouse is generally a lift-and-shift, but some SQL Server-specific features (CLR, SSIS Jobs, etc.) are not supported, so a compatibility check up front is required.

Lakehouse Spark: Spark Job startup overhead (Cold Start of seconds to tens of seconds), highly parallel processing once started, performs best on large datasets (TB-PB scale), interactive performance accelerated by Notebook's Persistent Spark Session. Warehouse: instant query execution (no Cold Start), fast complex JOIN / aggregation queries (Distributed Query), good performance on small-to-medium scale (GB-TB), Result Set Caching accelerates repeated queries. Decision: 1) interactive short queries → Warehouse, 2) large-scale batch processing → Lakehouse, 3) ML training → Lakehouse Spark, 4) Power BI Direct Lake connection — both are equally fast (direct Delta Parquet read). In production, use both based on workload characteristics. Since Fabric Capacity (CU) is shared between them, cost management can be unified.

Microsoft's recommended selection criteria. Lakehouse recommended for: 1) teams with strong Spark / Python development experience, 2) large-scale batch processing (10 TB+), 3) ML / AI workloads, 4) semi-structured / unstructured data (JSON, images, video), 5) streaming data ingestion, 6) emphasis on Open Source Data Formats (Iceberg, Hudi, etc., future support), 7) notebook-based exploratory data analysis. Warehouse recommended for: 1) migration from existing SQL Server / Synapse Dedicated SQL Pool, 2) SQL analysts / BI developers as primary owners, 3) high-frequency analysis of medium-scale data (TB level), 4) Operational Data requiring ACID transactions, 5) complex business logic via Stored Procedures / Functions, 6) SQL connectivity from third-party BI tools (Tableau, Qlik). Both recommended for: 1) hybrid architecture with Bronze/Silver = Lakehouse + Gold = Warehouse, 2) collaboration environments combining data engineers (Lakehouse) and BI developers (Warehouse).

Both share a single billing model consuming Fabric Capacity (CU), with no extra cost. CU consumption pattern differences: Lakehouse Spark Job — heavy CU usage at startup, CU released after processing, zero consumption while idle. Warehouse Query — always consumes CU via background processes (Always Warm), with spikes during query execution. Decision: 1) batch-processing focus → Lakehouse is more CU-efficient (only consumes during processing), 2) constant BI access → expect Warehouse's continuous CU consumption, 3) for dev/test, leverage Lakehouse's pause-ability. Visualize CU consumption by workload via the Capacity Metrics App and right-size monthly. Thanks to Fabric's shared Capacity design, you don't need to budget Lakehouse and Warehouse separately — you can optimize across the entire workload.

Standard hybrid architecture: 1) Bronze Lakehouse (raw data ingestion, schema detection via Spark, notebook development), 2) Silver Lakehouse (cleansing, JOIN, data quality processing via Spark), 3) Gold Warehouse (aggregation, Star Schema, SQL analysts query directly, business logic via Stored Procedures), 4) Power BI Semantic Model with Direct Lake connection (via Gold Warehouse), 5) Pipeline orchestrating Lakehouse → Warehouse data movement, 6) Cross-warehouse Query references Lakehouse data from Warehouse. Access permissions: Lakehouse — write access only for data engineers, read-only for SQL analysts; Warehouse — BI developers and SQL analysts can implement business logic. Roles become clearly separated, dramatically improving organization-wide data utilization efficiency.

DP-700 (Fabric Data Engineer Associate) is the headline certification for this domain, deeply testing how to choose between the two. DP-600 (Fabric Analytics Engineer Associate) covers Warehouse + Power BI integration, AZ-305 (Solutions Architect Expert) covers Fabric architecture decisions from an architect's perspective, and AI-103 (GA 2026-06) covers Lakehouse + Azure AI Foundry integration scenarios. Understanding Microsoft Fabric's differentiating features is the key to passing DP-700 — hands-on practice with Cross-warehouse Query, Direct Lake, and Medallion implementation patterns is recommended.