Microsoft Fabric Data Warehouse Optimization — Complete 2026 Guide

Storage Organization

Query performance in Fabric Warehouse starts with how data is physically organized on disk. Delta Parquet files in OneLake are the foundation — how those files are sized, partitioned, and clustered determines whether your queries scan gigabytes or terabytes.

Partitioning Strategy

Partition large fact tables by low-cardinality columns that align with your most common query filters. Date is the most effective partition key for most analytical workloads — queries filtered by date can skip entire file partitions without scanning them.

Avoid over-partitioning. A partition column with thousands of distinct values creates thousands of small files, which increases Delta log metadata overhead and slows query planning. A good target is partitions that each contain at least several hundred MB of data after compaction.

-- Partition a fact table by year and month
CREATE TABLE dw.fact_sales
(
  order_id    INT            NOT NULL,
  customer_id INT            NOT NULL,
  sale_year   INT            NOT NULL,
  sale_month  INT            NOT NULL,
  amount      DECIMAL(18,2)
);

File Compaction with OPTIMIZE

Incremental loads — especially from pipelines that insert small batches — accumulate many small Parquet files. Small files increase per-file overhead during query planning and reduce read parallelism. Run OPTIMIZE periodically to compact them.

-- Compact small Delta files on a high-ingestion table
OPTIMIZE dw.fact_sales;

-- Add ZORDER to co-locate rows by a selective filter column
OPTIMIZE dw.fact_sales
ZORDER BY (customer_id);

Data Ingestion Patterns

How you load data into Fabric Warehouse affects both throughput and the downstream query performance of loaded tables. Two primary T-SQL ingestion patterns exist — COPY INTO for bulk loads from external storage, and INSERT…SELECT for cross-warehouse transformation queries.

COPY INTO — Bulk Ingestion from Azure Storage

COPY INTO is the fastest supported path for loading large volumes of data into Fabric Warehouse from ADLS Gen2 or OneLake shortcuts. It runs in parallel across the distributed engine and is the recommended approach for initial historical loads and daily bulk refreshes.

COPY INTO dw.fact_sales
FROM 'https://youraccount.dfs.core.windows.net/container/sales/*.parquet'
WITH (
  FILE_TYPE = 'PARQUET',
  CREDENTIAL = (IDENTITY = 'Managed Identity')
);

INSERT…SELECT — Cross-Warehouse Transformation

Use INSERT…SELECT for incremental loads that involve joins, filters, and transformations from Silver layer tables. This pattern supports referencing tables across Lakehouses and Warehouses in the same workspace.

INSERT INTO dw.fact_sales (order_id, customer_id, sale_year, sale_month, amount)
SELECT
  s.order_id,
  s.customer_id,
  YEAR(s.sale_date),
  MONTH(s.sale_date),
  s.amount
FROM refined.sales_clean s
WHERE s.sale_date >= DATEADD(DAY, -1, CAST(GETDATE() AS DATE))
  AND s.amount >= 0;

Batch Sizing

For pipelines that load from source systems, target individual batch files above 100 MB before ingestion. Files smaller than this increase per-file scheduling overhead during COPY INTO and accumulate as small files requiring more frequent OPTIMIZE runs. If your source produces small files, stage and merge them in a Lakehouse notebook before loading into the Warehouse.

Query Optimization

Fabric’s distributed SQL engine handles query planning automatically, but query structure and schema design still determine whether the engine can skip data effectively and run plans in parallel.

Predicate Pushdown

Write WHERE clauses that the engine can evaluate against partition metadata before reading any Parquet files. Predicates on the partition column (e.g. sale_year, sale_month) enable partition pruning. Predicates on Z-ordered columns reduce the number of row groups read within each file.

-- Efficient: partition pruning + Z-ORDER benefit
SELECT region, SUM(amount) AS revenue
FROM dw.fact_sales
WHERE sale_year = 2026
  AND sale_month = 6
  AND customer_id BETWEEN 10000 AND 20000
GROUP BY region;

-- Inefficient: function on partition column prevents pruning
SELECT region, SUM(amount)
FROM dw.fact_sales
WHERE YEAR(sale_date) = 2026  -- use sale_year column instead
GROUP BY region;

Materialized Views for Hot Aggregations

Create materialized views for aggregations that power high-concurrency dashboards. The engine refreshes them incrementally — you do not manage refresh schedules. Queries that match a materialized view definition are automatically redirected to the pre-aggregated result.

CREATE MATERIALIZED VIEW dw.mv_daily_revenue
AS
SELECT
  sale_year,
  sale_month,
  region,
  SUM(amount)  AS total_revenue,
  COUNT(*)     AS order_count
FROM dw.fact_sales f
JOIN dw.dim_customer c ON f.customer_id = c.customer_id
GROUP BY sale_year, sale_month, region;

Avoid RLS in Warehouse for Direct Lake Models

Applying row-level security at the T-SQL layer in the Warehouse forces Power BI Direct Lake models to fall back to DirectQuery mode — which removes the performance benefit of Direct Lake entirely. For Power BI scenarios, apply RLS in the semantic model layer instead. Reserve T-SQL RLS for users accessing the Warehouse directly via SSMS, Tabular Editor, or API.

Statistics Management — January 2026 Updates

The query optimizer in Fabric Warehouse uses column statistics — histograms of value distribution — to build efficient query plans. Stale or missing statistics cause the optimizer to make poor cardinality estimates, leading to suboptimal join orders and over-allocated memory.

