All Fabric data stores keep their data in OneLake, so the choice is about the engine, the skills of the team, the shape of the data and the transaction needs, not about where the bytes live. The decision guide on Learn reduces to a handful of questions. Figure 1.2 turns those questions into a decision tree.
The candidates
- Lakehouse (Data Engineering): Delta tables in a Tables folder plus any files in a Files folder, developed mainly with Spark (PySpark, Scala, Spark SQL, R). Every lakehouse gets an automatic, read-only SQL analytics endpoint for T-SQL queries over its Delta tables.
- Warehouse (Data Warehouse): a relational warehouse developed with T-SQL, with full DQL, DML and DDL, multi-table ACID transactions, views, functions and stored procedures. Data is stored as Delta in OneLake, so Spark users can read it too.
- Eventhouse (Real-Time Intelligence): a container for one or more KQL databases, built for streaming, time-based events with structured, semi-structured (JSON, XML) and free-text data, queried mainly with KQL (with some T-SQL support). OneLake availability for KQL data is opt-in, unlike other stores where Delta in OneLake is the default.
- SQL database in Fabric (Databases): an operational (OLTP) database on the same SQL Database Engine as Azure SQL Database, with automatic index tuning. Its data is replicated near real time into OneLake, and it gets its own SQL analytics endpoint.
- Cosmos DB in Fabric: the NoSQL option, accessed through APIs in languages such as Python, JavaScript and C#.
- Mirrored database: a near real-time replica in OneLake of an external source such as Azure SQL Database, Azure SQL Managed Instance, Azure Cosmos DB, Azure Database for PostgreSQL, SQL Server, Oracle, Snowflake, Google BigQuery or SAP, or an open mirroring landing zone that any application can write change data to. It’s read-only for analysis, through a SQL analytics endpoint.
- Semantic model in Import mode: not a general data store but a compressed, in-memory analytical copy loaded by refresh. It can combine any number of source types, and it’s only as current as its last refresh.
Lakehouse or warehouse?
Learn’s decision tree for these two stores uses three questions:
| Question | Lakehouse | Warehouse |
|---|---|---|
| How do you want to develop? | Apache Spark (Python, Scala, Spark SQL, R) | T-SQL |
| Do you need multi-table transactions? | No | Yes |
| What data are you analysing? | Unstructured and structured, or unsure | Structured only |
The lakehouse’s SQL analytics endpoint and a warehouse look alike in the SQL editor, which is where many design mistakes start:
| Capability | Warehouse | Lakehouse SQL analytics endpoint |
|---|---|---|
| Write data with T-SQL | Yes: INSERT, UPDATE, DELETE, MERGE, COPY INTO, CTAS |
No; read-only |
| T-SQL surface | Full DQL, DML and DDL with transactions | Full DQL, no DML, limited DDL (views, table-valued functions) |
| Delta tables | Reads and writes | Reads only (Delta tables only; Parquet or CSV files aren’t shown) |
| Loading | T-SQL, pipelines, dataflows | Spark, pipelines, dataflows, shortcuts |
| Typical use | Enterprise or departmental warehouse, SQL-first BI | Exploring lakehouse tables, medallion layers, staging |
A warehouse supports explicit transactions that span several tables, and even several warehouses in the same workspace:
BEGIN TRAN;
INSERT INTO dbo.OrderHeader (OrderId, CustomerId) VALUES (1001, 42);
INSERT INTO dbo.OrderLine (OrderId, LineNumber, Qty) VALUES (1001, 1, 5);
COMMIT TRAN;Both inserts commit together or not at all. Fabric Data Warehouse always uses snapshot isolation (an attempt to change the isolation level is ignored) and table-level locking, so two transactions updating different rows of the same table can still conflict. Distributed transactions, save points, named transactions and marked transactions aren’t supported.
Any store with a SQL analytics endpoint can join data across items in the same workspace with three-part names:
SELECT c.CustomerName, SUM(s.Amount) AS TotalSales
FROM SalesWarehouse.dbo.FactSales AS s
JOIN CrmLakehouse.dbo.Customer AS c
ON c.CustomerId = s.CustomerId
GROUP BY c.CustomerName;The first part of each name is the warehouse, lakehouse, mirrored database or SQL database, so no data is copied to combine them.
Full comparison
| Store | Primary engine and language | Data shape | Write path | Multi-table transactions | Best for |
|---|---|---|---|---|---|
| Lakehouse | Spark (PySpark, Scala, Spark SQL, R); T-SQL read-only | Structured, semi-structured, unstructured | Spark, pipelines, dataflows, shortcuts | No | Data engineering, data science, medallion architecture |
| Warehouse | T-SQL | Structured | T-SQL (COPY INTO, INSERT, CTAS), pipelines, dataflows |
Yes | Enterprise data warehousing, dimensional models, SQL-first BI |
| Eventhouse / KQL database | KQL (T-SQL also available) | Streaming events, time series, JSON, free text | Eventstreams, SDKs, Kafka, Logstash, dataflows | Not applicable | Telemetry, logs, IoT, high-granularity interactive analytics |
| SQL database in Fabric | T-SQL | Relational, normalised | Applications and T-SQL; replicated to OneLake | Yes (full transactional tables) | OLTP apps, operational data store, reverse ETL, vector-based AI apps |
| Cosmos DB in Fabric | NoSQL via APIs | Documents | Application APIs | - | NoSQL apps, AI with vector data |
| Mirrored database | Source system; T-SQL read in Fabric | As the source | Replication from the source (or open mirroring writers) | Not applicable in Fabric | Analysing an external operational database without building pipelines |
| Semantic model (Import) | DAX over VertiPaq | Star schema tables | Refresh from sources | Not applicable | Fast BI queries over curated data |
Decision guide
| If the requirement says… | Choose |
|---|---|
| Team writes PySpark, data includes images, JSON and CSV files | Lakehouse |
Team writes T-SQL and needs UPDATE, DELETE and MERGE in SQL |
Warehouse |
| Several tables must be updated together, all or nothing | Warehouse (or SQL database for OLTP) |
| Analysts only read with T-SQL while engineers load with Spark | Lakehouse (analysts use the SQL analytics endpoint) |
| Billions of streaming events, time-series and free-text search, near real-time dashboards | Eventhouse (KQL database) |
| Application back end with high concurrency, enforced foreign keys and automatic index tuning | SQL database in Fabric |
| Analyse an existing Azure SQL or Snowflake database without ETL | Mirrored database |
| Report consumers need the fastest interactive queries over data refreshed a few times a day | Semantic model in Import mode (or Direct Lake on top of a lakehouse or warehouse; see Chapter 13, “Designing semantic models: storage modes, relationships and composite models”) |
Mirroring is also cheap to run: replication compute is free and doesn’t consume capacity, and each purchased CU includes 1 TB of free mirroring storage (so an F64 includes 64 TB). It does need a running capacity; when the capacity is paused, nothing replicates.
Common trap: Choosing a lakehouse and planning to fix bad rows with
UPDATEthrough its SQL analytics endpoint - the endpoint is read-only; change lakehouse data through Spark, or use a warehouse if the team must write with T-SQL.
Common trap: Picking a warehouse for raw JSON logs and free-text search because “it’s the analytics store” - semi-structured, time-based event data is what an eventhouse is built for.