How to create a Materialized View in Warehouse in Microsoft Fabric

João Barros 24 de October de 2026 6 min read

This tutorial shows how to create a Materialized View in a Warehouse in Microsoft Fabric to speed up T-SQL queries over data in a Lakehouse. A Materialized View stores precomputed results, reduces latency and avoids repeating heavy transformations in every query. In scenarios with millions of rows (for example, 50–200M sales records), a Materialized View can reduce response times from seconds/minutes to tenths of a second, depending on Warehouse size.

Prerequisites

Account with access to a workspace in Microsoft Fabric and permissions to create a Warehouse (role with Owner or Contributor privileges in the workspace).
A Lakehouse with a Delta table already loaded (e.g.: sales_delta). Ideally, the table should have partition indexes by date or columns that facilitate filtering (e.g. sales_date, region).
Basic knowledge of T-SQL and the concept of Materialized View. Understanding compute costs (e.g.: Small/Medium/Large or vCores) helps choose the correct Warehouse.
Volume estimate: for example, 10M to 200M rows, with an average of 5–20 columns. This conditions the refresh frequency and the space used by the Materialized View.

Step 1: Create or open a Warehouse

The Materialized View will reside in a Warehouse. If you already have a Warehouse, open it; otherwise, create a new Warehouse in the workspace. Configure the size according to the expected load: for testing, a Small size or 2 vCores is sufficient; for production with concurrent reports, consider Medium/4 vCores or more. Remember hourly costs increase with size, so balance performance and cost. Also configure auto-pause if available, to reduce costs during idle periods.

Step 2: Verify the base table in the Lakehouse

Confirm the Delta table in the Lakehouse is accessible through the Warehouse. In the Warehouse, open a T-SQL query and run a simple query to validate the data and obtain cardinality estimates.

SELECT TOP 10 *
FROM lakehouse.your_lakehouse.sales_delta;

Also run an approximate count to assess the impact:

SELECT COUNT(*) AS total_rows
FROM lakehouse.your_lakehouse.sales_delta;

If it returns, for example, 125000000, know that we are in the 125M rows range — this influences the refresh and partitioning strategy.

Step 3: Choose the query to materialize

Think about which query you want to speed up: aggregations, joins or heavy transformations. Simplify the logic to the version that makes sense to materialize. Prioritize columns and periods most used by reports (e.g.: last 90 days). Concrete examples help: if reports show total sales by day and region for the last 90 days, materialize only that interval and those columns.

-- Example of the query we will materialize
SELECT
  sales_date,
  region,
  SUM(amount) AS total_amount,
  COUNT(*) AS transactions
FROM lakehouse.your_lakehouse.sales_delta
WHERE sales_date >= DATEADD(day, -90, CURRENT_DATE)
GROUP BY sales_date, region;

This choice reduces space and time: instead of storing all 20 columns of the original table, store only 4 aggregated columns for 90 days * number of regions (e.g. 90 * 10 = 900 records per period).

Step 4: Create the Materialized View in the Warehouse

In the Warehouse, run a CREATE MATERIALIZED VIEW with the chosen query. Give it a clear name and define the resulting columns. Ensure the query is deterministic and avoids volatile functions that prevent efficient materialization.

CREATE MATERIALIZED VIEW mv_sales_daily_region
AS
SELECT
  sales_date,
  region,
  SUM(amount) AS total_amount,
  COUNT(*) AS transactions
FROM lakehouse.your_lakehouse.sales_delta
WHERE sales_date >= DATEADD(day, -90, CURRENT_DATE)
GROUP BY sales_date, region;

After creation, validate the disk size and MV metadata (in a few minutes, depending on initial volume it may take seconds to minutes to populate).

Step 5: Schedule updates (Refresh) of the Materialized View

Decide whether the Materialized View will be updated manually or automatically. In the Fabric Warehouse, you can use REFRESH MATERIALIZED VIEW to force an update; for automation, create a schedule with Pipelines, Tasks or external Jobs. Common frequencies: every 15 minutes for near-real-time reports, hourly for operational dashboards, or daily for historical analyses.

-- Manual refresh
REFRESH MATERIALIZED VIEW mv_sales_daily_region;

-- If you want to automate with T-SQL inside the Warehouse (simplified example)
-- It can be integrated with a Pipeline or a scheduled Job in your environment.

Consider incremental refreshes, when supported, to reduce cost. Monitor refresh time — if it takes more than, for example, 5 minutes for an hourly refresh, review the strategy (partitions, filters, or scaling the Warehouse).

Step 6: Query the Materialized View to speed up reports

Replace heavy queries with the Materialized View. This reduces response time and compute cost. Instead of aggregating over 125M rows, you are reading 900–10k pre-aggregated rows, depending on the period and cardinality.

SELECT sales_date, region, total_amount
FROM mv_sales_daily_region
WHERE sales_date BETWEEN '2026-01-01' AND '2026-01-31'
ORDER BY sales_date;

This is especially useful for Power BI or other tools that run many ad-hoc queries: faster response times and less Warehouse load.

Verify the result

Compare execution times between the original query and the query against the Materialized View using EXPLAIN or by measuring duration. Also verify that aggregated values match after a REFRESH. Examples of checks:

-- Measure time and plan
EXPLAIN SELECT * FROM mv_sales_daily_region WHERE sales_date = '2026-01-15';

-- Compare totals
SELECT SUM(amount) FROM lakehouse.your_lakehouse.sales_delta WHERE sales_date = '2026-01-15';
SELECT total_amount FROM mv_sales_daily_region WHERE sales_date = '2026-01-15' AND region = 'North';

If there are differences, run REFRESH and revalidate. Also record cost and latency metrics over a few weeks to quantify gains (e.g.: 70–95% reduction in CPU time for aggregated queries).

Conclusion

Materialized Views in Warehouses in Microsoft Fabric are useful to speed up analytical queries and reduce repeated processing costs. Next steps: test refresh policies (frequency and incremental), automate refresh with Pipelines or Jobs and monitor cost/performance impact. Practical tip: try materializing only the most used columns and periods (e.g. last 30–90 days) to optimize space and speed; run A/B tests before applying to full production.