Stored procedures in Python in Snowflake - Step-by-Step Execution

Process Flow - Stored procedures in Python

Define Python Stored Procedure

↓

Deploy to Snowflake

↓

Call Stored Procedure

↓

Execute Python Code in Snowflake

↓

Return Result to Caller

↓

End

This flow shows how a Python stored procedure is defined, deployed, called, executed inside Snowflake, and returns a result.

Execution Sample

Snowflake

CREATE OR REPLACE PROCEDURE greet(name STRING)
  RETURNS STRING
  LANGUAGE PYTHON
  RUNTIME_VERSION = '3.8'
AS
$$
  def run():
    return f'Hello, {name}!'
$$;

This code creates a Python stored procedure in Snowflake that takes a name and returns a greeting string.

Process Table

Step	Action	Input	Execution Detail	Output
1	Define procedure	name=STRING	Procedure code stored in Snowflake	Procedure created
2	Call procedure	name='Alice'	Snowflake runs Python code with input 'Alice'	Executes return statement
3	Execute Python code	name='Alice'	return f'Hello, {name}!'	Hello, Alice!
4	Return result	Hello, Alice!	Result sent back to caller	Hello, Alice!
5	End	-	-	-

💡 Procedure execution ends after returning the greeting string.

Status Tracker

Variable	Start	After Call	Final
name	undefined	'Alice'	'Alice'
return value	undefined	undefined	'Hello, Alice!'

Key Moments - 3 Insights

Why do we specify LANGUAGE PYTHON and RUNTIME_VERSION in the procedure?

Because Snowflake needs to know the language and Python version to run the code correctly, as shown in execution_table step 1.

How does the input 'name' get passed into the Python code?

The input parameter 'name' is passed automatically by Snowflake when calling the procedure, as seen in step 2 and variable_tracker.

What happens if the Python code does not return a value?

The procedure would return NULL or cause an error; returning a value is required to send output back, as shown in step 3 and 4.

Visual Quiz - 3 Questions

Look at the execution_table, what is the output at step 3?

A 'Hello, Alice!'

B 'name=Alice'

C Procedure created

D Undefined

💡 Hint

Check the Output column in execution_table row with Step 3.

At which step does Snowflake run the Python code?

A Step 2

B Step 3

C Step 1

D Step 4

💡 Hint

Look at the Action and Execution Detail columns in execution_table.

If the procedure is called with name='Bob', what changes in variable_tracker?

A The return value becomes 'Hello, Alice!'

B No change in variables

C The 'name' variable changes to 'Bob' after call

D 'name' becomes undefined

💡 Hint

Refer to variable_tracker rows for 'name' variable values.

Concept Snapshot

Stored procedures in Snowflake using Python:
- Define with CREATE PROCEDURE, specify LANGUAGE PYTHON and RUNTIME_VERSION
- Input parameters passed automatically
- Python code runs inside Snowflake
- Return value sends output back
- Call procedure with CALL proc_name(args)

Full Transcript

This visual execution shows how to create and run a Python stored procedure in Snowflake. First, you define the procedure with the Python code and specify the language and runtime version. Then, when you call the procedure with an input like a name, Snowflake runs the Python code inside its environment. The Python code uses the input and returns a result string. This result is sent back to the caller. Variables like the input parameter and return value change during execution as shown. Key points include specifying language and version, passing inputs, and returning outputs properly.

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