Bird
Raised Fist0
SnowflakeComparisonBeginner · 4 min read

View vs Materialized View in Snowflake: Key Differences and Usage

In Snowflake, a view is a saved query that runs fresh each time you access it, while a materialized view stores the query result physically for faster access. Materialized views improve performance by precomputing data but require storage and maintenance, unlike regular views which are always up-to-date but slower.
⚖️

Quick Comparison

This table summarizes the main differences between view and materialized view in Snowflake.

FactorViewMaterialized View
DefinitionA saved SQL query executed on demandA stored result of a query refreshed automatically
PerformanceSlower, runs query each timeFaster, reads precomputed data
StorageNo extra storage usedConsumes storage for data
Data FreshnessAlways current with base tablesMay lag until refreshed
MaintenanceNo maintenance neededRequires automatic or manual refresh
Use CaseFor simple, up-to-date queriesFor expensive queries needing speed
⚖️

Key Differences

A view in Snowflake is like a saved recipe that you follow every time you want to cook a dish. It does not store the dish itself but runs the instructions fresh each time you ask for it. This means the data is always current but can be slower if the query is complex.

A materialized view is like preparing the dish in advance and storing it in the fridge. When you want it, you get it quickly without cooking again. Snowflake stores the query result physically and refreshes it automatically or on demand. This speeds up access but uses storage and might not reflect the very latest data until refreshed.

Materialized views are best for queries that are costly to run repeatedly and where slight data delay is acceptable. Regular views are better when you need always up-to-date data and the query is not too heavy.

⚖️

Code Comparison

Creating a regular view in Snowflake runs the query fresh each time you select from it.

sql
CREATE OR REPLACE VIEW sales_summary AS
SELECT region, SUM(amount) AS total_sales
FROM sales
GROUP BY region;

-- Querying the view
SELECT * FROM sales_summary;
Output
Returns current total sales by region, computed on each query execution.
↔️

Materialized View Equivalent

Creating a materialized view stores the query result and refreshes it automatically for faster reads.

sql
CREATE OR REPLACE MATERIALIZED VIEW sales_summary_mv AS
SELECT region, SUM(amount) AS total_sales
FROM sales
GROUP BY region;

-- Querying the materialized view
SELECT * FROM sales_summary_mv;
Output
Returns precomputed total sales by region quickly, refreshed automatically.
🎯

When to Use Which

Choose a view when you need always up-to-date data and your queries are simple or run fast enough. Views are lightweight and require no extra storage.

Choose a materialized view when your queries are complex or slow, and you want faster read performance with acceptable data freshness delay. Materialized views use storage and need refresh management but improve speed significantly.

✅

Key Takeaways

Use views for always current data with minimal storage impact.
Use materialized views to speed up expensive queries by storing results.
Materialized views require storage and may have slight data delay.
Views run the query fresh every time, materialized views read stored data.
Choose based on query complexity, performance needs, and data freshness.