Lakehouse vs warehouse: Fabric and Databricks
A lakehouse keeps files and Delta tables in open storage and is built mainly with Spark; a warehouse is a relational store built mainly with T-SQL. In Microsoft Fabric both keep their tables as Delta in OneLake and share the same SQL engine, so the choice is about how the team works, not where the data sits. The decision rule: Spark or unstructured data means lakehouse, T-SQL with multi-table transactions means warehouse, and when the scenario is on Databricks, the answer is almost always the Databricks lakehouse.
Three questions, in this order
Microsoft’s Fabric decision guide starts with three questions. This guide keeps them as the framework, because exam scenarios are written to answer exactly these.
1. How will the team develop? Apache Spark (Python, Scala, Spark SQL or R) points to a lakehouse. T-SQL points to a warehouse.
2. Are multi-table transactions required? Yes means warehouse. A warehouse offers full ACID transactions across tables, plus views, functions and stored procedures. A lakehouse has Delta ACID per table, but its SQL endpoint cannot modify data.
3. What kind of data? Structured and unstructured, or unsure, means lakehouse. Structured only can be a warehouse.
If the answers disagree (Spark team, but T-SQL multi-table updates needed), the transaction requirement or a stated skill set in the scenario usually breaks the tie. Treat the three answers as a starting point, as Microsoft does, then check the details below.
The Fabric comparison that matters most
The lakehouse comes with an automatically created SQL analytics endpoint. It is the part exams compare against the warehouse.
| Warehouse | Lakehouse SQL analytics endpoint | |
|---|---|---|
| Primary job | Full relational warehousing | Read-only T-SQL over lakehouse Delta tables |
| T-SQL support | Full queries, DML and DDL, with transactions | Full queries, no DML, limited DDL such as views and table-valued functions |
| Loading data | COPY INTO, INSERT, CREATE TABLE AS SELECT, pipelines, dataflows | Spark, pipelines, dataflows, shortcuts |
| Developer | SQL developers, citizen developers | Data engineers, SQL developers |
| Storage | Open Delta in OneLake | Open Delta in OneLake |
Two features reduce the gap. Shortcuts let a lakehouse expose data from ADLS Gen2, Amazon S3 or another lakehouse in place, with no copy. Cross-database queries let a warehouse read lakehouse tables with no data duplication. Because everything is Delta in OneLake, a Spark notebook can read a warehouse table and T-SQL can read a lakehouse table.
Where Databricks fits
On Databricks the lakehouse is the platform: Delta Lake provides ACID transactions and schema enforcement, and Unity Catalog provides governance, according to the Databricks lakehouse documentation. Databricks contrasts it with a classic warehouse (good for BI on clean structured data, often in proprietary formats that limit machine learning) and a data lake (cheap storage of any format, but unvalidated). The lakehouse aims to give open formats, support for ML and low-latency BI together.
The word “warehouse” on Databricks usually means a SQL warehouse, which is a compute resource for running SQL, not a separate store. This is a frequent confusion: starting or stopping a SQL warehouse does not move data. The Data Analyst Associate outline asks for the role a SQL warehouse plays in query execution, so expect this distinction there. For the table format underneath, see Delta Lake explained and for governance Unity Catalog explained.
A worked example
A manufacturer receives JSON sensor files, CSV exports from an ERP system and a nightly SQL extract. A data engineering team uses PySpark. Finance analysts want governed star-schema tables they can reshape with T-SQL, including a nightly job that updates a dimension and a fact table in one transaction.
A defensible Fabric design uses both. Land and clean the mixed files in a lakehouse with Spark, because the data is unstructured in part and the team writes Spark. Build the finance star schema in a warehouse, because the nightly job needs a multi-table transaction that the lakehouse SQL endpoint cannot do. Query across them with cross-database queries and keep one copy of the data. Microsoft’s medallion guidance describes the same split: bronze and silver as lakehouses, gold optionally as a warehouse; see medallion architecture.
Which exams test this
| Exam | How the topic appears | On this site |
|---|---|---|
| DP-600 | Prepare data (45–50%): choose between different data stores, and implement a star schema for a lakehouse or a warehouse | Prepare data explained |
| DP-700 | Ingest and transform data (30–35%): choose an appropriate data store, then choose between Dataflows Gen2, notebooks, KQL and T-SQL to transform the data | Ingest and transform explained |
| Databricks Data Analyst Associate | Minor topic: describe the core platform components (Section 1, 11%) and explain the role a SQL warehouse plays in query execution (Section 4, 20%) | Platform and data explained |
How exam questions are built around it
The constraint words carry the answer. “Notebooks”, “PySpark”, “files” or “unstructured” mean lakehouse. “Stored procedures”, “T-SQL UPDATE across tables” or “rollback” mean warehouse. “Streaming events queried by time window” means an eventhouse, a third Fabric store that is neither.
Traps to watch for:
- Writing through the SQL analytics endpoint. It is read-only for data. A question asking you to
UPDATEorINSERTwith T-SQL on a lakehouse needs a warehouse, or a Spark write. - “Warehouse means a separate copy”. In Fabric it does not; both are Delta in OneLake.
- SQL warehouse is not a data warehouse in Databricks questions.
- Copying when a shortcut works. If the requirement says “without copying” or “no refresh job”, the answer is a shortcut, not a pipeline.
Practice questions
Question 1. A Fabric team needs a nightly T-SQL process that updates a dimension table and a fact table together and rolls both back if either step fails. Data is fully structured. Which item should store the tables?
- A. A lakehouse, using its SQL analytics endpoint
- B. An eventhouse
- C. A shortcut to external storage
- D. A warehouse
Show answer
Answer: D
Multi-table ACID transactions in T-SQL are a Fabric warehouse capability. The SQL analytics endpoint of a lakehouse is read-only for data, so it cannot run the updates, and an eventhouse is built for streaming and time-based queries. A shortcut only exposes existing data and does not store or modify it.
Question 2. A data engineering team works in PySpark notebooks on a mix of JSON files, images and CSV exports in Fabric. Analysts will later query the cleaned tables with read-only T-SQL. What should you create?
- A. A lakehouse
- B. A warehouse
- C. A Databricks SQL warehouse
- D. A semantic model
Show answer
Answer: A
A lakehouse suits Spark development and unstructured data, and it automatically provides a read-only SQL analytics endpoint for the analysts. A warehouse is built for T-SQL development and structured data, and cannot be the Spark-first store for images and files. A SQL warehouse is a Databricks compute resource, not a Fabric item, and a semantic model is a reporting layer, not storage.