Both the Fabric Lakehouse and Warehouse store Delta tables in OneLake — so why does picking the wrong one cause so much pain? This lesson explains the architectural differences that actually matter and shows you how to make a confident, defensible choice for any workload.
Your data pipeline is running clean. Transformed records are landing in Microsoft Fabric, and now you face the question that trips up practically every team at this stage: where exactly should this data live?
You've heard about Lakehouses and Warehouses. Maybe you've read the official docs, maybe you've seen some comparison charts. But those resources rarely tell you what happens when you pick the wrong one — the query that runs 40x slower than it should, the notebook that can't write to a table because the engine doesn't support it, or the six months of regret when your "simple" analytical store has grown into something your architecture can't support. This lesson exists to save you from those experiences.
By the end of this lesson, you'll be able to look at any data workload — a real one, with real complexity — and make a confident, defensible decision about which Fabric store to use. You'll understand the architectural differences that drive the practical tradeoffs, see where each store genuinely excels, and know how to recognize when you're fighting your own choice.
What you'll learn:
This lesson assumes you're comfortable with core Fabric concepts — workspaces, OneLake, and the general Fabric workload model. If you haven't worked through what Microsoft Fabric is and how its workloads fit together, start there. You should also have a working Fabric workspace; if you need one, setting up your first workspace walks you through F SKU trials and workspace configuration.
You should be comfortable with basic SQL and understand what Delta Lake format is in general terms. Experience with either Spark notebooks or T-SQL in an analytics context will help you follow the hands-on sections.
Before diving into differences, understand what both stores share: they both store data in OneLake, using Delta table format under the hood. This is not a minor implementation detail — it's the single most important architectural fact you need to hold in your head when comparing these two options.
Both a Lakehouse table and a Warehouse table are ultimately Delta Parquet files sitting in OneLake. What differs is who manages those files, which query engines can write to them, and what governance and transactional capabilities are layered on top.
This shared foundation means:
What it does not mean is that the two stores are interchangeable. The differences in write engines, transaction semantics, and SQL surface area are real and significant. Let's get into them.
When you create a Lakehouse in Fabric, you're creating a workspace artifact that owns a folder in OneLake. That folder has a Files section for raw or semi-structured data and a Tables section where Delta tables live.
The Lakehouse is designed to be written to by multiple engines:
That last point is critical. The SQL Analytics Endpoint on a Lakehouse is a read-only view of the Delta tables that Spark wrote. You cannot use T-SQL INSERT, UPDATE, DELETE, or CREATE TABLE statements against a Lakehouse through the SQL Analytics Endpoint. Many people discover this the hard way after spending an afternoon trying to write stored procedures that mutate data.
Warning
The SQL Analytics Endpoint on a Lakehouse is read-only for SQL clients. If your team's workflow involves data engineers writing T-SQL DML (INSERT, UPDATE, MERGE, DELETE), a Lakehouse is the wrong primary write destination. You'll need either a Warehouse or a pattern where Spark notebooks handle writes.
The Fabric Warehouse is a completely different artifact with a completely different write architecture. It presents a full T-SQL surface, including DDL and DML, backed by a serverless distributed SQL engine that Microsoft operates on your behalf.
When you write to a Warehouse, you're using T-SQL:
-- Create a table
CREATE TABLE sales.fact_orders (
order_id BIGINT,
customer_id INT,
order_date DATE,
product_id INT,
quantity INT,
unit_price DECIMAL(10,2),
total_amount DECIMAL(10,2)
);
-- Insert rows
INSERT INTO sales.fact_orders
SELECT
o.order_id,
o.customer_id,
CAST(o.order_datetime AS DATE) AS order_date,
od.product_id,
od.quantity,
od.unit_price,
(od.quantity * od.unit_price) AS total_amount
FROM staging.raw_orders o
JOIN staging.raw_order_details od ON o.order_id = od.order_id;
Under the hood, the Warehouse is still writing Delta files to OneLake — but those files are managed exclusively by the Warehouse SQL engine. Spark notebooks cannot write to Warehouse tables. You can read Warehouse tables from a Spark notebook using the Fabric SQL connector, but writes go through T-SQL only.
Key insight
The Lakehouse and Warehouse both store Delta tables in OneLake, but they use entirely different write engines. Lakehouse tables are written by Spark; Warehouse tables are written by the Warehouse SQL engine. Mixing write paths within a single artifact isn't supported — this is the single most common source of architectural confusion.
Both stores offer ACID transactions at the table level through Delta Lake's transaction log. But the Warehouse goes further: it supports multi-table transactions, which means you can wrap a sequence of operations across multiple tables in an explicit transaction and roll back if anything fails.
-- Warehouse: multi-table transaction
BEGIN TRANSACTION;
UPDATE sales.dim_customers
SET customer_tier = 'Gold'
WHERE lifetime_value > 10000;
INSERT INTO sales.customer_tier_history (customer_id, previous_tier, new_tier, changed_at)
SELECT customer_id, customer_tier, 'Gold', GETDATE()
FROM sales.dim_customers
WHERE lifetime_value > 10000;
COMMIT;
In a Lakehouse, each Spark write is atomic at the table level (Delta guarantees that), but there's no equivalent of BEGIN TRANSACTION that spans multiple tables. If your Spark job writes to dim_customers and then fails before writing to customer_tier_history, you'll have a partially updated state that you need to handle in application logic.
For analytical workloads where tables are refreshed in full rather than updated incrementally, this difference rarely matters. For operational or near-operational data patterns where partial updates are a genuine problem, the Warehouse's multi-table transaction support is meaningful.
Now that you understand the architecture, let's be concrete about which workloads belong where.
You're building a medallion architecture with Spark. If your data engineering team uses PySpark or Spark SQL to move data through bronze → silver → gold layers, the Lakehouse is the natural fit. The Spark API gives you full flexibility for complex transformations, schema evolution, partitioning strategies, and handling semi-structured data like JSON or Avro.
# Reading raw JSON from the Files section, cleaning, and writing a Delta table
from pyspark.sql.functions import col, to_date, trim, upper
raw_df = spark.read.json("Files/raw/orders/2024/")
clean_df = (raw_df
.filter(col("order_id").isNotNull())
.withColumn("order_date", to_date(col("order_datetime")))
.withColumn("customer_name", trim(upper(col("customer_name"))))
.select("order_id", "customer_id", "order_date", "product_id",
"quantity", "unit_price")
)
clean_df.write.format("delta").mode("overwrite").saveAsTable("silver_orders")
This kind of work — reading files, transforming with DataFrame operations, writing structured Delta tables — is exactly what the Lakehouse is designed for.
You have data scientists alongside data engineers. If your workspace serves both engineering (building pipelines) and science (building models), the Lakehouse gives both groups a home. Engineers write tables; data scientists read those same Delta tables directly in their notebooks and can also write model output tables or feature tables back to the Lakehouse.
You're ingesting diverse or unpredictable data. Files that arrive in inconsistent formats, schemas that evolve frequently, or sources you don't fully control yet — the Lakehouse handles these gracefully. You can land files in the Files section first, inspect them, then write structured tables once you understand the shape. The Warehouse, by contrast, requires you to know your schema up front.
Cost efficiency matters more than SQL convenience. Lakehouse queries through the SQL Analytics Endpoint use Fabric's shared compute infrastructure in a way that can be more cost-efficient for sporadic analytical queries. You're not paying for dedicated SQL compute.
Your transformation and serving layer is T-SQL-native. If your team writes stored procedures, uses MERGE statements for SCD Type 2 logic, or expects to use T-SQL windowing functions and CTEs as first-class citizens, the Warehouse is where you want to be. The SQL surface is rich and familiar.
-- SCD Type 2 merge in the Warehouse
MERGE INTO sales.dim_customers AS target
USING staging.customer_updates AS source
ON target.customer_id = source.customer_id
AND target.is_current = 1
WHEN MATCHED AND (
target.email <> source.email OR
target.customer_tier <> source.customer_tier
)
THEN UPDATE SET
target.is_current = 0,
target.valid_to = CAST(GETDATE() AS DATE)
WHEN NOT MATCHED BY TARGET
THEN INSERT (
customer_id, customer_name, email, customer_tier,
valid_from, valid_to, is_current
)
VALUES (
source.customer_id, source.customer_name, source.email,
source.customer_tier, CAST(GETDATE() AS DATE), NULL, 1
);
-- Insert new current records for updated customers
INSERT INTO sales.dim_customers (
customer_id, customer_name, email, customer_tier,
valid_from, valid_to, is_current
)
SELECT
source.customer_id, source.customer_name, source.email,
source.customer_tier, CAST(GETDATE() AS DATE), NULL, 1
FROM staging.customer_updates source
JOIN sales.dim_customers target
ON target.customer_id = source.customer_id
AND target.is_current = 0
AND target.valid_to = CAST(GETDATE() AS DATE);
This is clean, readable, and runs efficiently. Trying to replicate this in PySpark is possible but considerably more verbose and harder for SQL-trained analysts to maintain.
You need object-level security. The Warehouse supports column-level security, row-level security, and dynamic data masking — standard SQL Server / Synapse security patterns. If your governance requirements include those controls, the Warehouse is the only Fabric store that gives them to you natively through T-SQL.
-- Column-level security: deny access to PII column
DENY SELECT ON sales.dim_customers (email) TO [AnalystRole];
-- Dynamic data masking on a sensitive column
ALTER TABLE sales.dim_customers
ALTER COLUMN phone_number ADD MASKED WITH (FUNCTION = 'partial(0,"XXX-XXX-",4)');
The Lakehouse SQL Analytics Endpoint doesn't support these controls at the time of writing.
You're building a traditional dimensional model as the semantic layer. Star schema dimensional models with fact tables and dimensions, queried by Power BI or external SQL tools, are exactly what the Warehouse's SQL engine is optimized for. Query planning, statistics management, and the SQL optimizer all behave predictably in this pattern.
Your consumers are BI analysts who write SQL in familiar tools. The Warehouse connects via standard SQL Server drivers (TDS protocol). Any tool that can connect to Azure Synapse or SQL Server can connect to a Fabric Warehouse. That's a meaningful interoperability advantage if you have teams using SSMS, Azure Data Studio, or third-party BI tools alongside Power BI.
Tip
If your team includes both Spark-native data engineers and SQL-native BI analysts, consider a pattern where the Lakehouse serves as the bronze and silver layers (written by Spark) and the Warehouse serves as the gold layer (written by T-SQL from the silver data via shortcuts or COPY INTO). Each team works in their native environment.
Every Lakehouse automatically gets a SQL Analytics Endpoint — a read-only T-SQL interface that sits in front of the Lakehouse's Delta tables. You don't create it; it appears automatically when you create the Lakehouse.
You can query it with any SQL client that supports the Fabric connection string. Power BI can connect to it for Import or DirectQuery mode. You can write views and functions against it (though these are read-only constructs).
This endpoint is what makes the Lakehouse genuinely useful for SQL-trained analysts even when the write path is Spark. After your Spark pipeline runs, the resulting Delta tables are immediately queryable through the endpoint:
-- Querying a Lakehouse table through the SQL Analytics Endpoint
SELECT
YEAR(order_date) AS order_year,
MONTH(order_date) AS order_month,
SUM(total_amount) AS revenue,
COUNT(DISTINCT customer_id) AS unique_customers
FROM silver_orders
WHERE order_date >= '2024-01-01'
GROUP BY YEAR(order_date), MONTH(order_date)
ORDER BY order_year, order_month;
What you cannot do through the SQL Analytics Endpoint:
Note
Views and functions you create in the SQL Analytics Endpoint are stored as Warehouse objects in a special metadata layer, not as Delta tables. They persist across sessions but they're not stored in the Lakehouse's OneLake folder. Be aware of this when planning migrations or workspace governance.
The SQL Analytics Endpoint is also how Power BI achieves Direct Lake connectivity on Lakehouse tables, which delivers the performance of in-memory analysis without requiring a full data import. If you're building your first Lakehouse in Microsoft Fabric and planning to connect Power BI to it, the SQL Analytics Endpoint is the connection surface you'll use.
Both stores are fast. Both are distributed. Choosing based on raw query performance alone is usually the wrong frame. What matters more is which workloads run well on each engine.
Lakehouse queries through the SQL Analytics Endpoint are served by a separate SQL compute pool that reads the Delta files in OneLake. Query performance depends heavily on:
order_date and your query filters on order_date, the engine skips unneeded Parquet files. Poorly partitioned tables read far more data than necessary.OPTIMIZE periodically or configure your Spark writes to produce reasonably sized files.ANALYZE TABLE in Spark helps.# After writing, optimize the table layout
spark.sql("OPTIMIZE silver_orders ZORDER BY (customer_id, order_date)")
The Warehouse SQL engine has its own compute model and maintains its own statistics automatically. For star schema queries with large fact tables and small dimensions, the Warehouse optimizer generally produces excellent plans.
Where the Warehouse can struggle:
Key insight
For initial data landing and heavy transformation, Spark (and therefore Lakehouse) has a throughput advantage. For structured analytical queries against a well-defined dimensional model, the Warehouse SQL engine often has a latency advantage for complex multi-join queries because its optimizer has richer statistics.
In production, the choice isn't always binary. Many mature Fabric architectures use both stores deliberately:
Source Systems
│
▼
[Lakehouse: Bronze] ← Data pipelines / Dataflow Gen2 land raw files
│
▼ (Spark notebooks)
[Lakehouse: Silver] ← Cleaned, conformed Delta tables
│
▼ (T-SQL COPY or shortcut + CTAS)
[Warehouse: Gold] ← Dimensional model, secured, queried by Power BI
In this pattern, shortcuts let the Warehouse reference the Silver Lakehouse tables without copying data:
-- In the Warehouse: create an external table pointing to a Lakehouse shortcut
-- (Shortcuts appear as tables once the Lakehouse is added via cross-database querying)
-- Cross-database query to pull silver data into the Warehouse gold layer
INSERT INTO gold.fact_orders
SELECT
order_id,
customer_id,
order_date,
product_id,
quantity,
unit_price,
quantity * unit_price AS total_amount
FROM silver_lakehouse.dbo.silver_orders
WHERE order_date = CAST(GETDATE() AS DATE);
This cross-workspace or cross-artifact querying is one of Fabric's genuine differentiators — you can query Lakehouse tables from Warehouse SQL without staging data through external systems.
Some organizations run parallel tracks: a Warehouse serves the Finance and Operations domains that have stable schemas and governance requirements, while a Lakehouse serves the Data Science team that needs schema flexibility and Spark compute for model training.
Both can exist in the same workspace. Power BI can serve semantic models from either or both.
If your team is Spark-native and your analysts are comfortable with the SQL Analytics Endpoint, you might keep the entire pipeline in the Lakehouse and only expose a Warehouse to external BI consumers who need row-level security or column masking. The Warehouse in this pattern is thin — mostly views and security policies, not a data storage layer in its own right.
When you're sitting across from your team trying to make the call, run through these questions in order:
1. Who writes data, and what tool do they use?
2. Do you need T-SQL DML (INSERT, UPDATE, DELETE, MERGE) as your write path?
3. Do you need multi-table transactions?
4. Do you need column-level security, row-level security, or dynamic data masking?
5. Does your data have unpredictable schema, semi-structured content, or heavy file-level manipulation?
6. Who are your end consumers, and what tools do they use?
7. What's your team's existing skill set?
In this exercise, you'll build a realistic decision artifact: a mini data architecture for a retail analytics scenario, using both a Lakehouse and a Warehouse, and experience the difference between the two write paths firsthand.
Scenario: You work for a mid-sized retail company. Your data engineering team runs Spark pipelines that process daily order exports from an e-commerce platform. Your BI team uses Power BI and writes SQL in SSMS. Finance requires row-level security so regional managers only see their own region's data.
retail_silver.from pyspark.sql import Row
from pyspark.sql.functions import col, to_date, current_date
from datetime import date, timedelta
import random
# Generate synthetic order data
regions = ["Northeast", "Southeast", "Midwest", "West"]
products = [101, 102, 103, 104, 105]
rows = []
for i in range(1, 10001):
order_date = date(2024, 1, 1) + timedelta(days=random.randint(0, 364))
rows.append(Row(
order_id=i,
customer_id=random.randint(1, 500),
order_date=order_date.isoformat(),
product_id=random.choice(products),
region=random.choice(regions),
quantity=random.randint(1, 10),
unit_price=round(random.uniform(9.99, 299.99), 2)
))
df = spark.createDataFrame(rows)
df = df.withColumn("order_date", to_date(col("order_date"))) \
.withColumn("total_amount", col("quantity") * col("unit_price"))
df.write.format("delta") \
.mode("overwrite") \
.partitionBy("region") \
.saveAsTable("silver_orders")
print(f"Wrote {df.count()} rows to silver_orders")
region — you'll see four subfolders in the table's Delta directory.SELECT
region,
COUNT(*) AS order_count,
SUM(total_amount) AS total_revenue,
AVG(total_amount) AS avg_order_value
FROM silver_orders
GROUP BY region
ORDER BY total_revenue DESC;
-- This will fail — note the error message
INSERT INTO silver_orders (order_id, customer_id, order_date, product_id, region, quantity, unit_price, total_amount)
VALUES (99999, 1, '2024-12-01', 101, 'West', 2, 49.99, 99.98);
Observe the error. This is the read-only boundary you need to internalize.
retail_gold.-- Create schema
CREATE SCHEMA sales;
GO
-- Dimension: regions with manager mapping
CREATE TABLE sales.dim_regions (
region_id INT,
region_name VARCHAR(50),
manager VARCHAR(100)
);
INSERT INTO sales.dim_regions VALUES
(1, 'Northeast', 'alice@retail.com'),
(2, 'Southeast', 'bob@retail.com'),
(3, 'Midwest', 'carol@retail.com'),
(4, 'West', 'dave@retail.com');
-- Fact table (you'd populate this from the Lakehouse via cross-database query in production)
CREATE TABLE sales.fact_orders (
order_id BIGINT,
customer_id INT,
order_date DATE,
product_id INT,
region_name VARCHAR(50),
quantity INT,
unit_price DECIMAL(10,2),
total_amount DECIMAL(10,2)
);
-- Create a security predicate function
CREATE FUNCTION sales.fn_region_security(@region_name VARCHAR(50))
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS fn_result
WHERE @region_name = SESSION_CONTEXT(N'region')
OR IS_ROLEMEMBER('db_owner') = 1;
GO
-- Apply the security policy
CREATE SECURITY POLICY sales.RegionFilter
ADD FILTER PREDICATE sales.fn_region_security(region_name)
ON sales.fact_orders
WITH (STATE = ON);
Note
This is a demonstration of RLS syntax in the Warehouse. In a real implementation, the session context would be set based on the authenticated user's identity, often through a Power BI semantic model that sets the context via username mapping.
You've now touched both write paths and both query paths. Reflect on these questions:
This is the most common mistake. Someone creates a Lakehouse, connects via the SQL endpoint, and immediately tries to run INSERT INTO. The error message is clear but unexpected if you didn't know the constraint going in.
Fix: Use a Spark notebook or Dataflow Gen2 for writes to a Lakehouse. If you need SQL DML writes, use a Warehouse.
The reverse error: assuming that because both stores are "Delta tables in OneLake," a Spark notebook can write directly to a Warehouse table.
Fix: Spark cannot write directly to Warehouse tables. To move data from a Spark notebook into a Warehouse, write to a Lakehouse first, then use a T-SQL INSERT INTO ... SELECT or pipeline copy activity from the Lakehouse to the Warehouse.
Streaming ingestion or many small Spark writes produce hundreds or thousands of tiny Parquet files. The SQL Analytics Endpoint then has to open and scan all of them for any query, making performance dreadful.
Fix: Run OPTIMIZE on heavily written tables. In Spark SQL:
spark.sql("OPTIMIZE silver_orders")
# Or with Z-ordering for frequently filtered columns:
spark.sql("OPTIMIZE silver_orders ZORDER BY (customer_id, product_id)")
Schedule this in your pipeline after each major write cycle.
Warehouses have schema-on-write only. If you're doing exploratory analysis on messy, semi-structured, or constantly changing data, the Warehouse will slow you down with schema definition overhead.
Fix: Keep exploration in the Lakehouse. Move finalized, stable tables to the Warehouse only when the schema is settled and governance requirements kick in.
Views you create in the SQL Analytics Endpoint are visible in the endpoint UI and queryable from SQL clients, but they're not stored in the Lakehouse's Delta folder structure. Teams who assume they can recreate a Lakehouse and have these views reappear are surprised when they don't.
Fix: Document SQL Analytics Endpoint views separately. Consider whether governance-heavy SQL objects belong in a Warehouse instead, where DDL objects are managed more explicitly.
Warning
If you delete and recreate a Lakehouse, any views, functions, or other SQL objects created through the SQL Analytics Endpoint are lost — only the Delta tables in OneLake survive. Always script out these objects and store them in source control.
Here's what you now know that you didn't before:
| Dimension | Lakehouse | Warehouse |
|---|---|---|
| Write engine | Spark / Dataflow Gen2 | T-SQL (SQL engine) |
| Read via SQL | Read-only endpoint | Full read/write |
| T-SQL DML | Not supported | Fully supported |
| Multi-table transactions | Not supported | Supported |
| Column/Row-level security | Not supported | Fully supported |
| Schema on | Read (flexible) | Write (strict) |
| Best for | ETL/ELT with Spark, ML, exploratory | Dimensional models, SQL teams, governed serving |
| Both support | Delta format, OneLake, Direct Lake, Power BI | Same |
The real-world architecture for most mid-to-large teams is a hybrid: Lakehouse handles ingestion and transformation, Warehouse handles governed serving to SQL consumers. The Lakehouse's SQL Analytics Endpoint provides a useful SQL surface without requiring a Warehouse for every read workload.
Your next immediate steps:
The choice between Lakehouse and Warehouse isn't a one-time architectural decision you make at the start of a project and forget. It's a live consideration as your workloads evolve. Teams that understand the architectural reasons behind the tradeoffs — not just the comparison chart — are the ones who make the right call when the workload changes six months later.