Microsoft Fabric decision guide: Choose between Warehouse and Lakehouse

  • Article

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

Diagram that contains decision trees for Lakehouse and Warehouse in Microsoft Fabric.

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 Synapse 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 Synapse Data Engineering

  • 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