Why Snowflake separates compute from storage - Performance Analysis

Time Complexity: Why Snowflake separates compute from storage

O(n / c)

Understanding Time Complexity

We want to understand how Snowflake's design of separating compute from storage affects the time it takes to run queries.

Specifically, how does the number of compute resources and data size impact execution time?

Scenario Under Consideration

Analyze the time complexity of querying data in Snowflake with separate compute and storage.

-- Create a warehouse (compute)
CREATE WAREHOUSE my_wh WITH WAREHOUSE_SIZE = 'XSMALL';

-- Query data from a large table stored separately
SELECT * FROM big_table WHERE condition = 'value';

-- Scale warehouse size to increase compute power
ALTER WAREHOUSE my_wh SET WAREHOUSE_SIZE = 'LARGE';

-- Run the same query again
SELECT * FROM big_table WHERE condition = 'value';

This sequence shows how compute resources can be adjusted independently from storage to affect query time.

Identify Repeating Operations

Look at what happens repeatedly when running queries.

• Primary operation: Query execution using compute warehouse accessing stored data.
• How many times: Each query runs once, but compute resources can be scaled multiple times.

How Execution Grows With Input

As data size grows, the amount of data to scan grows too, increasing query time.

Input Size (n)	Approx. Compute Operations
10 GB	Small number of compute operations, fast query
100 GB	More compute operations, longer query time
1 TB	Much more compute operations, much longer query time

Increasing compute size can reduce time, but data scanned still grows with input size.

Final Time Complexity

Time Complexity: O(n / c)

This means query time grows with data size n, but dividing by compute power c reduces time.

Common Mistake

[X] Wrong: "Adding more compute always makes queries instant regardless of data size."

[OK] Correct: More compute helps, but scanning large data still takes time; compute can't make data size zero.

Interview Connect

Understanding how compute and storage separation affects query time shows you can balance resources for cost and speed, a key cloud skill.

Self-Check

"What if Snowflake did not separate compute from storage? How would that change the time complexity of queries?"