Warehouses in Fabric

What is a Fabric Warehouse?

Warehouse architecture

Storage

Warehouse data lives in OneLake as Delta Parquet files — the same format as lakehouse data. This means:

• Other Fabric items can read warehouse data via cross-database queries or shortcuts
• Data is not locked into a proprietary format
• You benefit from Delta features like ACID transactions and schema enforcement

Compute

The warehouse uses a distributed SQL engine that scales automatically within your Fabric capacity. There are no dedicated SQL pools to manage, no pause/resume decisions, and no manual scaling.

Automatic features

Feature	What It Does
Auto statistics	Automatically generates query statistics for the optimiser
Auto caching	Frequently accessed data is cached in memory
Default semantic model	A Power BI semantic model is auto-generated from warehouse tables
SQL connection string	Connect with SSMS, Azure Data Studio, or any ODBC/JDBC tool

When to use a warehouse vs a lakehouse

This is one of the most exam-tested decisions in DP-600.

Both store data as Delta Parquet in OneLake — the difference is how you interact with the data

Factor	Lakehouse	Warehouse
Primary language	PySpark / Scala / SQL (read-only via endpoint)	T-SQL (full DML)
Write access	Spark writes; SQL endpoint is read-only	Full SQL DML — INSERT, UPDATE, DELETE, MERGE
Stored procedures	Not available in lakehouse	Full T-SQL stored procedures
File formats ingested	Any — CSV, Parquet, JSON, Avro, ORC, etc.	Structured data via SQL INSERT or COPY INTO
Best for	Data engineers, Spark users, unstructured/semi-structured data	SQL-first teams, BI professionals, structured data
Power BI connection	Via SQL analytics endpoint (read-only)	Via default semantic model or SQL connection
Cross-database queries	Can query warehouse tables from lakehouse SQL endpoint	Can query lakehouse tables from warehouse

Decision framework

Ask these questions to choose:

1. Does your team primarily use SQL? → Warehouse
2. Do you need stored procedures and views with DML? → Warehouse
3. Do you need to process unstructured data (JSON, images, logs)? → Lakehouse
4. Do you need PySpark transformations? → Lakehouse
5. Do you need both SQL DML and Spark? → Use both — they can cross-query each other

💡 Scenario: Raj builds the financial warehouse

Raj at Atlas Capital builds a Fabric Warehouse for the finance team. They need:

• Stored procedures to calculate daily P&L (profit and loss)
• MERGE statements to update position data from a trading platform
• Views that hide raw data complexity from report builders
• Cross-database queries to join warehouse data with a lakehouse containing external market data

The warehouse handles all of this with familiar T-SQL syntax. Raj does not need to learn Spark — he works entirely in SQL.

Key SQL features in the Fabric Warehouse

COPY INTO

The fastest way to bulk-load data into a warehouse table:

COPY INTO dbo.daily_positions
FROM 'https://onelake.dfs.fabric.microsoft.com/...'
WITH (FILE_TYPE = 'PARQUET')

Cross-database queries

Query across lakehouses and warehouses in the same workspace:

-- From a warehouse, query a lakehouse's SQL endpoint
SELECT p.product_name, s.total_sales
FROM lakehouse_freshcart.dbo.products AS p
JOIN dbo.sales_summary AS s ON p.product_id = s.product_id

Stored procedures

Full T-SQL stored procedures with parameters, variables, and control flow:

CREATE PROCEDURE dbo.UpdateDailyPnL
    @trade_date DATE
AS
BEGIN
    MERGE dbo.daily_pnl AS target
    USING (
        SELECT portfolio_id, SUM(market_value - cost_basis) AS pnl
        FROM dbo.positions
        WHERE trade_date = @trade_date
        GROUP BY portfolio_id
    ) AS source
    ON target.portfolio_id = source.portfolio_id
       AND target.trade_date = @trade_date
    WHEN MATCHED THEN UPDATE SET pnl = source.pnl
    WHEN NOT MATCHED THEN INSERT (portfolio_id, trade_date, pnl)
         VALUES (source.portfolio_id, @trade_date, source.pnl);
END

💡 Exam tip: What SQL features are NOT supported?

The Fabric Warehouse supports most T-SQL but not everything from SQL Server. Notable gaps include:

• Triggers — not supported
• Cursors — not supported (use set-based logic instead)
• Temporary tables (#temp) — use CTEs or table variables instead
• Full-text search — not available
• SQL Agent jobs — use Fabric Pipelines for scheduling

The exam may test whether you can identify unsupported features. The general pattern: if the feature relies on row-by-row processing or server-level configuration, it probably is not supported.

The default semantic model

Every warehouse automatically creates a Power BI semantic model containing:

• All warehouse tables and views
• Basic relationships (if defined via foreign keys)
• A DirectQuery connection to the warehouse

This means Power BI report builders can start creating reports immediately without manually setting up a data connection or importing data. The semantic model updates automatically as you add tables or views.

💡 Scenario: James sets up a client reporting layer

James at Summit Consulting creates a warehouse for Client B with three views: vw_revenue, vw_expenses, and vw_headcount. The warehouse’s default semantic model immediately exposes these views to the client’s Power BI developers.

James does not need to create a separate semantic model or configure a data connection. The client’s report builders open Power BI, connect to the workspace, and see the views ready to use.

For more complex modelling (DAX measures, calculation groups), James creates a custom semantic model later — but the default model gets the team started in minutes.

Question

What is the primary difference between a Fabric Warehouse and a lakehouse SQL analytics endpoint?

Click or press Enter to reveal answer

Answer

The Fabric Warehouse supports full SQL DML (INSERT, UPDATE, DELETE, MERGE) and stored procedures. The lakehouse SQL analytics endpoint is read-only — you can query but not write via SQL. Both store data as Delta Parquet in OneLake.

Click to flip back

Question

What happens automatically when you create a Fabric Warehouse?

Click or press Enter to reveal answer

Answer

Fabric automatically creates: (1) A default semantic model for Power BI reporting, (2) A SQL connection string for SSMS/Azure Data Studio, (3) Auto-statistics for query optimisation, and (4) Auto-caching for frequently accessed data.

Click to flip back

Question

Can a warehouse query data from a lakehouse in the same workspace?

Click or press Enter to reveal answer

Answer

Yes. Cross-database queries let a warehouse query lakehouse tables (via the SQL analytics endpoint) and vice versa. The syntax uses three-part names: lakehouse_name.schema.table.

Click to flip back

Knowledge Check

Raj at Atlas Capital needs to run a nightly stored procedure that uses MERGE to update 500,000 financial position records. The data source is structured (CSV from a trading platform). Which Fabric item should he use?

ALakehouse — because it handles large data volumes via SparkBWarehouse — because it supports stored procedures and MERGE statementsCDataflow Gen2 — because it is designed for data transformationsDEventhouse — because financial data needs real-time processing

Check Answer

Knowledge Check

James at Summit Consulting needs a single SQL query to join revenue data from Client A's warehouse with external market benchmarks stored in a lakehouse. Both are in the same Fabric workspace. Is this possible, and how?

ANot possible — warehouses and lakehouses use different storage formats and cannot be joinedBYes — use cross-database queries with three-part naming (lakehouse_name.schema.table)CYes — but only by copying the lakehouse data into the warehouse firstDYes — but only via a Fabric Pipeline that joins and materialises the result

Check Answer

🎬 Video coming soon

---

Next up: Choosing the Right Data Store — lakehouse vs warehouse vs Eventhouse: a decision framework for every scenario.