Snowflake Materialized Views: Performance Optimization Guide

Materialized Views (MVs) are a Snowflake performance optimization feature that physically stores the result of a SELECT statement and automatically refreshes it when the base table changes. By precomputing the results of frequently executed aggregation or filtering queries, MVs dramatically reduce query response times. They are available on Enterprise Edition or higher.

How Materialized Views Work

Unlike a regular view, a Materialized View physically stores the SELECT result as micro-partitions. When DML operations occur on the base table, Snowflake's background service (Serverless) automatically refreshes the MV's contents.

CREATE MATERIALIZED VIEW Syntax

-- Create an MV that aggregates sales by day and region
CREATE OR REPLACE MATERIALIZED VIEW analytics.daily_sales_mv
AS
SELECT
  order_date,
  region,
  COUNT(*)        AS order_count,
  SUM(amount)     AS total_amount,
  AVG(amount)     AS avg_amount
FROM analytics.orders
WHERE order_date >= '2025-01-01'
GROUP BY order_date, region;

-- Inspect the MV
SHOW MATERIALIZED VIEWS IN SCHEMA analytics;

-- Query the MV (access it like a regular table)
SELECT *
FROM analytics.daily_sales_mv
WHERE region = 'APAC'
  AND order_date >= '2026-03-01'
ORDER BY order_date;

Automatic Query Rewrite

When a query against the base table matches an MV's definition, Snowflake's optimizer automatically rewrites the query to reference the MV. Users do not need to reference the MV explicitly.

-- The optimizer automatically routes this query to daily_sales_mv
SELECT region, SUM(amount) AS total
FROM analytics.orders
WHERE order_date >= '2026-03-01'
GROUP BY region;

-- You can see MATERIALIZED_VIEW_MATCH in the Query Profile steps

Materialized View Restrictions

Automatic Maintenance and Cost Management

MV refreshes run automatically on Serverless compute and do not consume your user Warehouse resources. Refresh frequency tracks the rate of DML operations on the base table.

-- Inspect refresh history (last 7 days)
SELECT *
FROM TABLE(INFORMATION_SCHEMA.MATERIALIZED_VIEW_REFRESH_HISTORY(
  DATE_RANGE_START => DATEADD(DAY, -7, CURRENT_DATE()),
  DATE_RANGE_END => CURRENT_DATE()
))
ORDER BY start_time DESC;

-- Long-term cost analysis via the Account Usage view
SELECT
  table_name,
  materialized_view_name,
  SUM(credits_used) AS total_credits
FROM SNOWFLAKE.ACCOUNT_USAGE.MATERIALIZED_VIEW_REFRESH_HISTORY
WHERE start_time >= DATEADD(MONTH, -1, CURRENT_TIMESTAMP())
GROUP BY table_name, materialized_view_name
ORDER BY total_credits DESC;

Clustering on Materialized Views

You can also define Clustering Keys on the Materialized View itself. When the MV's result set is large, adding Clustering Keys further improves partition pruning accuracy for queries against the MV.

-- Add Clustering Keys to the MV
ALTER MATERIALIZED VIEW analytics.daily_sales_mv
  CLUSTER BY (order_date);

Comparison with Dynamic Tables

Use Cases

• Dashboard acceleration: Precompute results of aggregation queries that scan billions of rows each time, cutting BI tool response from tens of seconds down to a few seconds.
• Filtering optimization: Store only the rows of a large table that match a specific predicate (WHERE clause) in the MV, reducing the amount of data scanned.
• Pre-flattening semi-structured data: Convert frequently accessed keys in VARIANT columns into relational columns in the MV to boost performance.

Best Practices

• Keep the number of MVs in check: Creating many MVs on the same base table forces all of them to refresh on every DML operation, driving up costs.
• Be cautious with high-churn tables: On tables that update in real time, maintenance costs can balloon.
• SECURE MATERIALIZED VIEW: Use the SECURE option when you want to hide the MV's definition (SQL) in data sharing scenarios.
• Drop MVs you no longer need: Promptly remove unused MVs with DROP MATERIALIZED VIEW to stop their maintenance costs.

Check Your Understanding

Frequently Asked Questions