How to Create a Materialized View in Snowflake

How do I create and manage a materialized view in Snowflake?

A Snowflake materialized view stores pre-computed query results, letting users query faster at the cost of extra storage and refresh overhead.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

What is a Snowflake materialized view?

A materialized view in Snowflake is a physical copy of a SELECT query’s result set. Snowflake keeps the copy automatically synchronized with source tables, so reads become much faster while writes incur extra maintenance cost.

Why choose a materialized view over a standard view?

Standard views run the SELECT each time, which can be slow on large joins. Materialized views trade extra storage and compute credits for sub-second reads, ideal for dashboards, KPIs, and API endpoints that hit the same query repeatedly.

How do I create a materialized view?

Use CREATE MATERIALIZED VIEW with an optimized SELECT. Include only columns and rows you need. Avoid non-deterministic functions and SELECT *. Keep the result small to limit maintenance cost.

Step-by-step example

1. Verify base tables are clustered on common join keys.
2. Grant USAGE & SELECT on tables to the role that will own the view.
3. Run CREATE MATERIALIZED VIEW (see syntax below).
4. Query the view like a table.

How do I query a materialized view?

Simply SELECT from it. No special syntax is required, and the optimizer may even rewrite queries to use the view automatically when it matches the pattern.

How are materialized views refreshed?

Snowflake refreshes incrementally after DML on source tables. Manual refresh is rarely needed, but ALTER MATERIALIZED VIEW ... RECLUSTER can force a rebuild if performance degrades.

What does it cost?

Storage cost equals the compressed size of the view. Compute credits are consumed during background refreshes. Narrow, highly selective views keep both costs low.

Best practices for materialized views

• Project only necessary columns
• Filter rows aggressively
• Cluster source tables on join/filter keys
• Monitor credit usage with ACCOUNT_USAGE.MATERIALIZED_VIEW_REFRESH_HISTORY
• Drop unused views promptly to stop ongoing charges

Why How to Create a Materialized View in Snowflake is important

How to Create a Materialized View in Snowflake Example Usage


-- Create a materialized view that caches customer LTV
CREATE MATERIALIZED VIEW IF NOT EXISTS analytics.customer_ltv_mv
CLUSTER BY (customer_id)
AS
SELECT
    c.id                AS customer_id,
    c.name              AS customer_name,
    SUM(o.total_amount) AS lifetime_value,
    COUNT(o.id)         AS order_count
FROM Customers c
JOIN Orders o ON o.customer_id = c.id
GROUP BY c.id, c.name;

How to Create a Materialized View in Snowflake Syntax


CREATE MATERIALIZED VIEW [IF NOT EXISTS] schema.view_name
    [CLUSTER BY (<expr1>, <expr2>, ...)]
    COMMENT = '<optional_comment>'
AS
SELECT
    c.id                AS customer_id,
    c.name              AS customer_name,
    SUM(o.total_amount) AS lifetime_value,
    COUNT(o.id)         AS order_count
FROM Customers c
JOIN Orders o  ON o.customer_id = c.id
GROUP BY c.id, c.name;

Common Mistakes

Creating views with SELECT * causes unnecessary storage and refresh cost. Fix by listing only required columns in the SELECT clause.
Including non-deterministic functions like CURRENT_TIMESTAMP prevents incremental refresh. Remove volatile functions or compute them at query time instead.