Fabric Warehouse vs lakehouse SQL analytics endpoint

Fabric has two warehousing items that share one SQL engine: the Warehouse item and the SQL analytics endpoint that is provisioned automatically for every lakehouse (and for warehouses, mirrored databases and SQL databases). The deciding question is whether you need to write data with T-SQL.

• A Warehouse supports full DQL, DML and DDL: you create, alter and drop tables and run INSERT, UPDATE, DELETE, MERGE and TRUNCATE, with full multi-table ACID transactions.
• The SQL analytics endpoint of a lakehouse is read-only over the Delta tables in the lakehouse. You can query them, create views, inline table-valued functions and stored procedures, and apply SQL security, but you can’t insert, update or delete data through it; data changes go through Spark.
• A warehouse snapshot is a third, user-created read-only child item of a warehouse (covered later in this chapter).

Metadata sync keeps the endpoint aligned with the lakehouse: a background process reads the Delta logs in the /Tables folder and creates, updates or removes the matching SQL tables. You can force a sync with the Refresh button in the endpoint’s Explorer or the Refresh SQL analytics endpoint metadata REST API. A newer metadata sync process, enabled per workspace under Warehouse settings, is in preview; it applies only to endpoints created after it’s enabled and adds the sys.sp_dw_refresh_ext_table procedure for refreshing one table. Only Delta tables in the /Tables folder are discovered; external Delta tables created with Spark code stay invisible unless you add a Tables-section shortcut.

Two workspace-level facts are worth knowing. A workspace supports up to 150 warehouse and SQL analytics endpoint items combined, and the Warehouse and endpoint share a user session limit of 2,048 per workspace.

Power BI default semantic models no longer exist for these items: since 5 September 2025 they haven’t been created automatically for new warehouses, lakehouses or mirrored items, and by 30 November 2025 existing ones were decoupled into independent semantic models. To report on a warehouse you create a semantic model yourself (Direct Lake is the storage mode for new models created on a Warehouse or SQL analytics endpoint); semantic model design is covered in “Designing semantic models: storage modes, relationships and composite models”.

Common trap: Running UPDATE or INSERT against a lakehouse table through its SQL analytics endpoint - the endpoint is read-only over Delta tables, so the statement fails; change lakehouse data with Spark, or load the data into a Warehouse if T-SQL writes are required.

Common trap: Expecting a new warehouse to come with a default semantic model whose tables are added automatically - default semantic models haven’t been created since September 2025 and existing ones were decoupled, so you create and maintain a semantic model explicitly.

Get the whole book

This note is one section of Ultra Transcenders DP-600: Implementing Analytics Solutions Using Microsoft Fabric, an independent study guide that explains every topic the exam covers by technology, with comparison tables, diagrams and the common traps, plus a glossary linked to Microsoft Learn.

About the book · DP-600 terms in the glossary · All DP-600 study notes