How to create and use Materialized Views in Azure Synapse Analytics

João Barros 10 de August de 2026 5 min read

This tutorial shows how to create and use Materialized Views in Azure Synapse Analytics to speed up analytical queries over large facts and reduce compute costs. I explain why the technique is useful, when to use Materialized Views and provide a practical example with steps to create, update and validate a Materialized View.

Prerequisites

Azure Synapse Analytics workspace with Dedicated SQL Pool available.
Permissions to create objects in a Dedicated SQL Pool database (CREATE TABLE, CREATE VIEW, CREATE MATERIALIZED VIEW).
Source data loaded into a fact table (e.g.: dbo.fact_sales) and dimensions (e.g.: dbo.dim_date, dbo.dim_product).
Basic knowledge of T-SQL.

Step 1: Why use Materialized Views in Azure Synapse Analytics

Materialized Views store the results of a materialized query on disk, avoiding full reprocessing on each query. They are useful when you have heavy aggregation queries that are executed frequently. In Dedicated SQL Pool they improve latency and reduce resource usage, but require maintenance (refresh) when data changes.

Step 2: Create the sample table

If you don't yet have sample tables, create a simple fact table and populate it with some records to test. Here is a minimal example to test the Materialized View.

CREATE TABLE dbo.fact_sales (
  sale_id BIGINT NOT NULL,
  product_id INT NOT NULL,
  sale_date DATE NOT NULL,
  quantity INT,
  amount DECIMAL(18,2)
);

INSERT INTO dbo.fact_sales (sale_id, product_id, sale_date, quantity, amount)
VALUES (1, 100, '2026-01-01', 2, 19.98),
       (2, 101, '2026-01-02', 1, 9.99),
       (3, 100, '2026-01-02', 3, 29.97);

Step 3: Create an aggregation Materialized View

Decide the aggregation you need frequently. Here we create a Materialized View with sales by product_id and month. In Dedicated SQL Pool the syntax is CREATE MATERIALIZED VIEW. Important: the query has restrictions (they do not support non-deterministic functions, etc.).

CREATE MATERIALIZED VIEW dbo.mv_sales_monthly
WITH (DISTRIBUTION = HASH(product_id), CLUSTERED COLUMNSTORE INDEX)
AS
SELECT
  product_id,
  DATEFROMPARTS(YEAR(sale_date), MONTH(sale_date), 1) AS month_start,
  SUM(quantity) AS total_qty,
  SUM(amount) AS total_amount
FROM dbo.fact_sales
GROUP BY product_id, DATEFROMPARTS(YEAR(sale_date), MONTH(sale_date), 1);

Step 4: Check properties and limitations

Confirm that the Materialized View was created and that it has a physically stored object. Query metadata to see distribution and indexes. Remember that Materialized Views in Dedicated SQL Pool are updated when DML operations occur, but you have options to control refreshes and maintenance if you perform bulk loads.

-- Verify existence
SELECT name, type_desc FROM sys.objects WHERE name = 'mv_sales_monthly';

-- See index details
SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID('dbo.mv_sales_monthly');

Step 5: Query the Materialized View (usage example)

Once created, queries that use it can be much faster. Simple example of a query that benefits from the Materialized View:

SELECT product_id, month_start, total_qty, total_amount
FROM dbo.mv_sales_monthly
WHERE month_start = '2026-01-01';

Step 6: Update data and force refresh

If you perform bulk loads into the base table with COPY/CTAS or using bulk steps, ensure the Materialized View reflects the new data. In Dedicated SQL Pool you can recreate the MV or use maintenance techniques. A common operation is DROP + CREATE when doing a mass reload; for incremental loads, ensure that DML operations trigger updates.

-- Example: insert new records
INSERT INTO dbo.fact_sales (sale_id, product_id, sale_date, quantity, amount)
VALUES (4, 100, '2026-01-15', 1, 9.99);

-- Depending on how you loaded, you may need to rebuild:
ALTER MATERIALIZED VIEW dbo.mv_sales_monthly REBUILD; -- if supported
-- If REBUILD is not available in your scenario, use DROP + CREATE

Verify the result

Query the Materialized View and compare with direct aggregation from the base table. Measure execution times to see the improvement and confirm that the values match (or that any difference corresponds to pending loads).

-- Compare results
SELECT product_id, month_start, total_qty, total_amount
FROM dbo.mv_sales_monthly
ORDER BY product_id, month_start;

-- Direct aggregation (slower)
SELECT product_id,
       DATEFROMPARTS(YEAR(sale_date), MONTH(sale_date), 1) AS month_start,
       SUM(quantity) AS total_qty,
       SUM(amount) AS total_amount
FROM dbo.fact_sales
GROUP BY product_id, DATEFROMPARTS(YEAR(sale_date), MONTH(sale_date), 1)
ORDER BY product_id, month_start;

Conclusion

Materialized Views in Azure Synapse Analytics are a powerful tool to speed up analytical queries in Dedicated SQL Pool, reducing latency and CPU costs when used correctly. Next steps: test with larger datasets, define refresh policies for batch loads and analyze distribution/indexes to optimize performance. Tip: always check expression limitations in the view definition and test behavior after mass loads — have you tried measuring the improvement with the same query scenario before/after?