Fabric Lakehouse vs Warehouse: Which to Use When
Pick the Warehouse if the people who load and transform your data write T-SQL and you need multi-table transactions. Pick the Lakehouse if they write Spark (PySpark, Spark SQL, Scala, R), or if you have files, semi-structured, or unstructured data to land. If you are not sure, start with a Lakehouse: you can query it with T-SQL anyway, and you can add a Warehouse later without moving data.
Neither item carries a license fee of its own, and both can sit in the same workspace, so the choice is reversible. What costs money is rebuilding a year of transforms in a different language after go-live, which is why the write side decides it.
The one question that settles it
Both items store data as Delta Parquet tables in OneLake. Both serve T-SQL through the same SQL engine. Both feed Power BI, including Direct Lake. So the storage and the reporting side are close to identical.
The difference is on the write side. Who puts data in, and with what language.
| You write data with | Use |
|---|---|
| T-SQL (INSERT, UPDATE, MERGE, stored procedures, CTAS) | Warehouse |
| Spark notebooks or Spark job definitions | Lakehouse |
| Pipelines or Dataflows Gen2 only, no code | Either. Pick by who will maintain it later |
The Lakehouse has a SQL analytics endpoint, but it is read-only. You can SELECT, create views, table-valued functions, and stored procedures, and you can grant permissions. You cannot INSERT, UPDATE, DELETE, or MERGE through it. Microsoft's docs put it plainly: "To modify data, switch to the lakehouse and use Apache Spark." If your team's whole toolkit is T-SQL and they expect to write data with it, a Lakehouse will frustrate them within a day.
Microsoft's own decision guide comes down to three questions:
| Question | Answer | Use |
|---|---|---|
| How do you want to develop? | Spark | Lakehouse |
| T-SQL | Warehouse | |
| Do you need multi-table transactions? | Yes | Warehouse |
| No | Lakehouse | |
| What type of data? | Structured only | Warehouse |
| Mixed, unstructured, or unknown | Lakehouse |
Side-by-side comparison
Checked against Microsoft Learn as of August 2026.
| Lakehouse | Warehouse | |
|---|---|---|
| Primary language | Spark: PySpark, Spark SQL, Scala, R | T-SQL |
| Storage format | Delta Parquet in OneLake | Delta Parquet in OneLake |
| Data types | Structured, semi-structured, unstructured (Tables and Files areas) | Structured |
| Write with T-SQL | No (SQL analytics endpoint is read-only) | Yes: full DDL and DML |
| Write with Spark | Yes, native | Yes, via the Spark connector (append, overwrite, ignore, errorIfExists; write needs Runtime 2.0) |
| Multi-table ACID transactions | No | Yes |
| Loading options | Notebooks, Spark jobs, pipelines, Dataflows Gen2, shortcuts | COPY INTO, INSERT, CTAS, pipelines, Dataflows Gen2 |
| Shortcuts to external data (ADLS, S3, other workspaces) | Yes, live reference without copying | Can query Lakehouse shortcuts through cross-database queries |
| Views, functions, stored procedures | Yes, on the SQL analytics endpoint (read-only logic) | Yes |
| Row-level, column-level, object-level security, dynamic data masking | Yes, on the SQL analytics endpoint. Applies only when data is read through that endpoint, not through Spark | Yes |
| Cross-database T-SQL queries | Yes, three-part names | Yes, three-part names |
| Power BI / Direct Lake | Yes | Yes |
| Typical owner | Data engineer, data scientist | SQL developer, BI developer, analyst who knows SQL |
| Microsoft's stated best fit | Data engineering, data science, medallion architecture, large mixed-format data | Star and snowflake schemas, curated data marts, governed semantic models for BI |
Neither item has a price of its own. Both consume capacity units from your Fabric capacity and store data in OneLake. See the Microsoft Fabric pricing breakdown for how capacity billing works.
When the Warehouse is the right call
Your transforms are written as stored procedures. A nightly load that truncates staging, merges into dimensions, then inserts facts, all inside one transaction that rolls back if any step fails. That is multi-table ACID, and only the Warehouse does it.
You are migrating from SQL Server, Synapse dedicated pools, or another relational warehouse. Most existing T-SQL runs unchanged. You can keep SSMS and the rest of your SQL tooling. The Synapse to Fabric migration guide covers the details.
The team that will own it after go-live knows SQL and nothing else. This is the most common reason in mid-sized companies. A finance or operations analyst can maintain a Warehouse. Handing them a notebook is not fair to them.
You need row-level security that holds for every reader. Both items support object-level, column-level, and row-level security plus dynamic data masking. The difference is reach: on a Lakehouse those rules apply only to readers coming through the SQL analytics endpoint, so a user with workspace access and a notebook reads around them. On a Warehouse, SQL is the only path in. If sensitive data must be locked down for everyone, the Warehouse is simpler to reason about.
When the Lakehouse is the right call
Your source data is not tables. JSON from an API, CSV drops from a supplier, logs, images, PDFs. The Lakehouse has a Files area for raw landings and a Tables area for Delta tables. The Warehouse has no place to put a raw file.
Your engineers write Python. PySpark notebooks are the native tool. Data science, ML feature engineering, and anything that needs a Python library live here.
You want shortcuts. A Lakehouse can point at data in ADLS Gen2, Amazon S3, or another Fabric workspace without copying it. The data shows up as a table and is queryable through Spark and T-SQL.
You are building bronze, silver, gold. Microsoft lists medallion architecture as a recommended Lakehouse use case. Raw files land in bronze, Spark cleans into silver, business-ready tables sit in gold. The medallion architecture in Fabric post walks through the layout.
You have not decided yet. The decision guide says if you do not know your data complexity, use a Lakehouse. It is the more flexible starting point, and the SQL analytics endpoint means SQL users are not locked out.
The pattern most teams land on: both
The two items are built to be used together, and Microsoft says so directly: "land and transform data in a lakehouse with Spark, then expose curated datasets to a warehouse for SQL-based reporting."
A typical layout:
- Lakehouse for bronze and silver. Raw files and shortcuts land here. Spark notebooks or pipelines clean, deduplicate, and conform the data.
- Warehouse for gold. T-SQL stored procedures read the silver tables through cross-database queries and build star-schema dimensions and facts with transactional guarantees. The Spark connector can also write here from a notebook if that suits the team better.
- Power BI semantic model on the Warehouse. Direct Lake or Import, with row-level security applied in the Warehouse or in the model.
If your team is Spark-only, skip step 2 and put gold in a second Lakehouse. If your team is SQL-only and your sources are already tabular, skip step 1 and load the Warehouse straight from pipelines. The point is that you do not have to choose once, for everything. Choose per layer, based on who maintains that layer.
For how these items fit alongside Eventhouse, SQL database, and the rest, see the Fabric architecture walkthrough.
What if you pick wrong and want to switch?
Moving the data is cheap. Moving the logic is not.
Both items keep their tables as Delta in OneLake, and both read each other with three-part names, so you rarely need to copy anything at all. If you do want a physical copy, a Warehouse pulls a Lakehouse table in with CREATE TABLE AS SELECT or INSERT ... SELECT, and a notebook pulls a Warehouse table out with the Spark connector and writes it back as a Delta table in a Lakehouse. Either way it is one statement.
The cost is the transformation code. Stored procedures do not become PySpark, and notebooks do not become T-SQL. If you have six months of nightly loads written in one language and you switch items, you rewrite them. That is the real reason to answer the language question honestly on day one, and the reason a Lakehouse is the safer start when you are unsure: SQL users can still read it, so nothing is rewritten if you later add a Warehouse for the gold layer.
Traps that catch people
Tables outside the Tables folder do not appear in the SQL endpoint. Only Delta tables registered under /Tables are visible to T-SQL. External Delta tables created from Spark with a custom path, or anything in /Files, will not show up. Use a shortcut in the Tables section to surface them.
Only Delta is queryable through the SQL endpoint. Parquet, CSV, and JSON in the Lakehouse are reachable from Spark but invisible to T-SQL until you convert them to Delta tables.
Metadata sync is automatic but not instantaneous. When Spark writes a new table or changes a schema, the SQL analytics endpoint picks it up on its own. If a report runs a second after the notebook finishes and the table looks stale, the sync has not finished. You can trigger a manual refresh.
Foreign keys on the SQL endpoint freeze the schema. If you add a foreign key constraint between tables on the Lakehouse SQL endpoint, further schema changes (such as new columns) stop syncing until you drop it.
Default semantic models are gone. Since September 5, 2025, creating a Lakehouse or Warehouse no longer auto-creates a default semantic model, and existing default models were decoupled from their parent items by November 30, 2025. You build the model yourself. That surprises people following older tutorials.
Workspace limit. A workspace supports up to 150 warehouse and SQL analytics endpoint items combined. Each Lakehouse counts one endpoint toward that total.
Strings truncate at 8 KB on Lakehouse endpoints. The varchar(max) type works on the SQL analytics endpoints of mirrored items and Fabric SQL databases, but not on Lakehouse endpoints, where 8 KB truncation still applies to string columns. If you store long text (full documents, large JSON blobs) and need it intact through T-SQL, test this before you commit to the design.
A quick scenario check
| Situation | Recommendation |
|---|---|
| Finance team, SQL Server background, clean ERP extracts, nightly star schema | Warehouse |
| Engineering team, Python, API and file sources, ML downstream | Lakehouse |
| Mixed team, sources are messy, reports must be governed | Lakehouse (bronze, silver) plus Warehouse (gold) |
| Small company, one analyst, Power BI is the only consumer, data arrives via Dataflows Gen2 | Warehouse, because the analyst can maintain T-SQL views and the loads are tabular |
| Migrating from Synapse dedicated SQL pool | Warehouse |
| Migrating from Databricks or Synapse Spark pools | Lakehouse |
| Not sure yet, want to experiment for a month | Lakehouse |
If you are still weighing Fabric against the alternatives, the Fabric vs Databricks comparison covers the Spark-heavy side of that choice. Microsoft's own Warehouse vs Lakehouse decision guide is short and worth reading in full.
FAQ
Can I write to a Fabric Lakehouse with T-SQL? No. The Lakehouse SQL analytics endpoint is read-only. You can run SELECT, create views, functions, and stored procedures, but INSERT, UPDATE, DELETE, and MERGE are not supported. Writes go through Spark, pipelines, dataflows, or shortcuts.
Is a Fabric Warehouse faster than a Lakehouse for Power BI? Both store Delta tables in OneLake and both serve T-SQL through the same engine, so the speed difference for a well-modeled star schema is small. Direct Lake semantic models work with either. Model quality matters more than the item type.
Can Spark write to a Fabric Warehouse? Yes. The Spark connector for Fabric Data Warehouse, preinstalled in the Fabric runtime, supports reading from warehouses and SQL analytics endpoints and writing to warehouse tables in append, overwrite, ignore, and errorIfExists modes.
Should I use a Lakehouse or Warehouse for medallion architecture? Microsoft lists medallion (bronze, silver, gold) as a recommended Lakehouse use case. A common pattern is Lakehouse for bronze and silver and either a Lakehouse or Warehouse for gold, depending on whether the team that owns gold writes Spark or T-SQL.
Does a Lakehouse cost less than a Warehouse in Fabric? Neither item carries its own fee. Both run on the capacity units of the Fabric capacity you already pay for and store data in OneLake. Cost is driven by how much compute your loads and queries use, not by which item you pick.
Where Maxwize fits
Maxwize designs and builds Fabric environments for small and mid-sized businesses in the US, UK, UAE, and India, including the Lakehouse-plus-Warehouse layout above and the Power BI models on top. Data analytics projects run from $1,000 to $20,000 depending on sources and scope. If you want a second opinion on which item to start with, see our Microsoft Fabric services or get in touch.