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?"