Lakehouse vs Warehouse in Microsoft Fabric: T-SQL or PySpark?

Quick summary

Choosing between a Lakehouse and a Warehouse in Fabric starts with one question: which language will you work in? PySpark (or Spark SQL) points to the Lakehouse; T-SQL points to the Warehouse. Both store data in Delta format in OneLake and share the same SQL engine, so the difference is in how you develop, not in where the data lives.

The Microsoft decision guide boils it down to three questions:

What is your development language? Spark (Python, Scala, Spark SQL or R) → Lakehouse. T-SQL → Warehouse.
Do you need transactions that span multiple tables? Yes → Warehouse. No → Lakehouse.
What type of data are you analyzing? Structured and unstructured, or you’re not sure yet → Lakehouse. Structured only → Warehouse.

The result of these three questions is a starting point. The rest of this article shows how to confirm the choice.

What each one is

Warehouse is an enterprise-scale relational data warehouse, developed primarily in T-SQL. It offers ACID transactions across multiple tables, views, functions and stored procedures, and loads data through COPY INTO, pipelines, dataflows and cross-database queries. It can read and write Delta tables.

Lakehouse is an architecture for storing and analyzing structured and unstructured data in one place. It uses Delta Lake (ACID transactions, schema enforcement and time travel), supports shortcuts to external data without copying it, and lets you work with Spark in notebooks.

The SQL analytics endpoint is the detail that confuses people the most. Every Lakehouse gets an automatically generated T-SQL endpoint, but it is read-only: it accepts queries, views and table-valued functions, but not INSERT, UPDATE or DELETE. To write to Lakehouse tables, you use Spark, pipelines or dataflows.

T-SQL vs PySpark in practice

T-SQL is declarative and table-oriented: you describe the result and the SQL engine decides how to get there. PySpark is Python code on top of DataFrames, running in notebooks, which gives you more flexibility to read files in many formats, apply programmatic logic and handle data that doesn’t fit neatly into rows and columns.

The same goal, totaling sales per customer, looks like this on each side.

T-SQL in the Warehouse:

CREATE TABLE dbo.sales_by_customer AS
SELECT
    customer_id,
    COUNT(*)        AS order_count,
    SUM(amount)     AS total_amount
FROM dbo.orders
GROUP BY customer_id;

PySpark in the Lakehouse:

from pyspark.sql import functions as F

df = spark.read.table("orders")

sales_by_customer = (
    df.groupBy("customer_id")
      .agg(
          F.count("*").alias("order_count"),
          F.sum("amount").alias("total_amount"),
      )
)

sales_by_customer.write.format("delta").mode("overwrite").saveAsTable("sales_by_customer")

For this case, T-SQL is shorter and more direct. PySpark pays off when the earlier step is reading CSV, JSON or Parquet from a folder, fixing types, handling nulls and only then writing the table, all in the same notebook.

You can also use SQL inside the Lakehouse: Spark SQL runs in notebooks, and the SQL analytics endpoint accepts T-SQL for reading only. It is a useful way to let analysts query the tables, but it doesn’t replace the Warehouse when the transformation needs to write data in T-SQL.

A practical example: from CSV to report

Let’s see this in practice with a simple example: an orders CSV arrives and you need to deliver a sales-per-customer report.

The picture sums it up: PySpark handles everything that writes data, and T-SQL only comes in at the end, to query. That’s because the Lakehouse SQL analytics endpoint is read-only.

The first step is cleaning. We read the CSV, drop duplicate orders, convert the date and discard rows with no amount. The result becomes the orders table:

from pyspark.sql import functions as F

raw = (
    spark.read
    .option("header", True)
    .option("inferSchema", True)
    .csv("Files/orders/orders.csv")
)

orders = (
    raw.dropDuplicates(["order_id"])
       .withColumn("order_date", F.to_date("order_date"))
       .filter(F.col("amount").isNotNull())
)

orders.write.format("delta").mode("overwrite").saveAsTable("orders")

Next comes the aggregation, which is the same code from the previous section: it reads orders and writes the sales_by_customer table.

With the table ready, anyone can query the result in T-SQL through the Lakehouse endpoint:

SELECT TOP 10 customer_id, total_amount
FROM dbo.sales_by_customer
ORDER BY total_amount DESC;

Finally, the Power BI semantic model reads the gold tables and feeds the report.

And where does the Warehouse fit? If your team prefers to model the gold layer in T-SQL, the aggregation can happen in a Warehouse, which can query the Lakehouse tables without duplicating the data. Then the Lakehouse keeps the cleaning in PySpark and the Warehouse keeps the modeling in T-SQL.

Side by side

Criterion	Warehouse	Lakehouse (with SQL analytics endpoint)
Main language	T-SQL	Spark (PySpark, Spark SQL, Scala, R) in notebooks; T-SQL for reading only on the endpoint
Writing via T-SQL (DML)	Yes, with full transaction support	No: the endpoint is read-only
Multi-table transactions	Yes	Not the focus; the guide says to use the Warehouse
Data types	Structured	Structured and unstructured
Storage	Delta in OneLake	Delta in OneLake
Data loading	`COPY INTO`, `INSERT`, `CREATE TABLE AS SELECT`, pipelines, dataflows	Spark, pipelines, dataflows, shortcuts
T-SQL available	Full queries, DML and DDL	Full queries, no DML, limited DDL (views and table-valued functions)
Typical profile	SQL developers or citizen developers	Data engineers or SQL developers
Recommended use	Enterprise or departmental data warehouse; structured analysis in T-SQL	Medallion architecture (bronze, silver, gold); exploration; staging and archive zone

