How to create materialized views in Lakehouse: step by step

This tutorial shows how to create materialized views in Lakehouse to speed up frequent analytical queries. Materialized views reduce I/O and improve performance by pre-aggregating or pre-filtering data, useful for reports and dashboards in Power BI or ad-hoc analyses.

Prerequisites

• Lakehouse instance with accessible Delta tables (for example in Microsoft Fabric).
• Permissions to create SQL objects and schedule jobs.
• SQL tool to run commands (SQL endpoint, Synapse or the Fabric interface).

Step 1: Choose the use case and the base query

Decide which query is costly and benefits from materialization: aggregation by day, top N, or heavy join. The base query should be deterministic and relatively stable. Example: daily aggregation of sales by product.

-- Consulta base: total de vendas por product_id por dia
SELECT
  product_id,
  CAST(sale_date AS DATE) AS sale_day,
  SUM(amount) AS total_sales,
  COUNT(*) AS sales_count
FROM sales_delta
GROUP BY product_id, CAST(sale_date AS DATE);

Step 2: Create a Delta table to store the materialization

A materialized view in many Lakehouse environments is implemented as a pre-computed table (Delta). Create a Delta table to hold the result and choose the appropriate partitioning (for example sale_day) for efficient queries.

CREATE TABLE IF NOT EXISTS mv_sales_by_day
USING DELTA
PARTITIONED BY (sale_day)
AS
SELECT
  product_id,
  CAST(sale_date AS DATE) AS sale_day,
  SUM(amount) AS total_sales,
  COUNT(*) AS sales_count
FROM sales_delta
GROUP BY product_id, CAST(sale_date AS DATE);

Step 3: Implement initial and incremental refresh (ETL/ELT)

The refresh can be full (truncate+insert) or incremental. For performance, do incremental using an updated_at column or a CDC table. Here we show a simple example based on updated_at: we update the days changed since the last run.

-- Exemplo: identificar dias alterados
WITH changed_days AS (
  SELECT DISTINCT CAST(sale_date AS DATE) AS sale_day
  FROM sales_delta
  WHERE updated_at >= DATEADD(day, -1, current_timestamp()) -- último dia
)

-- Recalcular apenas os dias alterados
MERGE INTO mv_sales_by_day AS target
USING (
  SELECT
    product_id,
    sale_day,
    SUM(amount) AS total_sales,
    COUNT(*) AS sales_count
  FROM sales_delta
  WHERE CAST(sale_date AS DATE) IN (SELECT sale_day FROM changed_days)
  GROUP BY product_id, CAST(sale_date AS DATE)
) AS src
ON target.product_id = src.product_id AND target.sale_day = src.sale_day
WHEN MATCHED THEN
  UPDATE SET total_sales = src.total_sales, sales_count = src.sales_count
WHEN NOT MATCHED
  THEN INSERT (product_id, sale_day, total_sales, sales_count) VALUES (src.product_id, src.sale_day, src.total_sales, src.sales_count);

Step 4: Schedule refresh and handle failures

Automate the refresh with a job scheduler (e.g., Azure Data Factory, Synapse pipelines, or the Fabric scheduler). Configure alerts for failures and retention of logs for auditing. Set frequency according to SLA: every minute, hourly or daily.

-- Pseudocódigo do job: executar script de MERGE acima
-- No scheduler, criar pipeline que executa query SQL no endpoint do Lakehouse
-- Definir política de retry e notificação em caso de erro

Step 5: Optimizations and maintenance

Monitor the size and fragmentation of the materialized Delta table. Run OPTIMIZE and ZORDER if available to reduce small files and speed up filters on frequent columns. Re-evaluate partitions and, if necessary, perform a full refresh periodically.

-- Exemplos comuns de manutenção
OPTIMIZE mv_sales_by_day ZORDER BY (product_id);
-- Se existir suporte, usar VACUUM com cuidado para remover ficheiros antigos
VACUUM mv_sales_by_day RETAIN 168 HOURS; -- não apagar historial recente

Verify the result

Confirm that the materialized table speeds up queries by comparing times before/after. Test a typical query and verify that the data matches the source and that the refresh job updates the changes.

-- Teste de desempenho e validade
EXPLAIN SELECT product_id, total_sales FROM mv_sales_by_day WHERE sale_day = '2026-10-01' ORDER BY total_sales DESC LIMIT 10;

-- Comparação simples de contagens
SELECT SUM(total_sales) FROM mv_sales_by_day WHERE sale_day = '2026-10-01';
SELECT SUM(amount) FROM sales_delta WHERE CAST(sale_date AS DATE) = '2026-10-01';

Conclusion

Creating materialized views in Lakehouse using a Delta table allows you to speed up queries and reduce I/O costs. Next steps: integrate with Power BI, implement CDC-based refresh for lower latency and automate alerts. Tip: start with simple aggregations and monitor cost/benefit before materializing everything.