Views and Materialized Views

Analogy categoryAnalogies

A standard view in Snowflake is a saved SQL query that has no storage of its own: every time you SELECT from a view, Snowflake re-executes the underlying query against the current state of its source tables, so the view always reflects live data with zero storage cost, at the price of re-doing that computation on every read. Views are the primary way teams simplify complex joins or business logic behind a single, reusable name, letting analysts write SELECT * FROM monthly_revenue instead of re-typing a ten-table join every time.

🏏

Cricket analogy: Like a live scoreboard app that recalculates a player's strike rate fresh every time you glance at it, rather than storing a fixed number — it's always current, but it does the arithmetic again on every look.

Materialized Views: Trading Storage for Speed

A materialized view, by contrast, physically stores its query results as actual data and Snowflake automatically keeps that stored result in sync with the underlying base table using a background service, so reads are fast because they scan pre-computed data rather than re-executing the full query. This makes materialized views ideal for expensive aggregations over a large, relatively slow-changing base table that many queries hit repeatedly, but they come with storage costs for the materialized data plus ongoing background compute cost every time the base table changes, and they carry restrictions such as not supporting joins across multiple tables or non-deterministic functions.

🏏

Cricket analogy: Like a printed match program pre-calculated before the game with each player's season stats baked in — fast to glance at, but someone has to reprint it whenever new stats come in, and it can't include live in-game data the way a scoreboard can.

Secure Views

A secure view hides its underlying query definition and query plan details from users who only have SELECT privilege on the view, and it also disables certain query optimizations like predicate pushdown reordering that could otherwise let a clever user infer protected data through query timing or the optimizer's execution plan. Secure views are the standard way to expose a filtered or masked subset of a sensitive table to a broader audience, such as sharing revenue data with a partner via Snowflake Secure Data Sharing without ever revealing the exact join logic or row-level security predicates behind it.

🏏

Cricket analogy: Like a broadcaster showing viewers only the final scoreline and highlights while keeping the production team's internal camera-switching script hidden — the audience sees the polished result, not how the sausage gets made.

sql

-- Standard view for reuse
CREATE OR REPLACE VIEW sales.monthly_revenue AS
SELECT
    DATE_TRUNC('month', order_date) AS month,
    SUM(total_amount)               AS revenue
FROM sales.orders
GROUP BY 1;

-- Materialized view for a hot aggregation path
CREATE OR REPLACE MATERIALIZED VIEW sales.daily_revenue_mv AS
SELECT
    order_date,
    SUM(total_amount) AS revenue,
    COUNT(*)          AS order_count
FROM sales.orders
GROUP BY order_date;

-- Secure view masking sensitive detail for partners
CREATE OR REPLACE SECURE VIEW sales.partner_revenue_share AS
SELECT month, revenue
FROM sales.monthly_revenue
WHERE revenue IS NOT NULL;

Materialized views cannot reference more than one base table, cannot use non-deterministic functions like CURRENT_TIMESTAMP(), and cannot include most window functions or outer joins. If your aggregation needs any of these, use a standard view, a scheduled task that populates a regular table, or Dynamic Tables instead.

Standard views store only the query definition and re-execute it live on every SELECT, with zero storage cost.
Materialized views physically store results and Snowflake keeps them automatically in sync with base tables.
Materialized views trade storage and background maintenance compute for much faster reads on hot aggregations.
Materialized views cannot join multiple tables or use non-deterministic functions or most window functions.
Secure views hide the underlying query definition and disable certain optimizer information leaks.
Secure Data Sharing typically relies on secure views to expose filtered data without revealing internal logic.
Choose standard views for reusable live logic, materialized views for expensive repeated aggregations.

Practice what you learned

1. What happens when you SELECT from a standard (non-materialized) view in Snowflake?

2. What is the main tradeoff of using a materialized view instead of a standard view?

3. Which of these is a restriction on Snowflake materialized views?

4. What does a secure view specifically hide from a user who only has SELECT privilege on it?

5. Which Snowflake feature is commonly used alongside secure views to share filtered data with external partners?

Was this page helpful?

Querying with Snowflake SQL

Related Study Notes in Programming

Browse all study notes

Programming

Apache Spark Study Notes

Programming · 30 topics

Programming

Apache Flink Study Notes

Programming · 30 topics

Programming

Hadoop Study Notes

Programming · 30 topics

Programming

Apache Airflow Study Notes

Programming · 30 topics

Programming

dbt (Data Build Tool) Study Notes

Programming · 30 topics

Programming

PowerShell Study Notes

Programming · 30 topics

Topics covered

#Programming#SnowflakeStudyNotes#ViewsAndMaterializedViews#Views#Materialized#Trading#Storage#StudyNotes#SkillVeris#ExamPrep

Views and Materialized Views