Microsoft Fabric
Microsoft Fabric Lakehouse vs Warehouse: Which One Should You Use?
A practical decision guide for Fabric Lakehouse and Warehouse: write paths, Spark and T-SQL access, the SQL analytics endpoint, transactions, governance and when to combine both.
On this page
The Lakehouse vs Warehouse question comes up in almost every Microsoft Fabric project, and it is confusing for a good reason: both items store their tables as Delta Lake in OneLake, and both can be queried with T-SQL through the same SQL engine. If the storage format and the query engine are shared, what is actually different?
The short answer is who writes the data, and with which engine. A Lakehouse is written by Spark. A Warehouse is written by T-SQL. Almost every other difference (transactions, maintenance, security surfaces, team workflow) follows from that one fact. This guide explains the differences that matter in production and gives you a decision matrix you can apply one dataset at a time.
What a Fabric Lakehouse is
A Lakehouse is a Fabric item for data engineering with Apache Spark. It has two areas:
- Files, for any file: CSV, JSON, Parquet, images, raw extracts, landing folders.
- Tables, for managed Delta tables that Spark creates under the
Tablesfolder.
You write to a Lakehouse with Spark: notebooks, Spark job definitions, or pipelines and Dataflows that land data into it. Spark gives you Python, Scala, R and Spark SQL, the whole Python ecosystem, and fine-grained control over how files are laid out. Each Delta table has its own ACID guarantees, schema enforcement and time travel (what that means for SQL Server engineers). OneLake shortcuts let a Lakehouse reference data in other Lakehouses, Warehouses or external storage without copying it.
The price of that flexibility is ownership. In a Lakehouse, you own table health: file sizes, OPTIMIZE, VACUUM, partitioning or clustering, and the small-file problems that frequent writes create. Delta Table Maintenance in Microsoft Fabric covers what that ownership involves.
What a Fabric Warehouse is
A Warehouse is a Fabric item for relational data warehousing developed primarily with T-SQL. You create schemas, tables, views, functions and stored procedures, and you load and transform data with INSERT, UPDATE, DELETE, MERGE, CREATE TABLE AS SELECT, COPY INTO, pipelines or Dataflows.
Its data is also stored as Delta tables in OneLake. The Warehouse publishes Delta Lake logs for its tables, so other engines, Spark included, can read them. Writes must go through the Warehouse engine: T-SQL, the ingestion paths above, or the Spark connector for Fabric Data Warehouse, which writes through the Warehouse rather than directly to its files.
What the Warehouse adds is a managed relational experience:
- Multi-table ACID transactions.
BEGIN TRAN…COMMITcan span several tables, and supported DDL can run inside a transaction too. - Managed table health. Compaction, checkpointing and garbage collection are handled by the service. There is no
OPTIMIZEschedule to own. - A familiar T-SQL surface for SQL developers and analysts, with automatic statistics, identity columns, temporary tables and
MERGE.
Some SQL Server habits do not carry over. Primary keys, unique keys and foreign keys can be declared only as NOT ENFORCED: they inform the optimizer and modelling tools, but the engine does not reject duplicates. ALTER TABLE supports only a subset of operations. If your loading pattern assumes the database will enforce uniqueness, you have to enforce it in the load instead.
To see these points in practice, Create Your First Fabric Warehouse with T-SQL builds a small Warehouse step by step: schema, tables, DML, a view, a transaction and a cross-database query to a Lakehouse.
The SQL analytics endpoint is not a Warehouse
Every Lakehouse automatically gets a SQL analytics endpoint. It runs on the same engine as the Warehouse, and from a query editor it looks similar, which is where most confusion starts.
The endpoint is a read-only T-SQL surface over the Lakehouse’s Delta tables:
- You can
SELECTfrom any Delta table underTables, including tables exposed through shortcuts. - You can create views, inline table-valued functions and stored procedures that persist in the endpoint.
- You can apply SQL security: object-level, column-level and row-level permissions.
- You cannot
INSERT,UPDATEorDELETE. To change data, you go back to Spark.
Three details matter in practice:
- Metadata sync. When Spark creates or changes a table, the endpoint detects the change and updates its SQL metadata automatically. The lag is usually small, but a pipeline that writes with Spark and immediately reads through the endpoint should account for it.
- Only the
Tablesarea is exposed. Delta files underFiles, and external Delta tables created with Spark outsideTables, are not visible to the endpoint. Use shortcuts inTablesinstead. - SQL security applies only to SQL access. Permissions set on the endpoint protect reads through the endpoint. They do not apply when the same data is read through Spark or OneLake. Workspace roles and OneLake security still decide who can read the files.
So a Lakehouse plus its endpoint gives you “Spark writes, SQL reads”. A Warehouse gives you “SQL reads and writes, with transactions”. They solve different problems.
Write paths decide most of the design
When I start a design, I ask how each dataset will be written, not how it will be queried. Reads are flexible in Fabric; writes are not.
| Write pattern | Natural home | Why |
|---|---|---|
| Raw files arriving from sources, semi-structured JSON, landing zones | Lakehouse | Needs the Files area and Spark parsing |
| Large transformations in Python or PySpark, ML features | Lakehouse | Spark is the engine you need |
| Upserts with complex Python logic, change data from many sources | Lakehouse | Delta MERGE in Spark, full control of the data layout |
| Dimensional loads written by SQL developers in stored procedures | Warehouse | T-SQL procedures, transactions, managed maintenance |
| Several tables that must change together or not at all | Warehouse | Multi-table transactions |
| Analyst-owned serving tables, curated marts | Warehouse | T-SQL is the team’s language |
Two datasets in the same platform can land in different rows of this table. That is normal, and it is the reason mixed architectures are common.
Reads: Spark, T-SQL and Power BI
Because both items are Delta in OneLake, reads cross engines easily:
- Spark reads Lakehouse tables natively and can read Warehouse tables through their published Delta logs or the Spark connector.
- T-SQL reads Lakehouse tables through the SQL analytics endpoint and Warehouse tables directly. Cross-database queries can join a Warehouse with a Lakehouse endpoint in the same workspace without copying data.
- Power BI semantic models can use Direct Lake over either item, or connect through the SQL endpoint.
The practical consequence: do not copy a table into a Warehouse only so analysts can query it with T-SQL. The endpoint already gives them that, read-only. Copy it when the Warehouse will own the next transformation.
Workloads: data engineering vs BI and reporting
Data engineering workloads tend to belong in a Lakehouse. They involve many input formats, schema drift, heavy transformations, deduplication and repeated reprocessing. Engineers want notebooks, version-controlled Python, unit-testable functions and control over files and partitions. The Medallion architecture usually places Bronze and Silver here.
BI and reporting workloads often belong in a Warehouse. They are relational, modelled as facts and dimensions, maintained by people fluent in T-SQL, and consumed by reports that need stable, governed tables. Stored procedures, views and SQL permissions fit that work. A Gold layer modelled as a star schema is a common Warehouse use.
That split is a tendency, not a rule. A small team of Spark engineers can serve reports perfectly well from a Lakehouse through its SQL analytics endpoint. A SQL team can run all of its transformations in a Warehouse if the sources are already tabular.
Schema, governance and security
Both items enforce a schema on their tables, but they evolve it differently:
- In a Lakehouse, schema changes happen in Spark. Delta schema evolution can add columns during a write, which is convenient and also a way to let source drift silently reach downstream consumers. Decide explicitly which writes may evolve a schema.
- In a Warehouse, schema changes are T-SQL DDL. They are explicit, reviewable and can be developed as a database project in Git, which suits change-controlled environments.
For security, think about every path to the data. A Warehouse is mostly accessed through T-SQL, so SQL permissions cover most reads. A Lakehouse is reached through Spark, OneLake and the endpoint; SQL permissions on the endpoint alone do not protect it. If row-level or column-level rules are mandatory, either serve that data through a Warehouse or SQL-only path, or use OneLake security so the rules apply beyond SQL.
Performance considerations
Neither item is “faster”. Each makes different performance work your responsibility.
Lakehouse performance depends on table layout. Many small files slow every reader, including the SQL analytics endpoint and Direct Lake. Plan compaction with OPTIMIZE, clean old files with VACUUM, choose clustering or partitioning deliberately, and write in reasonable batches. Notebook speed is its own topic, covered in How to Make Microsoft Fabric Notebooks Faster, and upsert-heavy tables need the patterns in Delta Lake MERGE Performance Patterns for Large Tables.
Warehouse performance depends more on SQL design: statistics, appropriate data types, avoiding very wide string columns, batching DML instead of row-by-row trickle inserts, and keeping transactions short. The service manages compaction, but it cannot fix a design that updates one row at a time thousands of times an hour.
Endpoint reads inherit the Lakehouse layout. A slow query through the SQL analytics endpoint is often a file layout problem on the Spark side, not a SQL problem.
When to combine both
Many production platforms use both items, each with a clear job:
- Lakehouse for landing and engineering. Raw files land in
Files. Spark notebooks parse, validate, deduplicate and conform them into Delta tables. - Warehouse for serving. A Warehouse reads the conformed Lakehouse tables through cross-database queries and builds dimensional models with T-SQL owned by the BI or analytics team.
- Power BI on the serving layer, with Direct Lake or SQL connections.
The boundary between the two is a contract: the Lakehouse team guarantees conformed tables with a documented schema; the Warehouse team owns the model built on top. Shortcuts and cross-database queries let the Warehouse read Lakehouse tables without a copy step, so the boundary does not have to cost a full data movement. The broader layout of workspaces and items is covered in Designing a Scalable Microsoft Fabric Architecture.
A related pattern applies to operational databases: mirroring Azure SQL into Fabric gives you a read-only, continuously replicated copy with its own SQL analytics endpoint, which a Lakehouse or Warehouse can then use as a source.
Anti-patterns
- Treating the SQL analytics endpoint as a Warehouse. Teams build stored procedures in the endpoint expecting to write data, then discover it is read-only.
- Copying every Lakehouse table into a Warehouse “so SQL users can see it”. The endpoint and cross-database queries already give read access.
- Keeping the same table in both items with two pipelines writing it. It doubles cost and guarantees the copies drift.
- Relying on declared primary keys for uniqueness in a Warehouse. They are
NOT ENFORCED; duplicates are accepted. - Ignoring Lakehouse maintenance and then blaming the SQL engine for slow endpoint queries over thousands of small files.
- Choosing per platform instead of per dataset. “We are a Lakehouse shop” forces SQL developers into notebooks, and the reverse forces Spark engineers into stored procedures.
- Assuming endpoint permissions protect the files. They do not cover Spark or OneLake access.
Decision matrix
Answer these for one dataset or one layer at a time.
| Question | If yes, lean towards | Notes |
|---|---|---|
| Is Spark required to write or transform the data? | Lakehouse | Python libraries, ML, complex parsing |
| Is T-SQL the primary interface for writing it? | Warehouse | Stored procedures, SQL developers |
| Do several tables have to change in one transaction? | Warehouse | Multi-table ACID transactions |
| Are analysts the main consumers, querying with SQL? | Either | Lakehouse endpoint for reads; Warehouse if they also own writes |
| Is the team working with raw files or semi-structured data? | Lakehouse | Files area and Spark readers |
| Is dimensional modelling central to the layer? | Warehouse | Facts, dimensions, governed serving tables |
| Do you want the service to manage compaction? | Warehouse | Lakehouse maintenance is your job |
| Do you need fine control of file layout, partitioning or clustering? | Lakehouse | Spark gives that control |
| Do different layers have different owners and languages? | Both | Lakehouse for engineering, Warehouse for serving |
If your answers split across the columns, you are probably looking at a mixed architecture, and that is fine. The printable Lakehouse vs Warehouse decision guide condenses these questions into a one-page checklist.
Production checklist
Before committing to a choice for a dataset:
- Test with representative volume and real query patterns; small samples mislead in both directions.
- Write down the owner of each table and the language they will use to change it.
- Decide who owns maintenance for Lakehouse tables and how often it runs.
- Map every read path (Spark, T-SQL, OneLake, Power BI) to the security control that protects it.
- Check how schema changes will be reviewed and deployed, and that the item supports your deployment approach.
Recommendation
Choose by write path and ownership, not by query preference. Use a Lakehouse where Spark engineers write and reshape data, especially raw and semi-structured inputs. Use a Warehouse where SQL developers own relational models, need multi-table transactions, and prefer managed maintenance. Use the Lakehouse SQL analytics endpoint for read-only T-SQL access to Spark-written tables, but never as a substitute for a Warehouse.
When in doubt, start with the simplest option your team can operate well, keep a clear contract between layers, and add the other item when a real requirement (transactions, Spark processing, a different owner) appears.
This decision is also a classic interview topic: see how to answer it, and the questions that usually follow, in the Microsoft Fabric interview questions guide.
Was this helpful?
What could be better?
Thanks — that helps me improve Javi Builds.
Continue learning
What should I build next?
I'm building Javi Builds around practical problems data engineers actually face. Tell me what you'd like to see next.
Stay in the loop
Get the Javi Builds updates you actually care about.
Check your inbox
I sent you a confirmation link. Your subscription will be active after you confirm it.