Cost optimization strategies in Snowflake - Time & Space Complexity

Time Complexity: Cost optimization strategies

O(n)

Understanding Time Complexity

We want to understand how the cost of running Snowflake operations grows as we use more resources or run more queries.

How does the amount of work affect the cost and time spent?

Scenario Under Consideration

Analyze the time complexity of the following operation sequence.

-- Create a warehouse
CREATE WAREHOUSE my_wh WITH WAREHOUSE_SIZE = 'SMALL';

-- Run a query using the warehouse
SELECT * FROM large_table WHERE condition = 'value';

-- Suspend the warehouse to save cost
ALTER WAREHOUSE my_wh SUSPEND;

-- Resume warehouse when needed
ALTER WAREHOUSE my_wh RESUME;

This sequence shows creating a compute resource, running a query, and managing the warehouse state to optimize cost.

Identify Repeating Operations

Identify the API calls, resource provisioning, data transfers that repeat.

• Primary operation: Running queries on the warehouse.
• How many times: Each query run uses the warehouse resources once.
• Additional operations: Suspending and resuming the warehouse happen as needed to save cost.

How Execution Grows With Input

As the number of queries increases, the total compute time and cost grow roughly in direct proportion.

Input Size (n)	Approx. API Calls/Operations
10 queries	10 query executions + some suspend/resume calls
100 queries	100 query executions + some suspend/resume calls
1000 queries	1000 query executions + some suspend/resume calls

Pattern observation: The cost and operations grow linearly with the number of queries run.

Final Time Complexity

Time Complexity: O(n)

This means the total cost and operations increase directly in proportion to how many queries you run.

Common Mistake

[X] Wrong: "Running more queries won't increase cost if the warehouse is already running."

[OK] Correct: Each query uses compute resources, so more queries mean more compute time and higher cost, even if the warehouse stays running.

Interview Connect

Understanding how cost grows with usage helps you design efficient systems and explain trade-offs clearly in real work and interviews.

Self-Check

"What if we used auto-suspend and auto-resume features for the warehouse? How would the time complexity change?"