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

Practice

1. What is the main purpose of using stored procedures in Python within Snowflake?

easy

A. To store files in Snowflake storage

B. To create user interfaces for Snowflake

C. To run Python code inside Snowflake and automate database logic

D. To replace SQL queries with Python scripts outside Snowflake

Solution

1. Step 1: Understand stored procedures in Snowflake

   Stored procedures allow running Python code inside Snowflake to automate tasks.

2. Step 2: Identify the main purpose

   The main purpose is to centralize and automate database logic using Python.

3. Final Answer:

   To run Python code inside Snowflake and automate database logic -> Option C

4. Quick Check:

   Stored procedures = automate database logic [OK]

Hint: Stored procedures run Python inside Snowflake to automate tasks [OK]

Common Mistakes:

• Thinking stored procedures create user interfaces
• Confusing stored procedures with external Python scripts
• Assuming stored procedures store files

2. Which of the following is the correct way to define a Python stored procedure in Snowflake?

easy

A. CREATE PROCEDURE my_proc() RETURNS STRING LANGUAGE PYTHON AS 'def main(): return "Hello"';

B. CREATE FUNCTION my_proc() RETURNS STRING AS $$ def main(): return 'Hello' $$;

C. CREATE PROCEDURE my_proc() RETURNS STRING LANGUAGE SQL AS $$ SELECT 'Hello'; $$;

D. CREATE PROCEDURE my_proc() RETURNS STRING LANGUAGE PYTHON AS $$ def main(): return 'Hello' $$;

Solution

1. Step 1: Identify correct syntax for Python stored procedure

   Snowflake requires CREATE PROCEDURE with LANGUAGE PYTHON and the code inside $$ delimiters.

2. Step 2: Check each option

   CREATE PROCEDURE my_proc() RETURNS STRING LANGUAGE PYTHON AS $$ def main(): return 'Hello' $$; uses CREATE PROCEDURE, LANGUAGE PYTHON, and $$ with def main(): which is correct.

3. Final Answer:

   CREATE PROCEDURE my_proc() RETURNS STRING LANGUAGE PYTHON AS $$ def main(): return 'Hello' $$; -> Option D

4. Quick Check:

   Correct syntax = CREATE PROCEDURE my_proc() RETURNS STRING LANGUAGE PYTHON AS $$ def main(): return 'Hello' $$; [OK]

Hint: Use CREATE PROCEDURE with LANGUAGE PYTHON and $$ for code [OK]

Common Mistakes:

• Using CREATE FUNCTION instead of PROCEDURE
• Using LANGUAGE SQL for Python code
• Not using $$ delimiters for Python code

3. Given this stored procedure code in Snowflake Python:

CREATE PROCEDURE add_numbers(a INT, b INT) RETURNS INT LANGUAGE PYTHON AS $$
def main(session, a, b):
    return a + b
$$;

What will be the result of calling CALL add_numbers(3, 4);?

medium

A. 34

B. 7

C. Error: missing session parameter

D. NULL

Solution

1. Step 1: Understand the procedure parameters and return

   The procedure takes two integers a and b, and returns their sum.

2. Step 2: Analyze the call with 3 and 4

   Calling add_numbers(3, 4) passes 3 and 4, so the return is 3 + 4 = 7.

3. Final Answer:

   7 -> Option B

4. Quick Check:

   3 + 4 = 7 [OK]

Hint: Stored procedure returns sum of inputs; 3+4=7 [OK]

Common Mistakes:

• Confusing concatenation with addition
• Forgetting the session parameter is implicit
• Expecting an error due to session parameter

4. What is wrong with this Snowflake Python stored procedure code?

CREATE PROCEDURE greet() RETURNS STRING LANGUAGE PYTHON AS $$
def main():
    return 'Hello, World!'
$$;

medium

A. The main function must include a session parameter

B. The RETURNS type must be INTEGER, not STRING

C. The procedure must use LANGUAGE SQL, not PYTHON

D. The return statement must be print(), not return

Solution

1. Step 1: Check the main function signature

   Snowflake Python stored procedures require main(session, ...) with session as first parameter.

2. Step 2: Identify the error

   The code defines main() without session parameter, causing an error.

3. Final Answer:

   The main function must include a session parameter -> Option A

4. Quick Check:

   Missing session parameter = The main function must include a session parameter [OK]

Hint: Always include session as first parameter in main() [OK]

Common Mistakes:

• Omitting the session parameter in main()
• Using wrong RETURNS type
• Confusing LANGUAGE SQL with PYTHON

5. You want to create a Python stored procedure in Snowflake that reads a table named users and returns the count of users with age over 30. Which approach is correct?

hard

A. Use the session object to execute a SQL query inside main(), then return the count

B. Write a Python loop over a local list of users without SQL

C. Use a SQL stored procedure instead of Python for this task

D. Return the count as a string without querying the database

Solution

1. Step 1: Understand how to access data in Python stored procedures

   Python stored procedures use the session object to run SQL queries inside Snowflake.

2. Step 2: Identify correct method to get user count

   Executing a SQL query via session to count users with age > 30 is correct.

3. Final Answer:

   Use the session object to execute a SQL query inside main(), then return the count -> Option A

4. Quick Check:

   Use session to query database = Use the session object to execute a SQL query inside main(), then return the count [OK]

Hint: Use session.execute() to run SQL inside Python procedure [OK]

Common Mistakes:

• Trying to loop over data without SQL query
• Ignoring session object for database access
• Returning count as string without querying