Bird
Raised Fist0
SnowflakeHow-ToBeginner · 3 min read

How to Create Materialized View in Snowflake: Syntax and Example

To create a materialized view in Snowflake, use the CREATE MATERIALIZED VIEW statement followed by the view name and a SELECT query. This stores the query result physically for faster access compared to regular views.
📐

Syntax

The basic syntax to create a materialized view in Snowflake is:

  • CREATE MATERIALIZED VIEW: Command to create the view.
  • view_name: Name you assign to the materialized view.
  • AS SELECT: Defines the query whose results are stored.

This materialized view stores the query result physically and updates automatically when base tables change.

sql
CREATE MATERIALIZED VIEW view_name AS
SELECT column1, column2
FROM table_name
WHERE condition;
💻

Example

This example creates a materialized view named mv_sales_summary that stores total sales per product from the sales table.

sql
CREATE MATERIALIZED VIEW mv_sales_summary AS
SELECT product_id, SUM(amount) AS total_sales
FROM sales
GROUP BY product_id;
Output
Materialized view 'mv_sales_summary' created successfully.
⚠️

Common Pitfalls

Common mistakes when creating materialized views in Snowflake include:

  • Using non-deterministic functions (like CURRENT_TIMESTAMP) in the SELECT query, which is not allowed.
  • Creating materialized views on tables without clustering keys, which can reduce performance.
  • Expecting immediate updates; materialized views refresh asynchronously.

Always ensure your SELECT query is deterministic and efficient.

sql
/* Wrong: Using non-deterministic function */
CREATE MATERIALIZED VIEW mv_wrong AS
SELECT CURRENT_TIMESTAMP AS now;

/* Right: Use deterministic query */
CREATE MATERIALIZED VIEW mv_right AS
SELECT product_id, COUNT(*) FROM sales GROUP BY product_id;
📊

Quick Reference

ClauseDescription
CREATE MATERIALIZED VIEW view_name AS SELECT ...Creates a materialized view with the given query.
SELECT columns FROM tableDefines the data to store physically.
WHERE conditionFilters rows for the view.
GROUP BY columnsAggregates data if needed.
Materialized views refresh asynchronouslyUpdates happen automatically but not instantly.
✅

Key Takeaways

Use CREATE MATERIALIZED VIEW with a deterministic SELECT query to store query results physically.
Materialized views improve query speed by caching results but refresh asynchronously.
Avoid non-deterministic functions in the view's SELECT statement.
Choose appropriate clustering keys on base tables for better materialized view performance.
Materialized views automatically update when base tables change but may have slight delays.