Source: Microsoft Learn, Warehouse and Lakehouse decision guide

When to choose each

Choose the Warehouse when:

your team works in T-SQL and wants to keep using tables, views, procedures and functions;
your data is already structured and the main goal is analysis and BI;
you need to write and transform data with T-SQL (INSERT, UPDATE, DELETE) with full transaction support;
a business rule requires a transaction that spans multiple tables.

Choose the Lakehouse when:

your team works with PySpark, Spark SQL or notebooks;
your data arrives as files in varied formats (CSV, JSON, Parquet) or includes unstructured data;
you want to organize the bronze, silver and gold layers of the medallion architecture;
you need to access external data without copying it, using OneLake shortcuts;
you don’t yet know how your data will evolve: the Microsoft guide points to the Lakehouse when there is doubt about the data type.

A practical rule: decide by team and data type, not by trend. A SQL-strong team forced into Spark loses productivity, and the reverse is also true.

Security, cost and performance

Security

Access combines Fabric permissions (a workspace role or an item permission) with granular SQL permissions. Microsoft’s recommendation is the principle of least privilege: someone who only needs to read can stay in the Viewer role, with access granted through T-SQL on specific objects.

In the Warehouse and the SQL analytics endpoint, you can protect data with object-level, column-level and row-level security, plus dynamic data masking, all through T-SQL and without changing applications. The Warehouse also offers user audit logs (through Microsoft Purview and PowerShell) and customer-managed encryption keys (CMK). For the Lakehouse endpoint, Microsoft keeps a dedicated page, OneLake security for SQL analytics endpoints.

Cost and capacity

Both run on the same Fabric capacity, whose units (CUs) are shared by all workloads. The difference is in how each side consumes:

Warehouse: consumption is measured in vNodes (each with four vCores), allocated and released as demand changes. Reads and writes on the Warehouse and reads on the Lakehouse SQL analytics endpoint consume capacity, and both show up together as Warehouse in the metrics app, because they use the same SQL engine.
Spark (Lakehouse): each CU maps to two Spark vCores. Billing starts when a notebook, a job or a Lakehouse operation begins running, and idle pool time is not billed. The session expires after 20 minutes by default; to stop billing sooner, end the session. There is also an autoscale billing option for Spark, with serverless resources and a maximum CU limit.

In practice, the cost question is not “which item is cheaper” but “how does my workload use the capacity”. The Fabric Capacity Metrics app shows that for both sides.

Performance

This article does not compare speed between Lakehouse and Warehouse: the result depends on the workload, the data volume and how the tables are written. Measure with a sample of your own data before deciding. For the Warehouse, Microsoft keeps a dedicated guide, Performance guidelines in Fabric Data Warehouse.

You don’t have to pick just one

Lakehouse and Warehouse work well together in the same project. Both use Delta in OneLake, and the Warehouse can run queries across warehouses and lakehouses without duplicating data. Microsoft itself lists pairing a Lakehouse with a Warehouse for enterprise analytics as a use case.

A common design in the medallion architecture:

Bronze: the Lakehouse receives raw data (files, extracts, shortcuts).
Silver: PySpark notebooks in the Lakehouse clean, type and deduplicate.
Gold: a dimensional model (facts and dimensions) ready for consumption. It can stay in the Lakehouse itself or live in a Warehouse, if the team prefers to model and transform in T-SQL.
Consumption: a semantic model and reports in Power BI.

There is no single answer for Gold. If the team wants T-SQL procedures and SQL writes, the Warehouse makes sense. If the whole chain is already in PySpark and consumption is read-only, keeping Gold in the Lakehouse means fewer moving parts.

Common pitfalls

Trying to write through the SQL analytics endpoint. It is read-only. If the plan is INSERT, UPDATE or DELETE in T-SQL, the right item is the Warehouse; in the Lakehouse, writes go through Spark, pipelines or dataflows.
Treating the two as separate worlds. Both store Delta in OneLake and you can query one from the other, so the choice doesn’t lock your architecture forever.
Mixing up Spark SQL and T-SQL. They are different dialects. A query that works in a notebook may not run on the endpoint or in the Warehouse without changes.
Choosing by tool, not by team. Before deciding, ask who will maintain the pipeline six months from now and which language that person is most productive in.

Frequently asked questions

Can I use T-SQL on the Lakehouse? Yes, for reading. The SQL analytics endpoint accepts queries, views and table-valued functions. To write, use Spark, pipelines or dataflows.

Can I use PySpark with the Warehouse? The Warehouse is developed in T-SQL. There is a Spark connector to access its data, described in the documentation listed in the sources.

Does the SQL analytics endpoint consume capacity? Yes. Reading on the endpoint counts as Warehouse compute usage and appears together with it in the metrics app.

Does choosing one lock me in forever? No. Both store Delta in OneLake and you can query one from the other. What doesn’t migrate on its own is the code: a transformation written in PySpark has to be rewritten to run in T-SQL, and the reverse is also true.

Conclusion

Choose the Lakehouse if your work is PySpark, files and data of many types; choose the Warehouse if it is T-SQL, structured data and writing through SQL. When in doubt about the data type, start with the Lakehouse, and remember that both can live in the same project.

Sources

Prefer to see it in practice? In the video Lakehouse x Warehouse no Fabric (Spark x T-SQL) — Direto pra DP-600 I show the differences directly in Fabric. The video is in Portuguese.