Views and materialized views in Snowflake - Time & Space Complexity

Time Complexity: Views and materialized views

O(n) for views, O(1) for materialized views

Understanding Time Complexity

When using views and materialized views in Snowflake, it is important to understand how the time to get results changes as data grows.

We want to know how the number of operations grows when querying these views.

Scenario Under Consideration

Analyze the time complexity of querying a view and a materialized view.

-- Create a regular view
CREATE OR REPLACE VIEW sales_view AS
SELECT customer_id, SUM(amount) AS total
FROM sales
GROUP BY customer_id;

-- Create a materialized view
CREATE OR REPLACE MATERIALIZED VIEW sales_mat_view AS
SELECT customer_id, SUM(amount) AS total
FROM sales
GROUP BY customer_id;

-- Query the views
SELECT * FROM sales_view WHERE total > 1000;
SELECT * FROM sales_mat_view WHERE total > 1000;
    

This sequence creates a view and a materialized view, then queries each to get customers with totals over 1000.

Identify Repeating Operations

Look at what happens each time we query these views.

• Primary operation: For the regular view, the query runs the aggregation on the base table every time.
• Primary operation: For the materialized view, Snowflake reads precomputed results stored internally.
• How many times: The regular view runs the aggregation each query, so once per query.
• How many times: The materialized view reads stored data, so it avoids repeating the aggregation each query.

How Execution Grows With Input

As the sales table grows, the work to compute the regular view grows too.

Input Size (n rows in sales)	Approx. Operations per Query
10	Runs aggregation on 10 rows
100	Runs aggregation on 100 rows
1000	Runs aggregation on 1000 rows

For the materialized view, the query reads stored results, so the operations stay about the same regardless of input size.

Pattern observation: Regular view work grows with data size; materialized view work stays mostly constant.

Final Time Complexity

Time Complexity: O(n) for regular views, O(1) for materialized views

This means regular views take longer as data grows, while materialized views give faster queries by storing results.

Common Mistake

[X] Wrong: "A view stores data like a table, so querying it is always fast."

[OK] Correct: Regular views just save the query, not the data. They run the full query each time, so speed depends on data size.

Interview Connect

Understanding how views and materialized views affect query speed shows you know how to balance storage and compute in cloud databases.

Self-Check

"What if the materialized view needs to refresh frequently as data changes? How would that affect the time complexity of queries?"

Practice

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