Overview#
Fabric's SQL estate includes Warehouse for T-SQL analytics over OneLake, the Polaris distributed query engine behind key SQL experiences, SQL analytics endpoints for lakehouse and replicated data, and SQL database in Fabric for operational workloads replicated into OneLake [S1] [S2] [S3] [S4]. The common theme is SQL access over Fabric-governed data with OneLake as the analytical storage layer [S1] [S3].
Core concepts#
A Fabric Warehouse is a T-SQL analytics item that stores data in OneLake using open Delta/Parquet format while presenting a warehouse-oriented SQL surface [S1]. Lakehouse SQL analytics endpoints expose lakehouse Delta tables through a read-only T-SQL surface, while SQL database in Fabric provides an operational SQL engine whose data is automatically replicated to OneLake for analytics [S3] [S4].
Polaris is the distributed query-processing architecture that explains how SQL work is decomposed and scheduled across stateless compute against lake-backed storage [S2]. That distinction matters: choosing a Warehouse, lakehouse endpoint, or SQL database is partly about write semantics and workload type, not just SQL syntax [S1] [S3] [S4].
SQL surface decision matrix#
| Workload need | Use | Why | Source |
|---|---|---|---|
| SQL-first analytical warehouse | Fabric Warehouse | Provides a warehouse item and T-SQL analytical surface over OneLake-backed data | [S1] |
| Spark-owned Delta tables with SQL consumers | Lakehouse SQL analytics endpoint | Exposes managed lakehouse tables through a read-only SQL surface | [S3] |
| Operational app data with analytical mirror | SQL database in Fabric | Separates transactional write path from OneLake-backed analytical access | [S4] |
| Deep distributed SQL behavior | Polaris concepts | Explains task planning, distributed execution, and stateless compute design | [S2] |
How it works and best practices#
Use Warehouse when the team needs a SQL-first analytical serving layer with warehouse management patterns [S1]. Use a lakehouse SQL analytics endpoint when Spark owns table writes and SQL consumers need read-only access to managed Delta tables [S3]. Use SQL database in Fabric when the application workload is operational and the analytical copy should appear in OneLake automatically [S4].
Design for the read/write contract. Lakehouse SQL endpoints are read-only, so mutations belong in Spark or another write path [S3]. SQL database analytical queries should use the analytics endpoint rather than competing with transactional operations on the primary database [S4].
Use warehouse performance guidance as a design input, not an afterthought. Query shape, statistics, caching, and workload-management behavior are part of the warehouse architecture, especially when multiple report, ELT, and ad-hoc SQL workloads share the same capacity [S6] [S7] [S8].
-- Lakehouse SQL analytics endpoint pattern: read curated Delta tables.
-- The endpoint is read-only, so this is a serving/query surface, not the write path.
CREATE VIEW reporting.vw_sales_daily AS
SELECT
OrderDate,
SUM(SalesAmount) AS DailySales
FROM lakehouse_gold.Sales
GROUP BY OrderDate;
Inference: the view illustrates a read-only serving pattern over curated tables. The sourced mechanics are that lakehouse SQL analytics endpoints expose Delta tables through a read-only T-SQL surface and can persist SQL objects such as views [S3].
What goes wrong#
Forcing every SQL workload into a Warehouse can miss the point of Fabric's separate SQL surfaces. Operational writes, lakehouse-managed Delta tables, and SQL-first curated warehouses have different ownership and performance models [S1] [S3] [S4].
Another failure is running operational reporting directly against the SQL database transactional path. SQL database in Fabric provides a read-only analytical endpoint so reporting can use the OneLake-replicated analytical path instead of competing with application writes [S4].
Internals#
Architecture & design#
Warehouse data persists in OneLake, and Polaris decomposes SQL work into distributed execution over lake-backed data [S1] [S2]. SQL database in Fabric uses an operational SQL architecture while replicating analytical data into OneLake for downstream query paths [S4] [S5].
How it works internally#
Polaris uses distributed planning and execution concepts to schedule work across stateless compute resources [S2]. Lakehouse SQL endpoint metadata sync reads Delta transaction information from table folders rather than rewriting the underlying data [S3]. SQL database in Fabric separates transactional access from the read-only analytical endpoint [S4].
Caching matters inside the warehouse path: Fabric Warehouse uses in-memory and disk caching behavior that can change cold-versus-warm query performance, so repeated query tests may not represent first-run behavior [S6]. Workload management controls how warehouse work consumes resources and should be considered alongside capacity placement [S7].
Performance characteristics#
Warehouse performance is shaped by caching, statistics, query shape, and workload management [S6] [S7] [S8]. SQL endpoint freshness depends on metadata sync over Delta logs, and SQL database analytical freshness depends on the platform replication path into OneLake [S3] [S4].
-- Performance investigation pattern: compare grouped analytical query shapes.
-- Use real workspace tables and inspect behavior under cold and warm cache conditions.
SELECT
CustomerSegment,
SUM(NetSalesAmount) AS NetSalesAmount,
COUNT_BIG(*) AS RowCount
FROM warehouse_gold.FactSales
GROUP BY CustomerSegment;
Inference: the SQL is an example query shape for testing; the sourced guidance is that warehouse performance is affected by caching, workload management, and performance practices [S6] [S7] [S8].
Worked example#
A curated finance mart can use a Warehouse when SQL teams own transformations and reporting tables [S1]. A lakehouse engineering team can instead write Delta with Spark and expose those tables to T-SQL consumers through the lakehouse endpoint [S3]. An application team can use SQL database in Fabric for transactional writes and let Fabric mirror the analytical copy into OneLake [S4].
Operational order capture
-> SQL database in Fabric transactional engine
-> automatic analytical copy in OneLake
-> SQL analytics endpoint for reporting
-> Power BI semantic model for governed consumption
For a migration, keep the target choice explicit: SQL-first marts go to Warehouse, Spark-owned curated tables stay in lakehouse with SQL endpoint serving, and application-owned operational data belongs in SQL database with analytical replication [S1] [S3] [S4].