Introduction to Microsoft Fabric Lakehouse vs Warehouse: which one to use, and how they share data in OneLake

#The short answer

Both store tables as Delta Lake (Parquet files + transaction log) in OneLake, so data in one is readable from the other without copying.

#What a Lakehouse is

A Lakehouse item gives you:

• A Files area: any files (CSV, JSON, Parquet, images). Not tables.
• A Tables area: Delta tables, created by Spark, Dataflows or pipelines.
• An automatically created SQL analytics endpoint: a read-only T-SQL view of the Delta tables.
• A default semantic model for Power BI (behaviour varies by tenant settings and release).

You write to it with Spark (notebooks, Spark job definitions), Dataflows Gen2 or pipelines. Through the SQL analytics endpoint you can SELECT, create views, functions and grant permissions, but you can't INSERT/UPDATE/DELETE table data.

# Fabric notebook attached to a Lakehouse
df = spark.read.option("header", True).csv("Files/landing/orders/2026-11-01.csv")
(df.withColumnRenamed("Order Id", "order_id")
   .write.mode("append").format("delta").saveAsTable("orders_bronze"))

#What a Warehouse is

A Warehouse is a full T-SQL data warehouse on the same storage:

• CREATE TABLE, INSERT, UPDATE, DELETE, MERGE, COPY INTO in T-SQL.
• Multi-statement, multi-table transactions.
• Stored procedures for ELT.
• Tables are still Delta in OneLake, so Spark can read them (read-only from Spark).

-- Fabric Warehouse
CREATE TABLE dbo.dim_customer (
    customer_id   INT NOT NULL,
    customer_name VARCHAR(200),
    city          VARCHAR(100),
    updated_at    DATETIME2(6)
);

COPY INTO dbo.dim_customer
FROM 'https://<storage>.dfs.core.windows.net/landing/customers/*.parquet'
WITH (FILE_TYPE = 'PARQUET');

Fabric Warehouse supports a subset of T-SQL compared with SQL Server; check the documented T-SQL surface area (some data types, identity behaviour and features differ) before migrating code.

#Side-by-side

#How they share data

Cross-database queries: within a workspace, the Warehouse can query Lakehouse tables using three-part names:

SELECT o.order_id, c.customer_name
FROM   lh_sales.dbo.orders_silver AS o
JOIN   dbo.dim_customer           AS c ON c.customer_id = o.customer_id;

Shortcuts: a Lakehouse can contain a shortcut to tables in another Lakehouse, a Warehouse, ADLS Gen2, S3 or other supported sources. The data isn't copied; reads go to the source.

Tip

The SQL analytics endpoint syncs Delta table metadata in the background. Right after a Spark write, a query through the endpoint can briefly show old data. If freshness matters to the second, refresh the endpoint metadata or read via Spark.

#A simple pattern that works

Sources ──► Lakehouse (bronze: raw Delta + Files)
               │  Spark notebooks
               ▼
           Lakehouse (silver: cleaned, conformed)
               │  T-SQL stored procs (cross-database) or Spark
               ▼
           Warehouse or Lakehouse (gold: star schema)
               │
               ▼
           Power BI semantic model (Direct Lake)

• Keep bronze and silver in Lakehouses so engineers can use Spark freely.
• Build gold in a Warehouse if your modellers want T-SQL procedures and transactions; otherwise a Lakehouse gold layer is fine.
• Point Power BI at gold with Direct Lake to avoid import refreshes.

#Permissions basics

• Workspace roles (Admin, Member, Contributor, Viewer) give broad access to all items.
• For finer control, share individual items and use SQL GRANT on the SQL analytics endpoint or Warehouse:

GRANT SELECT ON SCHEMA::sales TO [analysts@example.com];

• Lakehouse Files and Spark access are governed by item/workspace permissions and OneLake security features; check the current OneLake data access roles documentation for folder-level control.

#Capacity and performance notes

• Both consume capacity units (CU) from the same Fabric capacity. A heavy Spark job and a busy Warehouse compete.
• Delta table health matters to both: run OPTIMIZE (and V-Order, where enabled) on Lakehouse tables that receive many small writes.
• Watch the Fabric Capacity Metrics app for throttling before blaming either engine.

#Prevent problems later

• Decide per layer which item type owns it, and write it down; mixed ownership causes "who changed this table" arguments.
• Name items by layer and domain (lh_sales_bronze, wh_sales_gold).
• Keep notebooks and SQL in Git (Fabric Git integration) and deploy with deployment pipelines.
• Don't build reports on bronze or silver tables.