Stored procedures in Python in Snowflake - Time & Space Complexity

Time Complexity: Stored procedures in Python

O(n)

Understanding Time Complexity

We want to understand how the time it takes to run a Python stored procedure in Snowflake changes as the input size grows.

Specifically, how does the number of operations inside the procedure grow with the data it processes?

Scenario Under Consideration

Analyze the time complexity of the following Python stored procedure in Snowflake.

CREATE OR REPLACE PROCEDURE process_data(input_array ARRAY)
  RETURNS STRING
  LANGUAGE PYTHON
  RUNTIME_VERSION = '3.8'
AS
$$
  result = []
  for item in input_array:
    # Simulate some processing
    result.append(item * 2)
  return str(result)
$$;

This procedure takes an array of items, processes each item by doubling it, and returns the results as a string.

Identify Repeating Operations

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

• Primary operation: Looping over each item in the input array and doubling it.
• How many times: Once for each item in the input array.

How Execution Grows With Input

The procedure processes each item one by one, so if the input doubles, the work doubles too.

Input Size (n)	Approx. Api Calls/Operations
10	10 operations (doubling each item)
100	100 operations
1000	1000 operations

Pattern observation: The number of operations grows directly with the input size.

Final Time Complexity

Time Complexity: O(n)

This means the time to run the procedure grows in a straight line with the number of items processed.

Common Mistake

[X] Wrong: "The procedure runs in constant time no matter the input size because it's just one call."

[OK] Correct: Even though it's one procedure call, the work inside depends on how many items it processes, so time grows with input size.

Interview Connect

Understanding how stored procedures scale with input size helps you design efficient data processing in the cloud, a key skill for real-world projects.

Self-Check

"What if the procedure called another procedure inside the loop for each item? How would the time complexity change?"