Bird
Raised Fist0
Snowflakecloud~5 mins

Views and materialized views in Snowflake - Cheat Sheet & Quick Revision

Choose your learning style10 modes available
LearnWhyDeepVisualTryChallengeProjectRecallTime

Start learning this pattern below

Jump into concepts and practice - no test required

or
Recommended
Test this pattern10 questions across easy, medium, and hard to know if this pattern is strong
Recall & Review
beginner
What is a view in Snowflake?
A view is a saved SQL query that acts like a virtual table. It does not store data itself but shows data from underlying tables when queried.
Click to reveal answer
beginner
How does a materialized view differ from a regular view?
A materialized view stores the query result physically, so it can return data faster. Snowflake automatically refreshes it when the base tables change.
Click to reveal answer
intermediate
When should you use a materialized view instead of a regular view?
Use a materialized view when you want faster query performance on complex or large data sets and can accept some delay in data freshness.
Click to reveal answer
beginner
What happens when you query a regular view in Snowflake?
Snowflake runs the view's underlying SQL query on the current data each time you query the view, so results are always up-to-date.
Click to reveal answer
intermediate
How do you refresh a materialized view in Snowflake?
Materialized views are automatically refreshed by Snowflake based on changes to the base tables. You can also refresh them manually using the ALTER MATERIALIZED VIEW ... REFRESH command.
Click to reveal answer
What does a regular view in Snowflake store?
ANothing, it is just a name
BThe SQL query only, no data
CBoth query and data
DThe query result data physically
Which of the following is true about materialized views?
AThey always show real-time data
BThey cannot be refreshed
CThey store query results physically
DThey are slower than regular views
How do you update data in a materialized view?
AIt updates automatically with no action
BBy dropping and recreating the view
CBy running REFRESH MATERIALIZED VIEW
DBy updating the underlying tables only
Which scenario is best for using a materialized view?
AFor complex queries needing faster results
BFor simple queries on small tables
CWhen data freshness is critical
DWhen you want to save storage space
What happens when you query a regular view?
AIt requires manual refresh
BIt returns stored data instantly
CIt returns cached data only
DIt runs the underlying query on current data
Explain the difference between a view and a materialized view in Snowflake.
Think about how data is stored and updated.
You got /4 concepts.
    Describe when and why you would choose to use a materialized view over a regular view.
    Consider trade-offs between speed and data freshness.
    You got /4 concepts.

      Practice

      (1/5)
      1. What is the main difference between a view and a materialized view in Snowflake?
      easy
      A. A view does not store data, while a materialized view stores query results.
      B. A view stores data permanently, but a materialized view does not.
      C. A materialized view updates automatically with every query, but a view does not.
      D. A view can only be used once, while a materialized view can be reused multiple times.

      Solution

      1. Step 1: Understand what a view does

        A view saves a query for reuse but does not store the actual data; it runs the query each time.

      2. Step 2: Understand what a materialized view does

        A materialized view stores the results of the query physically, so it can return data faster without rerunning the query.

      3. Final Answer:

        A view does not store data, while a materialized view stores query results. -> Option A

      4. Quick Check:

        View = no stored data, Materialized view = stored data [OK]
      Hint: Remember: views save queries, materialized views save data [OK]
      Common Mistakes:
      • Thinking views store data permanently
      • Believing materialized views update instantly with every query
      • Confusing reuse capability between views and materialized views
      2. Which of the following is the correct syntax to create a materialized view in Snowflake?
      easy
      A. CREATE VIEW my_view MATERIALIZED AS SELECT * FROM my_table;
      B. CREATE MATERIALIZED VIEW my_view AS SELECT * FROM my_table;
      C. CREATE MATERIALIZED my_view VIEW AS SELECT * FROM my_table;
      D. CREATE VIEW MATERIALIZED my_view AS SELECT * FROM my_table;

      Solution

      1. Step 1: Recall Snowflake syntax for materialized views

        The correct syntax starts with CREATE MATERIALIZED VIEW followed by the view name and query.

      2. Step 2: Check each option's order and keywords

        Only CREATE MATERIALIZED VIEW my_view AS SELECT * FROM my_table; uses the correct order and keywords: CREATE MATERIALIZED VIEW my_view AS SELECT * FROM my_table;

      3. Final Answer:

        CREATE MATERIALIZED VIEW my_view AS SELECT * FROM my_table; -> Option B

      4. Quick Check:

        Correct syntax = CREATE MATERIALIZED VIEW [OK]
      Hint: Materialized view syntax starts with CREATE MATERIALIZED VIEW [OK]
      Common Mistakes:
      • Mixing order of keywords
      • Placing MATERIALIZED after VIEW
      • Using incorrect keyword sequences
      3. Given the following Snowflake SQL code:
      CREATE TABLE sales (id INT, amount FLOAT);
      INSERT INTO sales VALUES (1, 100.0), (2, 200.0);
      CREATE MATERIALIZED VIEW sales_mv AS SELECT SUM(amount) AS total FROM sales;
      INSERT INTO sales VALUES (3, 300.0);
      SELECT * FROM sales_mv;

      What will the SELECT query return?
      medium
      A. total = 600.0
      B. total = 300.0 (only new rows)
      C. total = 300.0 plus previous sums
      D. total = 300.0

      Solution

      1. Step 1: Understand materialized view refresh behavior

        Materialized views in Snowflake do not automatically update after data changes; they show data as of last refresh.

      2. Step 2: Analyze the timing of inserts and query

        The materialized view was created after inserting two rows (100.0 + 200.0 = 300.0). The third row (300.0) was inserted after the view creation but before the select.

      3. Final Answer:

        total = 300.0 -> Option D

      4. Quick Check:

        Materialized view shows data at last refresh, not latest inserts [OK]
      Hint: Materialized views show data as of last refresh, not latest inserts [OK]
      Common Mistakes:
      • Assuming materialized views auto-update instantly
      • Adding new rows to materialized view results without refresh
      • Confusing materialized views with normal views
      4. You created a materialized view in Snowflake but it returns outdated data after table updates. What is the best way to fix this?
      medium
      A. Use a normal view instead of a materialized view to get fresh data.
      B. Drop and recreate the materialized view every time data changes.
      C. Manually refresh the materialized view using ALTER MATERIALIZED VIEW ... REFRESH.
      D. Restart the Snowflake warehouse to update the materialized view.

      Solution

      1. Step 1: Identify how to update materialized views

        Materialized views do not update automatically; they require manual refresh to show latest data.

      2. Step 2: Choose the correct refresh method

        Snowflake supports manual refresh with ALTER MATERIALIZED VIEW ... REFRESH to update the stored data.

      3. Final Answer:

        Manually refresh the materialized view using ALTER MATERIALIZED VIEW ... REFRESH. -> Option C

      4. Quick Check:

        Manual refresh updates materialized view data [OK]
      Hint: Use ALTER MATERIALIZED VIEW ... REFRESH to update data [OK]
      Common Mistakes:
      • Dropping and recreating instead of refreshing
      • Restarting warehouse has no effect on view data
      • Confusing materialized views with normal views for freshness
      5. You want to speed up queries that aggregate large sales data but also need the most current totals. Which approach best balances speed and freshness using Snowflake views?
      hard
      A. Use a materialized view and schedule frequent refreshes to keep data updated.
      B. Use a normal view only, since it always shows fresh data but may be slower.
      C. Create a materialized view and never refresh it to save compute costs.
      D. Use both a normal view and a materialized view simultaneously in the same query.

      Solution

      1. Step 1: Understand trade-offs between views and materialized views

        Normal views provide fresh data but can be slow on large data; materialized views are fast but need refresh to update.

      2. Step 2: Find a balanced solution

        Scheduling frequent refreshes of materialized views keeps data reasonably fresh while improving query speed.

      3. Final Answer:

        Use a materialized view and schedule frequent refreshes to keep data updated. -> Option A

      4. Quick Check:

        Materialized view + refresh = speed + freshness balance [OK]
      Hint: Schedule refreshes on materialized views for fresh and fast data [OK]
      Common Mistakes:
      • Not refreshing materialized views and expecting fresh data
      • Using only normal views and ignoring performance
      • Trying to combine views in one query without refresh strategy