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.