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.