Microsoft Fabric decision guide: Choose between Warehouse and Lakehouse

Microsoft Fabric offers two enterprise-scale, open standard format workloads for data storage: Warehouse and Lakehouse. This article compares the two platforms and the decision points for each.

Criterion

No Code or Pro Code solutions: How do you want to develop?​

• Spark
  • Use Lakehouse​
• T-SQL​
  • Use Warehouse​

Warehousing needs​: Do you need multi-table transactions?​

• Yes
  • Use Warehouse​
• No​
  • Use Lakehouse​

Data complexity​: What type of data are you analyzing?​

• Don't know​
  • Use Lakehouse​
• Unstructured and structured​ data
  • Use Lakehouse​
• Structured​ data only
  • Use Warehouse​

Choose a candidate service

Perform a detailed evaluation of the service to confirm that it meets your needs.

The Warehouse item in Fabric Data Warehouse is an enterprise scale data warehouse with open standard format.​

• No knobs performance with minimal set-up and deployment, no configuration of compute or storage needed. ​
• Simple and intuitive warehouse experiences for both beginner and experienced data professionals (no/pro code)​.
• Lake-centric warehouse stores data in OneLake in open Delta format with easy data recovery and management​.
• Fully integrated with all Fabric workloads.
• Data loading and transforms at scale, with full multi-table transactional guarantees provided by the SQL engine.​
• Virtual warehouses with cross-database querying and a fully integrated semantic layer​.
• Enterprise-ready platform with end-to-end performance and usage visibility, with built-in governance and security​.
• Flexibility to build data warehouse or data mesh based on organizational needs and choice of no-code, low-code, or T-SQL for transformations​.

The Lakehouse item in Fabric Data Engineering is a data architecture platform for storing, managing, and analyzing structured and unstructured data in a single location.

• Store, manage, and analyze structured and unstructured data in a single location to gain insights and make decisions faster and efficiently.​
• Flexible and scalable solution that allows organizations to handle large volumes of data of all types and sizes.​
• Easily ingest data from many different sources, which are converted into a unified Delta format ​
• Automatic table discovery and registration for a fully managed file-to-table experience for data engineers and data scientists. ​
• Automatic SQL analytics endpoint and default dataset that allows T-SQL querying of delta tables in the lake

Both are included in Power BI Premium or Fabric capacities​.

Compare different warehousing capabilities

This table compares the Warehouse to the SQL analytics endpoint of the Lakehouse.

Microsoft Fabric offering

Warehouse

SQL analytics endpoint of the Lakehouse

---

Primary capabilities

ACID compliant, full data warehousing with transactions support in T-SQL.

Read only, system generated SQL analytics endpoint for Lakehouse for T-SQL querying and serving. Supports analytics on the Lakehouse Delta tables, and the Delta Lake folders referenced via shortcuts.

---

Developer profile

SQL Developers or citizen developers

Data Engineers or SQL Developers

---

Data loading

SQL, pipelines, dataflows

Spark, pipelines, dataflows, shortcuts

---

Delta table support

Reads and writes Delta tables

Reads delta tables

---

Storage layer

Open Data Format - Delta

Open Data Format - Delta

---

Recommended use case

• Data Warehousing for enterprise use
• Data Warehousing supporting departmental, business unit or self service use
• Structured data analysis in T-SQL with tables, views, procedures and functions and Advanced SQL support for BI

• Exploring and querying delta tables from the lakehouse
• Staging Data and Archival Zone for analysis
• Medallion lakehouse architecture with zones for bronze, silver and gold analysis
• Pairing with Warehouse for enterprise analytics use cases

---

Development experience

• Warehouse Editor with full support for T-SQL data ingestion, modeling, development, and querying UI experiences for data ingestion, modeling, and querying
• Read / Write support for 1st and 3rd party tooling

• Lakehouse SQL analytics endpoint with limited T-SQL support for views, table valued functions, and SQL Queries
• UI experiences for modeling and querying
• Limited T-SQL support for 1st and 3rd party tooling

---

T-SQL capabilities

Full DQL, DML, and DDL T-SQL support, full transaction support

Full DQL, No DML, limited DDL T-SQL Support such as SQL Views and TVFs

---

Related content

• Microsoft Fabric decision guide: choose a data store