Stored procedures in Python in Snowflake - Commands & Configuration

Introduction

Sometimes you want to run a set of instructions inside your database to automate tasks. Stored procedures let you do this. Using Python for stored procedures in Snowflake helps you write these instructions in a simple, familiar language.

When you want to automate data transformations inside Snowflake without moving data out.

When you need to run multiple SQL commands together with logic like loops or conditions.

When you want to reuse a set of database operations easily by calling a single procedure.

When you want to keep your business logic close to your data for faster execution.

When you want to use Python's capabilities to process data inside Snowflake.

Commands

This command creates a stored procedure named greet_user that takes a name and returns a greeting message. It uses Python 3.8 and defines a function greet as the handler.

Terminal

CREATE OR REPLACE PROCEDURE greet_user(name STRING)
RETURNS STRING
LANGUAGE PYTHON
RUNTIME_VERSION = '3.8'
HANDLER = 'greet'
AS
$$
def greet(session, name):
    return f"Hello, {name}! Welcome to Snowflake."
$$;

Expected Output

Statement executed successfully.

→

LANGUAGE PYTHON - Specifies that the procedure is written in Python.

→

RUNTIME_VERSION = '3.8' - Sets the Python version for the procedure.

→

HANDLER = 'greet' - Defines the Python function to run when the procedure is called.

This command runs the stored procedure greet_user with the input 'Alice' to get a greeting message.

Terminal

CALL greet_user('Alice');

Expected Output

+------------------------------------------+

| GREET_USER                               |

|------------------------------------------|

| Hello, Alice! Welcome to Snowflake.      |

+------------------------------------------+

This command lists the stored procedure greet_user to verify it exists in the database.

Terminal

SHOW PROCEDURES LIKE 'greet_user';

Expected Output

created_on                      name        argument_signature  return_type  language  owner  comment

------------------------------  ----------  ------------------  -----------  --------  -----  -------

2024-06-01 12:00:00.000 +0000  greet_user  (name STRING)       STRING       PYTHON    SYSADMIN

Key Concept

If you remember nothing else from this pattern, remember: Stored procedures let you run Python code inside Snowflake to automate and reuse database tasks.

Common Mistakes

Forgetting to specify the HANDLER function name in the CREATE PROCEDURE statement.

Snowflake won't know which Python function to run, causing an error.

Always include HANDLER = 'function_name' matching your Python function.

Calling the procedure without parentheses or missing input parameters.

The call will fail because Snowflake expects the correct syntax and arguments.

Use CALL procedure_name(arguments); with parentheses and required inputs.

Using Python features not supported by Snowflake's Python runtime version.

The procedure may fail or behave unexpectedly if unsupported features are used.

Use Python 3.8 compatible code and check Snowflake documentation for supported features.

Summary

Create a Python stored procedure in Snowflake using CREATE OR REPLACE PROCEDURE with LANGUAGE PYTHON.

Call the procedure with CALL procedure_name(arguments); to run your Python code inside the database.

Verify the procedure exists using SHOW PROCEDURES LIKE 'procedure_name'; to confirm deployment.

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