The single most common question I get from teams standing up Microsoft Fabric is: lakehouse or warehouse? They have watched both demos, seen that both produce SQL, and noticed both write Delta into OneLake. So they assume the decision is cosmetic, pick one on a hunch, and then spend the next year fighting the wrong engine. The difference is real, and once you see it, table placement stops being a coin flip.
The actual difference, not the marketing one
Start from what they share, because that is what confuses everyone. Both the Fabric lakehouse and the Fabric warehouse store their tables as Delta Lake tables in OneLake. Same open format, same physical files, same single copy of your data. Neither one hides your bytes in a proprietary vault. That is the part the marketing slides emphasise, and it is genuinely true.
The difference is the engine sitting on top of those files and what that engine is allowed to do.
The lakehouse is Spark and files first.
A lakehouse is built for engineers who work in notebooks. You land raw files, you transform with Spark or Python, you read and write Delta tables directly, and you drop unstructured files next to structured ones in the same container. On top of that, Fabric gives every lakehouse a read-only SQL analytics endpoint. You can query the lakehouse tables with T-SQL, connect Power BI to it, and point tools at it, but you cannot run INSERT, UPDATE, DELETE, or MERGE through that endpoint. Writes happen through Spark. The SQL endpoint is a window, not a door.
The warehouse is a full T-SQL engine.
A warehouse is a complete relational engine that happens to store its data as Delta in OneLake. It supports the full write surface: INSERT, UPDATE, DELETE, MERGE, and, critically, multi-table transactions. You can update three tables inside one transaction and have all of them commit or all of them roll back. That guarantee is the thing the lakehouse SQL endpoint cannot give you, and it is the reason the warehouse exists as a separate object at all.
The one-line test
Do you write to this table with Spark or with T-SQL? Spark writes and file work → lakehouse. T-SQL writes, especially anything that must succeed or fail as a set across tables → warehouse. Everything else in this article is refinement on that single line.
For the canonical capability lists, keep the Fabric lakehouse docs and the Fabric warehouse docs open in a tab. Feature parity between them shifts with almost every release, so treat any capability claim, including mine, as something to reverify.
The real question: where each table should live
Teams frame this as "which product do we buy". That framing is wrong, because you get both in the same Fabric workspace at no extra licensing decision. The right question is per table: where does this table belong? The answer follows the data's job, not the team's habit.
Land raw and messy data in the lakehouse. Sensor readings, CMMS work-order exports, ERP extracts, JSON payloads, log files, anything semi-structured or arriving in bulk belongs where Spark can chew through it cheaply and where schema can flex. I go deeper on that side of the estate in my note on OneLake for operational and sensor data.
Put conformed, governed, heavily updated tables in the warehouse. Finance marts, slowly changing dimensions maintained with MERGE, compliance registers where a correction has to touch several tables atomically, and anything a T-SQL developer will maintain by hand. These are the tables where multi-table transactions and mature SQL tooling earn their keep.
The layer where teams hesitate is the middle: the cleaned, conformed intermediate tables that are not yet a finance mart but are past raw. My rule there is to ask who owns the table next. If a Spark pipeline keeps rebuilding it, leave it in the lakehouse until something downstream genuinely needs transactional writes. Promoting a table to the warehouse too early buys you nothing and adds a maintenance surface, because the warehouse only pays off once real T-SQL writes, row-level grants, or multi-table commits are in play. Absent those, a lakehouse table queried through the SQL endpoint serves Power BI identically and costs less to reshape when requirements move.
One more placement signal worth naming: the blast radius of a schema change. If reshaping a table forces you to notify three downstream teams and coordinate a release, that table has become a contract and belongs in the warehouse where the contract is explicit and versioned by hand. If you can rename a column at will because only your own notebook reads it, it is still an engineering table and belongs in the lakehouse.
Decision table: eight criteria that settle it
When a table could plausibly live in either store, I run it through these eight criteria. Most tables lean clearly one way once you are honest about the answers.
| Criterion | Favours Lakehouse | Favours Warehouse |
|---|---|---|
| Write pattern | Spark or Python jobs, file ingestion, bulk loads | T-SQL statements, hand-maintained records |
| Transaction needs | Single-table writes are fine | Multi-table commit or rollback as one unit |
| Team skills | Data engineers who live in notebooks | Analysts and developers fluent in T-SQL |
| Semi-structured data | JSON, nested, files, evolving schema | Clean, stable, relational shapes |
| Update / merge volume | Append-mostly, few in-place edits | Frequent UPDATE and MERGE churn |
| Tooling compatibility | Spark ecosystem, notebooks, ML libraries | SSMS, SQL agents, T-SQL stored logic |
| Security granularity | Object and workspace level is enough | Row, column, and object-level SQL grants |
| Cost of change | Reshaping raw and intermediate layers often | Stable contracts consumers depend on |
Read it as a tally, not a gate. A table that leans warehouse on transactions and security but lakehouse on everything else usually still belongs in the warehouse, because those two criteria are the ones the lakehouse endpoint cannot satisfy. When the tally is genuinely split, default to the lakehouse: it is cheaper to promote a table upward later than to unwind a warehouse dependency.
Two of the eight criteria are effectively vetoes rather than votes. If a table needs multi-table transactional integrity, or it needs row and column-level SQL security enforced by grants, the lakehouse SQL endpoint cannot deliver either today, so those requirements decide the placement on their own regardless of how the other six criteria fall. The remaining six are genuine trade-offs you weigh together. I call the vetoes out separately because I have seen teams average everything into a soft score and then land a compliance table in the lakehouse, only to discover months later that the security model they promised the auditors was never actually enforceable there.
The hybrid pattern for asset-heavy operators
If you run a facilities, utilities, or asset-intensive operation, you will not pick one store. You will use both, and the split maps cleanly onto how the data flows. This is the pattern I keep landing on.
- Land and transform in the lakehouse. Bring CMMS exports, ERP extracts, sensor feeds, and meter data into lakehouse tables. Do the heavy Spark work here: cleaning, deduplication, joining sensor streams to asset registers, building the medallion bronze and silver layers.
- Serve conformed marts from the warehouse. Promote the finance, compliance, and reporting gold tables into a warehouse, where T-SQL developers maintain dimensions with
MERGE, apply row-level security, and get multi-table transactional guarantees for anything auditors will inspect. - Point Power BI at the right layer. Operational and engineering reports read the lakehouse endpoint; governed finance and compliance reports read the warehouse.
The shortcut that avoids copying data
A warehouse can read lakehouse tables without importing them. Because both live in OneLake, you either add the lakehouse as a source in the warehouse editor and query its tables across the boundary, or you use a shortcut so the lakehouse Delta table appears inside the warehouse. Either way there is one physical copy. You do not run a nightly job to duplicate the silver layer into the warehouse just so a T-SQL query can reach it. Copy only when you deliberately want a separate, governed, point-in-time table, not as a reflex.
Getting this split right is really a phasing question, and it is one of the first architecture decisions I flag in my Microsoft Fabric adoption roadmap. Decide the lakehouse-to-warehouse boundary before you build the first pipeline, not after three teams have wired reports to whichever store they happened to open first.
Two traps that catch real teams
These are not hypothetical. I have watched both cost weeks.
Trap one: the SQL endpoint metadata sync lag.
When Spark writes a new table or new columns into the lakehouse, the read-only SQL analytics endpoint does not always see the change instantly. There is a metadata sync that can lag, and it surprises people mid-refresh: the Spark notebook finished, the data is in OneLake, but a Power BI refresh or a downstream T-SQL query against the endpoint returns stale schema or misses the freshest rows. Nobody did anything wrong, and yet the numbers look incomplete. If you have orchestrated pipelines where a Spark step feeds a SQL-endpoint step, build in an explicit metadata refresh or a wait, and do not assume "the write finished" means "the endpoint sees it". Check the current sync behaviour in the docs each quarter, because Microsoft keeps tightening this.
Trap two: choosing the warehouse because someone once used SSMS
The weakest reason I hear for putting everything in the warehouse: "our report writer is comfortable in SSMS". That is a tooling preference, not a data requirement, and the lakehouse SQL endpoint answers T-SQL queries in SSMS too. Familiarity with a client tool is not a reason to force append-only sensor data through a transactional engine it does not need. Decide on the data's job first, then let people connect with whatever client they like.
Table-placement worksheet for a CMMS plus ERP estate
To make this concrete, here is how the common tables in a maintenance-plus-finance data estate usually land. Yours will vary, but this is a defensible starting point.
| Table / data set | Store | Why |
|---|---|---|
| Raw CMMS work-order exports | Lakehouse | Bulk file loads, schema drift, Spark cleaning |
| Sensor / meter readings | Lakehouse | High-volume append, semi-structured, cheap Spark scan |
| Asset register (raw / silver) | Lakehouse | Joined and enriched in notebooks |
| Conformed asset dimension | Warehouse | Maintained with MERGE, consumed as a stable contract |
| ERP GL / cost postings | Warehouse | Multi-table transactions, T-SQL maintenance |
| Finance reporting marts | Warehouse | Row-level security, audited, SSMS-friendly |
| Compliance / permit register | Warehouse | Corrections must commit atomically across tables |
| Maintenance KPI aggregates | Either | Lakehouse if Spark-built, warehouse if T-SQL-built |
| ML feature tables | Lakehouse | Consumed by Spark and Python ML libraries |
Notice the shape: everything raw, high-volume, or engineered in notebooks lives in the lakehouse; everything conformed, governed, or transactionally sensitive lives in the warehouse. The KPI row is deliberately "either" to make the point that the build tool, not the topic, decides. Deciding where a table lives is the same muscle as deciding which tool moves it, which is why I treat store selection and pipeline design together in choosing an ETL tool for a one-person data team.
Independence and freshness note
I am not affiliated with Microsoft and receive nothing for recommending Fabric. This reflects hands-on delivery, not a partnership. Fabric licensing, SKUs, and features change fast: the lakehouse SQL endpoint's capabilities and the warehouse's feature parity shift with almost every release. This guidance is verified as of 1 August 2026. Recheck the linked Microsoft docs quarterly before committing an architecture.
Conclusion
Lakehouse and warehouse are not competing products you choose between once. They are two engines over the same OneLake Delta tables, and the honest answer is almost always "use both, per table". Land and transform in the lakehouse where Spark and flexible schema are cheap. Serve conformed finance and compliance marts from the warehouse where multi-table transactions and mature SQL security matter. Use shortcuts so the warehouse reads lakehouse tables without a second copy. Decide by the data's job, never by which client tool someone opened first, and revisit the boundary each quarter as Fabric keeps moving.
Written by Muhammad Abbas
CMMS / CAFM Manager & Enterprise Integration Specialist · 22+ years across ERP, EAM, CAFM and enterprise integration.
Work with me