Stored Procedures in Python (Snowpark)
When you write a Snowpark Python script on your laptop, your laptop is acting as the orchestrator. If you close your laptop, the pipeline stops.
To automate your pipelines and run them entirely inside Snowflake (without requiring external servers like Airflow or a running laptop), you must package your Python code into a Snowpark Stored Procedure.
Topics Covered
- Stored Procedures vs UDFs
- Registering Python Stored Procedures
- Caller's Rights vs Owner's Rights
- Executing Procedures inside Snowflake
Stored Procedures vs UDFs vs Tasks
Before writing code, you must understand when to use which feature:
- UDF (User Defined Function): Returns a value. Used inside a
SELECTquery. Cannot modify tables (NoINSERTorDELETE). - Stored Procedure: Orchestrates complex logic. Can create tables, truncate data, and issue multiple queries. Called independently using
CALL my_procedure(). - Task: The scheduler. You create a Task to automatically run your Stored Procedure at 3:00 AM every day.
Hands-On: Creating a Python Stored Procedure
Let's assume our Data Engineering team in Hyderabad has a python function that archives old log files. We want to deploy this function into Snowflake.
1. The Local Python Function
First, we write the function. The first argument of a Snowpark Stored Procedure must always be the Session object.
from snowflake.snowpark import Session
from snowflake.snowpark.functions import col
import datetime
# The function must accept the Session as its first parameter
def archive_old_logs(session: Session, table_name: str, days_to_keep: int) -> str:
try:
# Calculate the cutoff date
cutoff_date = datetime.datetime.now() - datetime.timedelta(days=days_to_keep)
# 1. Archive the old rows
df_old_logs = session.table(table_name).filter(col("LOG_DATE") < cutoff_date)
# We append to the archive table
archive_table = table_name + "_ARCHIVE"
df_old_logs.write.mode("append").save_as_table(archive_table)
# 2. Delete the old rows from the primary table
session.table(table_name).delete(col("LOG_DATE") < cutoff_date)
return f"Successfully archived logs older than {days_to_keep} days."
except Exception as e:
return f"Error occurred: {str(e)}"
2. Registering the Procedure in Snowflake
Right now, this function only exists on your laptop. To push it into Snowflake, we use session.sproc.register().
We must tell Snowflake:
- The name of the procedure.
- Where to store the uploaded Python code (an internal stage).
- The third-party packages required (e.g.,
snowflake-snowpark-python).
(You must have the CREATE PROCEDURE privilege in the current schema to do this).
# Create an internal stage to hold the Python code if it doesn't exist
session.sql("CREATE STAGE IF NOT EXISTS BEEMS_PROC_STAGE").collect()
# Register the procedure
session.sproc.register(
func=archive_old_logs,
name="ARCHIVE_LOGS_SPROC",
packages=["snowflake-snowpark-python"],
is_permanent=True,
stage_location="@BEEMS_PROC_STAGE",
replace=True,
# Execution rights: "caller" or "owner"
execute_as="caller"
)
print("Stored Procedure successfully deployed to Snowflake!")
Caller's Rights vs Owner's Rights
When registering the procedure, you must define the execute_as parameter. This determines the security context.
- Owner (
execute_as="owner"): The procedure runs with the privileges of the role that deployed it. This is useful if you want to allow a junior analyst to run the procedure, but you don't want to grant the analyst the raw permission to drop or modify the tables directly. - Caller (
execute_as="caller"): The procedure runs with the privileges of the role that calls it. If the caller does not have permission to delete rows from the log table, the procedure will fail. This is best for auditing and strict RBAC.
Executing the Procedure
Once deployed, anyone with permission can call this Python logic without needing a Python environment. They just run standard SQL!
(Run this in a Snowflake SQL worksheet)
-- Call the Python Stored Procedure directly from SQL
CALL ARCHIVE_LOGS_SPROC('SYSTEM_LOGS', 30);
You can also call it from another Python script or Airflow DAG:
# Calling it programmatically via Snowpark
result = session.call("ARCHIVE_LOGS_SPROC", "SYSTEM_LOGS", 30)
print(result)
Summary
- A Snowpark Stored Procedure allows you to deploy Python pipeline logic directly into Snowflake.
- The first argument of your Python function must always be the
Sessionobject. - Use
session.sproc.register()to deploy the code to an internal stage. - Choose between Caller's Rights and Owner's Rights depending on your security model.
- Once deployed, the Python code can be executed using the SQL
CALLcommand.