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';

Invest now in Acorns!!! 🚀
Join Acorns and get your $5 bonus!

Acorns is a micro-investing app that automatically invests your "spare change" from daily purchases into diversified, expert-built portfolios of ETFs. It is designed for beginners, allowing you to start investing with as little as $5. The service automates saving and investing. Disclosure: I may receive a referral bonus.

Invest now!!! Get Free equity stock (US, UK only)!

Use Robinhood app to invest in stocks. It is safe and secure. Use the Referral link to claim your free stock when you sign up!.

The Robinhood app makes it easy to trade stocks, crypto and more.

Webull! Receive free stock by signing up using the link: Webull signup.