Browse cheat sheets · Microsoft Fabric

Technology: Microsoft FabricCategory: ArchitectureDifficulty: IntermediateUpdated

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
  1. Problem
  2. Quick answer
  3. Code / commands
  4. 1. Use Lakehouse when Spark is the primary transformation engine
  5. 2. Query Lakehouse Delta tables through the SQL analytics endpoint
  6. 3. Use Warehouse when T-SQL is the primary development model
  7. 4. Use Warehouse for dimensional models
  8. 5. Use Lakehouse for mixed data types
  9. 6. Use shortcuts in a Lakehouse when copying data is unnecessary
  10. 7. Use Warehouse when multi-table transactions matter
  11. 8. Use Lakehouse for medallion architecture
  12. 9. Use Warehouse as a governed serving layer after Lakehouse engineering
  13. 10. Use cross-database queries when data already exists in Fabric
  14. 11. Use COPY INTO for Warehouse ingestion when SQL loading is preferred
  15. 12. Use CTAS in Warehouse for SQL transformations
  16. 13. Do not use Warehouse as a raw-file landing zone
  17. 14. Do not use the Lakehouse SQL endpoint as a substitute for Warehouse DML
  18. 15. Choose based on primary developer workflow
  19. 16. Choose based on source data shape
  20. 17. Choose based on transaction requirements
  21. 18. Choose based on serving requirements
  22. 19. Validate whether both layers are actually needed
  23. What the result means
  24. What to look for
  25. Team keeps moving data between Lakehouse and Warehouse unnecessarily
  26. Analysts need frequent SQL writes or stored procedures
  27. Engineers keep using Spark against raw/semi-structured data
  28. Warehouse contains only copies of Lakehouse gold tables
  29. Lakehouse SQL users expect a full SQL database
  30. Production notes
  31. Both use OneLake and open Delta storage
  32. The Lakehouse SQL analytics endpoint is read-oriented
  33. Warehouse provides full SQL warehousing capabilities
  34. Lakehouse is the broader data-engineering surface
  35. Do not duplicate data without a reason
  36. Performance is workload-dependent
  37. Governance should span both
  38. Common mistakes
  39. 1. Choosing Lakehouse only because the word sounds more modern
  40. 2. Choosing Warehouse only because the team knows SQL
  41. 3. Assuming Lakehouse SQL endpoint supports full Warehouse DML
  42. 4. Assuming Warehouse does not use Delta/OneLake
  43. 5. Copying every Lakehouse table into Warehouse
  44. 6. Treating the Lakehouse as only a raw zone
  45. 7. Treating Warehouse as an ETL landing zone for arbitrary files
  46. 8. Ignoring transaction requirements
  47. Next step
  48. 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:

  1. Sources
  2. Lakehouse
    • Bronze
    • Silver
    • Curated Delta
  3. WarehouseDimensional / reporting layer
  4. Power BI / SQL consumers
A common Lakehouse + Warehouse architecture

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:

  1. External storage
  2. OneLake Shortcut
  3. Lakehouse
  4. Spark / SQL / Power BI
OneLake shortcut in a Lakehouse

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:

  1. Source systems
  2. Lakehouse Bronze
  3. Lakehouse Silver
  4. Curated data
  5. Warehouse
  6. Power BI / SQL applications
Warehouse as a governed serving layer

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:

  1. Lakehouse Files
  2. Delta transformation
  3. WarehouseOnly if relational serving is needed
Land files in the Lakehouse, not the Warehouse

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.

Next step

Use the workload requirements to choose the architecture.

  • Spark/notebooks are primary → start with Lakehouse.
  • Full T-SQL write surface is primary → start with Warehouse.
  • Structured + unstructured data → use Lakehouse.
  • Dimensional SQL serving is the main goal → use Warehouse.
  • Need multi-table transactions → use Warehouse.
  • Need medallion ingestion/transformation → use Lakehouse.
  • Need both Spark engineering and SQL-first serving → use Lakehouse + Warehouse.
  • Unsure whether both are necessary → start with one and add the second only when a concrete requirement appears.
  • Data already exists elsewhere in OneLake/external storage → evaluate shortcuts or cross-item access before copying.

← All cheat sheets