Microsoft Fabric Data Warehouse vs Lakehouse: When to Use Which

Six months after the item is chosen, the consequence arrives: "the reconciliation job needs to update three tables together and roll back if the third fails, and we cannot do that here." Or: "our two SQL developers can read the data but every change goes through the one person who knows PySpark, and he is on leave."

It is not performance and it is not price — both items bill against the same Fabric capacity units. It is who is allowed to write to the data, and with what tool. Get that wrong and you have not bought a slow platform; you have bought a bottleneck with a name and a holiday allowance. This is about the two item types inside Fabric, verified against Microsoft Learn.

A Fabric Lakehouse stores files and Delta tables, is written to with Apache Spark, pipelines, dataflows and shortcuts, and is read over T-SQL through a read-only SQL analytics endpoint. A Fabric Warehouse is written to and read with full T-SQL — including INSERT, UPDATE, DELETE and MERGE — and supports multi-table transactions. Both store Delta Parquet in OneLake. Microsoft's own decision guide reduces the choice to three questions: whether your developers work in Spark or T-SQL, whether you need multi-table transactions, and whether your data is structured only or mixed.

The Lakehouse SQL analytics endpoint is read-only — you cannot insert, update or delete through it; to modify data you switch to the lakehouse and use Spark. The Fabric Warehouse supports full T-SQL DML. The asymmetry runs both ways: Spark can read Warehouse tables but cannot write to them. Into a Lakehouse, you can point SSMS at the endpoint, SELECT, create views and stored procedures, and apply RLS — but every write goes through Spark, a pipeline, a dataflow or a shortcut. If your data team is four SQL developers and one part-time Spark engineer, you have just routed every data change through the part-time engineer.

Into a Warehouse, Fabric publishes Delta logs so any Delta-capable engine can read its user tables — but that access is read-only to preserve ACID compliance, and all inserts, updates and deletes must go through the Warehouse. So each item is writable by exactly one engine family and readable by both; neither is a superset of the other. The transaction difference is the second half: the Warehouse supports BEGIN TRAN / COMMIT / ROLLBACK across multiple tables; a Lakehouse has ACID at the Delta table level but no equivalent of "these three tables commit together or none of them do". If your month-end posts adjustments across a ledger, an allocation and an audit table, you need a Warehouse.

In mid-market estates the skills question decides this more often than the workload does. In the estates I have worked in — manufacturing, FMCG, logistics, EPC — the analytics capability is typically two to five people. One or two write SQL fluently because they have maintained ERP reports for a decade; perhaps one has used Python; nobody has run a Spark cluster in production. For that team a Warehouse is not the technically superior choice; it is the choice where the second-best person can fix Tuesday's broken load. Bus factor is an architecture property.

The inverse holds too. If you already have Spark capability — because you are ingesting OPC-UA or MQTT telemetry, processing inspection images, or running feature engineering for a forecast — the Lakehouse is where that work belongs, and forcing it through T-SQL produces worse code and slower delivery. The medallion pattern assumes Bronze and Silver are Lakehouse territory for exactly this reason. Be honest about which team you have today, not the one in the hiring plan.

Both run on Fabric capacity and bill in capacity units, so this is a fit comparison, not a cost one. The Lakehouse has a Files area alongside Tables, so PDFs, images, JSON payloads and raw CSV drops have somewhere legitimate to live. The Warehouse is tables only, and its persisted data type list is narrower than SQL Server's: as of mid-2026 it does not support nvarchar (use varchar), datetime (use datetime2), datetimeoffset, money, xml, a native json type, geography/geometry, tinyint or CLR types, and varchar(max) caps at 16 MB.

Those are not exotic edge cases — a logistics estate with GPS traces, or an EPC estate with timezone-stamped site records, will meet geography and datetimeoffset in the first fortnight. On the T-SQL surface, MERGE and #temp tables are supported, while materialised views, triggers, recursive queries and synonyms are not. Teams migrating stored procedures from SQL Server should budget for that list rather than discovering it in UAT.

