What are Snowflake Materialized Views and when should you use them over regular views?

A Materialized View (MV) in Snowflake is a pre-computed, physically stored snapshot of a SELECT query's result. Unlike a regular view that re-executes its SQL every time it is queried, an MV stores the result as a table that Snowflake automatically keeps fresh in the background as the base table changes. Reads from an MV are as fast as reads from a regular table.

Snowflake refreshes MVs incrementally — only the rows affected by changes to the base table are reprocessed, not the entire result set. This background refresh uses Snowflake-managed serverless compute and is billed separately from warehouse usage. The cost model is: storage for the materialized result + serverless compute credits for incremental maintenance.

Use an MV when: a complex aggregation is queried frequently, the base table changes predictably (not constantly), and query latency matters more than the added storage and maintenance cost. Skip MVs when: the base table is updated at very high frequency (refresh cost exceeds the query savings), the query joins multiple tables (MVs only support a single base table in Snowflake), or the query uses non-deterministic functions. Requires Enterprise edition or higher.

-- Create a Materialized View of daily sales aggregates
CREATE MATERIALIZED VIEW daily_sales_mv AS
  SELECT
    DATE_TRUNC('day', order_ts) AS sale_date,
    region,
    SUM(amount)                AS daily_revenue,
    COUNT(*)                   AS order_count
  FROM orders
  GROUP BY 1, 2;

-- Query the MV — reads pre-computed data, not the full orders table
SELECT * FROM daily_sales_mv
WHERE sale_date >= '2024-01-01';