Both proactive and incremental statistics refresh are enabled by default. You do not need to run CREATE STATISTICS or UPDATE STATISTICS manually for system-managed statistics. For custom statistics on specific column combinations, you can still create them manually.

-- Check existing statistics on a table
SELECT
  stat_name,
  column_name,
  last_updated
FROM sys.dm_pdw_nodes_db_stats_properties
WHERE object_id = OBJECT_ID('dw.fact_sales')
ORDER BY last_updated DESC;

V-Order — Write-Time Parquet Optimization

V-Order is a Microsoft write-time optimization applied to Parquet files stored in OneLake. It applies a combination of special sorting, row group distribution, dictionary encoding, and compression that enables in-memory-like read performance for Fabric engines — SQL, Spark, and Power BI Direct Lake — without any query-side changes.

Direct Lake on SQL — May 2026

Direct Lake on SQL, published in May 2026, extends Direct Lake mode to Power BI semantic models backed directly by Fabric Data Warehouse tables. Previously, Direct Lake was available only for Lakehouse Delta tables. This means warehouse-backed semantic models now achieve near-import query speeds without import refresh schedules or DirectQuery latency.

Prerequisites for Direct Lake on SQL

V-Order is applied by default to all Fabric Data Warehouse tables — no additional configuration required. For best results, ensure your warehouse tables are well-compacted (run OPTIMIZE on high-ingestion tables) and that your semantic model does not use T-SQL RLS on the warehouse side (use semantic model RLS instead to avoid DirectQuery fallback).

Delta Analyzer — Evaluate Physical Storage Layout

Delta Analyzer is a tool that evaluates the physical storage layout of Delta tables. It surfaces file counts, size distribution, row group cardinality, and V-Order compliance — the inputs you need to decide whether to run OPTIMIZE, adjust partition strategy, or investigate Direct Lake fallback causes.

Semantic Link Labs — Direct Lake Guardrail Evaluation

Semantic Link Labs is an open-source Python library for Fabric. Its Direct Lake functions evaluate whether a semantic model’s tables meet the guardrails required to stay in Direct Lake mode, identify which tables are falling back to DirectQuery and why, and surface row count and column cardinality metrics against the current F-SKU limits.

# Semantic Link Labs: check Direct Lake guardrails
import sempy_labs as labs

labs.direct_lake.check_fallback_reason(
    dataset="Sales Analytics Model",
    workspace="Production"
)

Capacity Planning & Throttling

Fabric Warehouse runs on F-SKU capacity measured in Capacity Units (CUs). Understanding how CUs are consumed, how throttling works, and how to size and pause capacity correctly prevents performance degradation and cost overruns.

Throttling Stages

Pause and Resume Strategy

F-SKUs support pause and resume. A structured pause schedule reduces cost significantly for workloads that do not run 24/7. The key numbers: pausing nights and weekends on a business-hours schedule reduces runtime to roughly 260 hours per month versus 730 hours always-on — a reduction of about 64%.

For detailed pause architecture and reserved vs PAYG cost comparison, see Microsoft Fabric Capacity Optimization and Microsoft Fabric Pricing Calculator.

Power BI Integration Optimization

Power BI semantic models sitting on top of Fabric Warehouse have three connection options — Import, DirectQuery, and Direct Lake on SQL (May 2026). The right choice depends on data freshness requirements, dataset size, and concurrency.

DAX Optimization for Warehouse-Backed Models

When building semantic models on Fabric Warehouse tables, write DAX that avoids full table scans and row-by-row iteration. Variables eliminate repeated expression evaluation. CALCULATE with explicit filter arguments outperforms implicit context modification in complex models.

-- Inefficient: iterates every row
Revenue YTD = SUMX(FILTER(fact_sales, fact_sales[sale_year] = YEAR(TODAY())), fact_sales[amount])

-- Efficient: uses columnar aggregation with time intelligence
Revenue YTD = CALCULATE(SUM(fact_sales[amount]), DATESYTD(dim_date[date]))

Aggregation Tables for High Concurrency

For dashboards with many concurrent users, build aggregation tables in the Warehouse at daily, weekly, or monthly grain. Connect Power BI to the aggregation table as the primary source and the detail table for drill-through. This significantly reduces CU consumption per query because aggregation queries hit the smaller, pre-computed table.

Monitoring & Cost Optimization

Two tools form the foundation of Fabric Warehouse observability: the Microsoft Fabric Capacity Metrics app for capacity-level visibility, and query history in the Fabric portal for warehouse-level query analysis.

Fabric Capacity Metrics App

The Capacity Metrics app shows CU consumption over time, throttling events, and carryforward balance. Use it to identify which workloads consume the most CUs, when throttling occurred, and whether your F-SKU size is correctly matched to your workload pattern.

• Track utilization trend by day and hour to identify peak windows
• Monitor throttling frequency — more than occasional throttling signals F-SKU undersizing or a workload scheduling problem
• Analyse carryforward balance — a consistently negative balance means your workload consistently exceeds your base CU rate

Query History Analysis

In the Fabric portal, open your Warehouse item and navigate to Query activity. This shows execution duration, CU consumption, and query text for all recent queries. Sort by duration or CU cost to find the queries most worth optimizing.

-- In Fabric Warehouse: query execution history via DMV
SELECT TOP 20
  command,
  status,
  submit_time,
  end_compile_time,
  end_time,
  total_elapsed_time_ms
FROM sys.dm_pdw_exec_requests
WHERE status = 'Completed'
ORDER BY total_elapsed_time_ms DESC;

Common Error Fixes

FAQ — Microsoft Fabric data warehouse optimization

Official Resources & Further Reading