Tasks for scheduling SQL in Snowflake - Time & Space Complexity

Time Complexity: Tasks for scheduling SQL

O(n)

Understanding Time Complexity

We want to understand how the time to run scheduled SQL tasks grows as we add more tasks or increase their frequency.

How does the number of scheduled tasks affect the total work Snowflake does?

Scenario Under Consideration

Analyze the time complexity of the following task scheduling operations.

CREATE OR REPLACE TASK task_1
  WAREHOUSE = my_wh
  SCHEDULE = 'USING CRON 0 * * * * UTC'
AS
  INSERT INTO table_a SELECT * FROM table_b;

CREATE OR REPLACE TASK task_2
  WAREHOUSE = my_wh
  SCHEDULE = 'USING CRON 30 * * * * UTC'
AS
  UPDATE table_c SET col = col + 1 WHERE condition = TRUE;

-- Enable tasks
ALTER TASK task_1 RESUME;
ALTER TASK task_2 RESUME;

This sequence creates two scheduled tasks that run SQL statements at different times and enables them.

Identify Repeating Operations

Look at what repeats when tasks run:

• Primary operation: Each scheduled task runs its SQL statement once per schedule.
• How many times: Number of runs depends on schedule frequency and number of tasks.

How Execution Grows With Input

As you add more tasks or increase how often they run, the total number of SQL executions grows.

Input Size (n tasks)	Approx. SQL Executions per Hour
10	10 (if each runs once per hour)
100	100
1000	1000

Pattern observation: The total executions grow roughly in direct proportion to the number of tasks.

Final Time Complexity

Time Complexity: O(n)

This means the total work grows linearly as you add more scheduled tasks.

Common Mistake

[X] Wrong: "Adding more tasks won't affect total execution time because they run independently."

[OK] Correct: Even if tasks run separately, the system must process each one, so total work adds up with more tasks.

Interview Connect

Understanding how scheduled tasks scale helps you design systems that run smoothly as they grow, a key skill in cloud infrastructure roles.

Self-Check

"What if we changed the schedule frequency to run tasks every minute instead of every hour? How would the time complexity change?"