A Fabric Lakehouse and Warehouse can be queried together. Cross-database queries using three-part names work across warehouses and lakehouse SQL analytics endpoints in the same active workspace. Shortcuts reference data in place without copying, and both persist tables as Delta Parquet in OneLake, so a table built in one is readable from the other.

Microsoft's own guidance names the hybrid explicitly: among the Lakehouse's recommended use cases is pairing with Warehouse for enterprise analytics. The common shape is Lakehouse for Bronze and Silver — ingestion, telemetry, files, Spark transformation — and Warehouse for Gold, where T-SQL developers own the dimensional model, the multi-table posting logic and the security predicates. That hybrid is the answer more often than either item alone. It also costs you a second item to govern, so do not adopt it reflexively.

Direct Lake on OneLake reads Delta tables from any Fabric source and does not fall back to DirectQuery. Direct Lake on SQL reads lakehouse or warehouse tables and views through the SQL analytics endpoint, and falls back to DirectQuery when it cannot load directly from a Delta table — for example when the source is a SQL view, or when the warehouse uses SQL-based granular access control.

Read that second clause carefully. If you implement row-level security in the Warehouse using T-SQL predicates and then serve the model through Direct Lake on SQL, you have chosen DirectQuery behaviour for those tables. That is a legitimate design; it is not a legitimate surprise. Either way, Direct Lake guardrails are per-SKU: F2–F8 permit 300 million rows and a 10 GB model; F64 raises that to 1,500 million rows and unlimited model size. Exceed the guardrail and behaviour changes.

Fabric Warehouse governs access with T-SQL — row-level security, column-level security, dynamic data masking and object-level security. OneLake security roles, which enforce table, row and column rules across Fabric engines at the data plane, currently support Lakehouse and mirrored databases — Warehouse is not on that list.

If your requirement is "the Saudi entity's team sees Saudi rows in Power BI, in a notebook and in SQL, enforced once", OneLake security applied to a Lakehouse is the mechanism designed for it — noting that items with a SQL endpoint start in Delegated identity mode and must be switched to User's identity mode. If your requirement is "mask the last six digits of the bank account for anyone outside treasury, in T-SQL, the way our auditors already understand", the Warehouse's dynamic data masking and column-level security are the mechanism.

Some choices here are one-way doors. Writing the Gold layer into a Lakehouse when the owners are SQL developers — reversing means rebuilding every transformation as T-SQL and repointing every model, three to eight weeks that produce no new capability. Implementing T-SQL RLS on the Warehouse then wanting cross-engine enforcement — predicates do not port to OneLake security roles, and in a regulated estate the re-certification is longer than the rebuild. Splitting one domain across both items to avoid choosing — cross-database joins work, but a fact in the Warehouse joined to a dimension in the Lakehouse doubles the lineage surface. Split by layer, never by table.

And what neither choice fixes: your source data — a duplicated customer master commits perfectly into both. Metadata sync is not instantaneous — the SQL endpoint discovers Lakehouse Delta changes through a background process, so tables appearing late after a Spark write is expected, not a fault. Cross-region does not work — connections require source and target in the same region, which multi-entity groups with a UAE workspace and an India workspace meet immediately. And neither item makes anyone own the tables — the recurring cause of a stalled Fabric estate is not item choice, it is that no named person owns the Gold model after go-live.

Answer these five this week, before anyone creates an item:

• Name every person who will need to change data in the Gold layer in the next twelve months, and the language each writes fluently
• List any process that must update two or more tables atomically. If there is one, you need a Warehouse somewhere
• List the data types your sources actually emit — timezone-aware timestamps, GUIDs, geospatial points, JSON payloads
• State the access rule hardest to defend to an auditor, and decide whether it must hold in Spark as well as SQL
• Decide who owns the Gold tables by name after go-live. Not the platform. The tables

If questions one and five name the same two people, and they write SQL, the decision has already been made for you. We build these estates on Microsoft Fabric, OneLake and Power BI — first value in six weeks, on the item type your team can actually maintain.