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.

By JaviPublished 12 min readIntermediate

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 Tables folder.

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 … COMMIT can 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 OPTIMIZE schedule 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 SELECT from any Delta table under Tables, 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, UPDATE or DELETE. To change data, you go back to Spark.

Three details matter in practice:

  1. 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.
  2. Only the Tables area is exposed. Delta files under Files, and external Delta tables created with Spark outside Tables, are not visible to the endpoint. Use shortcuts in Tables instead.
  3. 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:

  1. Lakehouse for landing and engineering. Raw files land in Files. Spark notebooks parse, validate, deduplicate and conform them into Delta tables.
  2. 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.
  3. 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.

Tags

  • Microsoft Fabric
  • Fabric Lakehouse
  • Fabric Warehouse
  • SQL Endpoint
  • Data Architecture