Browse cheat sheets · Microsoft Fabric
Lakehouse vs Warehouse
Choose between Microsoft Fabric Lakehouse and Warehouse by comparing development model, data types, SQL capabilities, transactions, ingestion patterns, and downstream analytics requirements.
On this page
- Problem
- Quick answer
- Code / commands
- 1. Use Lakehouse when Spark is the primary transformation engine
- 2. Query Lakehouse Delta tables through the SQL analytics endpoint
- 3. Use Warehouse when T-SQL is the primary development model
- 4. Use Warehouse for dimensional models
- 5. Use Lakehouse for mixed data types
- 6. Use shortcuts in a Lakehouse when copying data is unnecessary
- 7. Use Warehouse when multi-table transactions matter
- 8. Use Lakehouse for medallion architecture
- 9. Use Warehouse as a governed serving layer after Lakehouse engineering
- 10. Use cross-database queries when data already exists in Fabric
- 11. Use COPY INTO for Warehouse ingestion when SQL loading is preferred
- 12. Use CTAS in Warehouse for SQL transformations
- 13. Do not use Warehouse as a raw-file landing zone
- 14. Do not use the Lakehouse SQL endpoint as a substitute for Warehouse DML
- 15. Choose based on primary developer workflow
- 16. Choose based on source data shape
- 17. Choose based on transaction requirements
- 18. Choose based on serving requirements
- 19. Validate whether both layers are actually needed
- What the result means
- What to look for
- Team keeps moving data between Lakehouse and Warehouse unnecessarily
- Analysts need frequent SQL writes or stored procedures
- Engineers keep using Spark against raw/semi-structured data
- Warehouse contains only copies of Lakehouse gold tables
- Lakehouse SQL users expect a full SQL database
- Production notes
- Both use OneLake and open Delta storage
- The Lakehouse SQL analytics endpoint is read-oriented
- Warehouse provides full SQL warehousing capabilities
- Lakehouse is the broader data-engineering surface
- Do not duplicate data without a reason
- Performance is workload-dependent
- Governance should span both
- Common mistakes
- 1. Choosing Lakehouse only because the word sounds more modern
- 2. Choosing Warehouse only because the team knows SQL
- 3. Assuming Lakehouse SQL endpoint supports full Warehouse DML
- 4. Assuming Warehouse does not use Delta/OneLake
- 5. Copying every Lakehouse table into Warehouse
- 6. Treating the Lakehouse as only a raw zone
- 7. Treating Warehouse as an ETL landing zone for arbitrary files
- 8. Ignoring transaction requirements
- Next step
- Related deep dives
Problem
You are designing a Microsoft Fabric solution and need to decide whether data should live in a Lakehouse, a Warehouse, or both.
Both use OneLake and open Delta storage, so the decision is not simply “data lake vs database.”
The real questions are:
- Will engineers work primarily in Spark or T-SQL?
- Is the data structured only, or also semi-structured/unstructured?
- Do you need multi-table SQL transactions?
- Will data be transformed in notebooks?
- Do analysts need a SQL-first relational experience?
- Is the workload primarily engineering, BI, or both?
- Do you need shortcuts and file-oriented access?
- Should the curated serving layer expose full T-SQL DML and warehouse objects?
Quick answer
Use this as the starting decision:
Need Spark / Python / notebooks?
→ Lakehouse
Need full T-SQL DML, stored procedures, and multi-table transactions?
→ Warehouse
Need structured + semi-structured/unstructured data?
→ Lakehouse
Need a SQL-first dimensional serving layer?
→ Warehouse
Need both engineering and enterprise SQL serving?
→ Often Lakehouse + Warehouse
A common production architecture is:
- Sources
- Lakehouse
- Bronze
- Silver
- Curated Delta
- WarehouseDimensional / reporting layer
- Power BI / SQL consumers
Do not add both automatically.
Use both only when each has a clear responsibility.
Code / commands
1. Use Lakehouse when Spark is the primary transformation engine
Example PySpark:
from pyspark.sql import functions as F
sales = (
spark.read
.format("delta")
.load("Tables/sales")
)
daily_sales = (
sales
.filter(F.col("sale_date") >= "2026-10-01")
.groupBy(
"sale_date",
"merchant_id",
)
.agg(
F.sum("amount").alias("revenue"),
F.countDistinct("sale_id").alias("sales"),
)
)
Write back to a Delta table:
(
daily_sales.write
.format("delta")
.mode("overwrite")
.saveAsTable("sales_daily")
)
Lakehouse is the natural fit when:
- PySpark is central
- notebooks perform engineering transformations
- files and Delta tables coexist
- medallion architecture is used
- shortcuts are part of the design
2. Query Lakehouse Delta tables through the SQL analytics endpoint
Each Lakehouse exposes a SQL analytics endpoint for querying Delta tables.
Example:
SELECT
merchant_id,
SUM(revenue) AS total_revenue
FROM dbo.sales_daily
GROUP BY
merchant_id
ORDER BY
total_revenue DESC;
This is useful for:
- SQL exploration
- BI consumption
- views
- reporting queries
The Lakehouse SQL analytics endpoint is primarily read-oriented.
Do not design it as if it were a full read/write SQL warehouse.
3. Use Warehouse when T-SQL is the primary development model
Example:
CREATE TABLE dbo.FactSales
(
SaleId bigint NOT NULL,
MerchantId int NOT NULL,
SaleDate date NOT NULL,
Amount decimal(18,2) NOT NULL
);
Insert data:
INSERT INTO dbo.FactSales
(
SaleId,
MerchantId,
SaleDate,
Amount
)
VALUES
(
1001,
25,
'2026-10-04',
125.50
);
Warehouse is a better fit when engineers and analysts need:
- SQL DDL
- SQL DML
- stored procedures
- functions
- multi-table transactions
- relational modeling
4. Use Warehouse for dimensional models
Example schema:
FactSales
DimMerchant
DimCustomer
DimDate
DimProduct
Typical SQL:
SELECT
d.CalendarMonth,
m.MerchantName,
SUM(f.Amount) AS Revenue
FROM dbo.FactSales AS f
INNER JOIN dbo.DimDate AS d
ON d.DateKey = f.SaleDate
INNER JOIN dbo.DimMerchant AS m
ON m.MerchantId = f.MerchantId
GROUP BY
d.CalendarMonth,
m.MerchantName;
This SQL-first serving pattern aligns naturally with Warehouse.
5. Use Lakehouse for mixed data types
Lakehouse can keep raw files and managed Delta tables together.
Example logical structure:
Lakehouse
├── Files
│ ├── raw-json/
│ ├── csv/
│ ├── images/
│ └── landing/
└── Tables
├── bronze_events
├── silver_sales
└── gold_customer_activity
Use this when one workload must work with:
- Delta tables
- CSV
- JSON
- files
- semi-structured data
- unstructured content
Warehouse is designed around structured relational tables.
6. Use shortcuts in a Lakehouse when copying data is unnecessary
A Lakehouse can expose data through OneLake shortcuts.
Conceptually:
- External storage
- OneLake Shortcut
- Lakehouse
- Spark / SQL / Power BI
Use this when:
- data already exists in an accessible source
- copying would create unnecessary duplication
- live access is acceptable for the workload
Do not copy data simply because a new Fabric item was created.
7. Use Warehouse when multi-table transactions matter
Example:
BEGIN TRANSACTION;
UPDATE dbo.FactOrder
SET Status = 'Closed'
WHERE OrderId = 1001;
INSERT INTO dbo.OrderAudit
(
OrderId,
EventType,
EventTime
)
VALUES
(
1001,
'Closed',
SYSUTCDATETIME()
);
COMMIT TRANSACTION;
This kind of multi-table transactional SQL workflow is a Warehouse use case.
Lakehouse Delta tables provide transactional behavior at the Delta-table level, but the Lakehouse is not the choice for full multi-table T-SQL transaction semantics.
8. Use Lakehouse for medallion architecture
Example:
Bronze
raw source-aligned Delta
Silver
validated / standardized Delta
Gold
curated analytical Delta
This is a strong Lakehouse pattern because Spark and Delta are first-class components.
Example:
bronze = spark.table("bronze_sales")
silver = (
bronze
.dropDuplicates(["sale_id"])
.filter("sale_id IS NOT NULL")
)
(
silver.write
.format("delta")
.mode("overwrite")
.saveAsTable("silver_sales")
)
9. Use Warehouse as a governed serving layer after Lakehouse engineering
Example architecture:
- Source systems
- Lakehouse Bronze
- Lakehouse Silver
- Curated data
- Warehouse
- Power BI / SQL applications
This pattern is useful when:
- engineering prefers Spark
- reporting prefers SQL
- dimensional modeling matters
- stored procedures or SQL DML are needed
Do not duplicate every Lakehouse table into Warehouse.
Promote only the data that benefits from the SQL serving layer.
10. Use cross-database queries when data already exists in Fabric
Fabric supports querying across Warehouse and Lakehouse SQL endpoints without requiring a traditional copy for every analytical scenario.
Conceptually:
SELECT ...
FROM SomeWarehouse.dbo.FactSales AS f
JOIN SomeLakehouse.dbo.DimMerchant AS m
ON ...
Exact naming and accessibility depend on workspace/item context.
Use cross-item access to reduce unnecessary duplication when the workload supports it.
11. Use COPY INTO for Warehouse ingestion when SQL loading is preferred
Example:
COPY INTO dbo.FactSales
FROM 'https://<storage-path>/sales/'
WITH
(
FILE_TYPE = 'PARQUET'
);
Use Warehouse-native ingestion when:
- the destination is relational
- T-SQL is the team’s operating model
- the data is already curated enough for warehouse loading
For large engineering transformations, perform them before loading the serving layer.
12. Use CTAS in Warehouse for SQL transformations
Example:
CREATE TABLE dbo.SalesSummary
AS
SELECT
MerchantId,
SaleDate,
SUM(Amount) AS Revenue
FROM dbo.FactSales
GROUP BY
MerchantId,
SaleDate;
Use SQL-native transformations where that keeps the architecture simpler.
Do not move data into Spark solely because Spark exists.
13. Do not use Warehouse as a raw-file landing zone
Poor fit:
Warehouse
raw JSON
images
CSV files
telemetry blobs
Better:
- Lakehouse Files
- Delta transformation
- WarehouseOnly if relational serving is needed
Warehouse is for structured relational analytics, not arbitrary file storage.
14. Do not use the Lakehouse SQL endpoint as a substitute for Warehouse DML
Avoid designing around assumptions such as:
INSERT ...
UPDATE ...
DELETE ...
against Lakehouse tables through the SQL analytics endpoint as if it were a full SQL warehouse.
Use:
- Spark
- pipelines
- supported Lakehouse write paths
for Lakehouse mutation.
Use Warehouse when SQL write semantics are a central requirement.
15. Choose based on primary developer workflow
Use this mapping:
PySpark / Python / Scala / Spark SQL
→ Lakehouse
T-SQL
→ Warehouse
Mixed engineering + SQL serving
→ Lakehouse + Warehouse
Technology preference alone should not be the only factor, but it is a strong design signal.
16. Choose based on source data shape
Structured only
→ Warehouse candidate
Structured + semi-structured + files
→ Lakehouse candidate
Raw ingestion + transformation
→ Lakehouse candidate
Curated star schema
→ Warehouse candidate
Do not force semi-structured landing workloads into a relational model too early.
17. Choose based on transaction requirements
Single Delta-table transactional writes
→ Lakehouse can fit
Multi-table SQL transaction
→ Warehouse
This distinction is important for workloads that require coordinated relational updates.
18. Choose based on serving requirements
Use Warehouse when consumers need a familiar relational surface with:
- tables
- views
- procedures
- functions
- T-SQL DML
Use Lakehouse when consumers can work from:
- Delta tables
- Spark
- SQL analytics endpoint
- Direct Lake / Fabric semantic models
19. Validate whether both layers are actually needed
Before building:
Lakehouse → Warehouse
ask:
What capability does Warehouse add here?
Good answers include:
- full T-SQL write surface
- multi-table transactions
- SQL-first development
- dimensional serving
- stored procedures
- enterprise relational workload
Weak answer:
"Because every Fabric architecture should have both."
It should not.
What the result means
| Decision factor | Lakehouse | Warehouse |
|---|---|---|
| Primary development | Spark / notebooks | T-SQL |
| Data types | Structured + semi/unstructured | Structured |
| Storage | Delta in OneLake | Delta in OneLake |
| Spark access | Native | Not primary development model |
| SQL querying | SQL analytics endpoint | Full SQL warehouse |
| T-SQL DML | Not through Lakehouse SQL endpoint | Supported |
| Multi-table transactions | Not the primary model | Supported |
| Files | First-class | Not the target use case |
| Medallion architecture | Strong fit | Usually serving layer |
| Dimensional modeling | Possible, but not its main differentiator | Strong fit |
| Shortcuts | Strong Lakehouse pattern | Can interoperate through Fabric/OneLake patterns |
| Stored procedures | Not Lakehouse SQL endpoint model | Supported |
| Typical role | Engineering / transformation | Relational serving / BI |
What to look for
Team keeps moving data between Lakehouse and Warehouse unnecessarily
You may have duplicated architecture.
Ask whether:
- cross-item access is sufficient
- consumers can query the Lakehouse SQL endpoint
- Warehouse adds actual SQL-serving capability
Analysts need frequent SQL writes or stored procedures
Warehouse is likely the better serving layer.
Do not force those workflows into notebook-based mutation.
Engineers keep using Spark against raw/semi-structured data
Lakehouse is likely the natural core storage layer.
Do not relationalize everything prematurely.
Warehouse contains only copies of Lakehouse gold tables
Check whether the duplicate layer adds:
- dimensional structure
- SQL DML
- procedures
- transactional logic
- governance/serving value
If not, the extra layer may be unnecessary.
Lakehouse SQL users expect a full SQL database
Clarify that the SQL analytics endpoint is primarily a read/serving surface for Lakehouse Delta tables.
If full T-SQL write capability is required, use Warehouse.
Production notes
Both use OneLake and open Delta storage
The difference is primarily workload and development model, not a completely different storage technology.
That makes Lakehouse and Warehouse complementary rather than mutually exclusive.
The Lakehouse SQL analytics endpoint is read-oriented
It supports T-SQL querying and selected metadata objects such as views/functions, but it is not the same as Warehouse’s full SQL DDL/DML surface.
Architect accordingly.
Warehouse provides full SQL warehousing capabilities
Warehouse supports:
- DQL
- DML
- DDL
- stored procedures
- functions
- multi-table transactions
This makes it the SQL-first relational option.
Lakehouse is the broader data-engineering surface
Lakehouse supports:
- files
- Delta tables
- Spark
- notebooks
- SQL querying
- shortcuts
- medallion design
It is generally the more flexible choice for ingestion and transformation.
Do not duplicate data without a reason
OneLake is designed to reduce unnecessary copies.
Before copying between Fabric items, ask whether:
- shortcuts
- SQL cross-item querying
- Direct Lake
- shared Delta storage patterns
can meet the requirement.
Performance is workload-dependent
Do not assume:
Warehouse is always faster
or:
Lakehouse is always more scalable
Performance depends on:
- query shape
- table design
- data layout
- concurrency
- engine
- workload type
- capacity
Benchmark the actual workload.
Governance should span both
Security and governance should follow the data product, not the storage-label choice.
Document:
- ownership
- lineage
- access model
- serving contract
- retention
- quality expectations
for either architecture.
Common mistakes
1. Choosing Lakehouse only because the word sounds more modern
Choose based on workload, not terminology.
2. Choosing Warehouse only because the team knows SQL
If ingestion includes large-scale files, Spark, or semi-structured data, forcing everything into Warehouse may add unnecessary friction.
3. Assuming Lakehouse SQL endpoint supports full Warehouse DML
It does not.
Use the correct write path.
4. Assuming Warehouse does not use Delta/OneLake
Fabric Warehouse stores data in OneLake in Delta format.
The difference is the user/development experience and workload capabilities.
5. Copying every Lakehouse table into Warehouse
This adds:
- duplication
- orchestration
- latency
- maintenance
without necessarily adding value.
6. Treating the Lakehouse as only a raw zone
A Lakehouse can contain curated Delta tables and support analytical SQL access.
It is not limited to Bronze/raw data.
7. Treating Warehouse as an ETL landing zone for arbitrary files
Warehouse is optimized for structured relational analytics.
Use Lakehouse/OneLake file storage for raw/semi-structured landing.
8. Ignoring transaction requirements
Multi-table SQL transactions are a real architectural decision point.
If they matter, Warehouse becomes much more compelling.
Was this helpful?
What could be better?
Thanks — that helps me improve Javi Builds.
Stay in the loop
Get the Javi Builds updates you actually care about.
Check your inbox
I sent you a confirmation link. Your subscription will be active after you confirm it.