Stored procedures in Python in Snowflake - Deep Dive

Overview - Stored procedures in Python

What is it?

Stored procedures in Python are blocks of code saved inside a Snowflake database that perform specific tasks when called. They allow you to write Python code that runs directly within Snowflake to manipulate data or control workflows. This means you can automate complex operations close to your data without moving it outside the database. Stored procedures help keep your data processing efficient and organized.

Why it matters

Without stored procedures, you would have to move data out of Snowflake to process it, which is slow and risky. Stored procedures let you run Python code right where the data lives, saving time and reducing errors. This makes data workflows faster, more reliable, and easier to maintain. It also helps teams keep logic centralized and secure inside the database.

Where it fits

Before learning stored procedures in Python, you should understand basic SQL and Python programming. Knowing how Snowflake stores and manages data is helpful. After this, you can explore advanced data pipelines, automation, and integrating Snowflake with other tools using Python stored procedures.

Mental Model

Core Idea

A stored procedure in Python is like a recipe saved inside your database that you can run anytime to prepare or change your data exactly how you want.

Think of it like...

Imagine a coffee machine with a saved button for your favorite drink. Pressing that button runs a fixed set of steps to make your coffee without you doing anything each time. Similarly, a stored procedure runs a set of Python instructions inside the database whenever you call it.

┌─────────────────────────────┐
│       Snowflake Database     │
│ ┌─────────────────────────┐ │
│ │ Stored Procedure (Python)│ │
│ │  - Python code block      │ │
│ │  - Runs inside database   │ │
│ └─────────────────────────┘ │
│                             │
│  Data Tables                │
└──────────────┬──────────────┘
               │
               ▼
       Call procedure to run
       Python code on data

Build-Up - 7 Steps

1

Foundation

What is a Stored Procedure

Concept: Introduce the basic idea of stored procedures as saved code blocks inside a database.

A stored procedure is a set of instructions saved inside a database that you can run whenever you want. It helps automate repetitive tasks like data updates or calculations. Instead of writing the same commands over and over, you save them once and call them by name.

Result

You understand that stored procedures are reusable code inside databases to automate tasks.

Knowing that stored procedures save and run code inside the database helps you see how they keep work organized and efficient.

2

Foundation

Python in Snowflake Stored Procedures

Concept: Explain that Snowflake supports writing stored procedures using Python language.

Snowflake allows you to write stored procedures using Python, not just SQL. This means you can use Python's programming features to work with your data inside Snowflake. You write Python code that Snowflake runs directly, combining Python's power with database speed.

Result

You realize stored procedures can be written in Python to manipulate data inside Snowflake.

Understanding Python support in Snowflake stored procedures opens up more flexible and powerful data processing options.

3

Intermediate

Creating a Python Stored Procedure

🤔Before reading on: do you think creating a Python stored procedure is similar to writing a Python function or different? Commit to your answer.

Concept: Learn how to write and save a Python stored procedure in Snowflake using the CREATE PROCEDURE command.

To create a Python stored procedure, you use the SQL command CREATE PROCEDURE with LANGUAGE PYTHON. Inside, you write your Python code as a string. For example: CREATE OR REPLACE PROCEDURE my_proc() RETURNS STRING LANGUAGE PYTHON AS $$ return 'Hello from Python stored procedure!' $$; This saves the procedure in Snowflake, ready to be called.

Result

You can create and save a Python stored procedure in Snowflake that returns a simple message.

Knowing the syntax to create Python stored procedures lets you start automating tasks inside Snowflake.

4

Intermediate

Calling and Using Stored Procedures

🤔Before reading on: do you think calling a stored procedure returns data like a query or just runs code? Commit to your answer.

Concept: Understand how to run stored procedures and handle their results.

You call a stored procedure using the CALL command, like CALL my_proc();. Stored procedures can return values or just perform actions. For example, a procedure can update tables or return a string. You can capture the return value in your application or SQL script.

Result

You can run stored procedures and get results or perform database changes.

Knowing how to call and use stored procedures helps you integrate them into workflows and applications.

5

Intermediate

Working with Data Inside Procedures

🤔Before reading on: do you think Python stored procedures can directly query and modify Snowflake tables? Commit to your answer.

Concept: Learn how Python stored procedures interact with Snowflake data using Snowflake's Python API.

Inside Python stored procedures, you use the Snowflake-provided 'snowflake.snowpark' API to run SQL commands and manipulate data. For example, you can create a session, run queries, and update tables all within Python code. This lets you combine Python logic with database operations.

Result

You can write Python code that reads and writes Snowflake tables inside stored procedures.

Understanding data access inside Python procedures unlocks powerful data processing close to the data.

6

Advanced

Error Handling and Transactions

🤔Before reading on: do you think errors inside stored procedures automatically rollback changes or not? Commit to your answer.

Concept: Explore how to handle errors and control transactions inside Python stored procedures.

Python stored procedures can use try-except blocks to catch errors and handle them gracefully. Snowflake treats each procedure call as a transaction, so if an error occurs and is not caught, changes are rolled back. You can also explicitly commit or rollback using Snowflake APIs inside the procedure.

Result

You can write robust stored procedures that manage errors and data consistency.

Knowing error and transaction control prevents data corruption and ensures reliable automation.

7

Expert

Performance and Security Considerations

🤔Before reading on: do you think running Python code inside Snowflake is slower than external scripts or faster? Commit to your answer.

Concept: Understand the tradeoffs in performance and security when using Python stored procedures in Snowflake.

Running Python inside Snowflake avoids data transfer delays, often making procedures faster than external scripts. However, complex Python logic can still be slower than pure SQL for some tasks. Security is improved because code runs inside Snowflake's controlled environment, reducing data exposure. Best practice is to keep procedures focused and secure, granting minimal permissions.

Result

You can optimize stored procedures for speed and safety in production.

Understanding performance and security tradeoffs helps you design efficient and safe data workflows.

Under the Hood

When you create a Python stored procedure in Snowflake, the Python code is stored inside the database metadata. When called, Snowflake launches a secure Python runtime environment inside its cloud infrastructure. The procedure code runs there, using Snowflake's Python APIs to interact with data. This runtime isolates the code for security and manages resources automatically. Results or changes are committed as part of the database transaction.

Why designed this way?

Snowflake designed Python stored procedures to combine Python's flexibility with the speed and security of running code inside the database. This avoids slow data movement and leverages Snowflake's scalable cloud platform. Alternatives like running Python outside the database were slower and less secure. Embedding Python inside Snowflake lets users write complex logic close to data with controlled access.

┌───────────────────────────────┐
│       Snowflake Cloud          │
│ ┌───────────────┐             │
│ │ Python Runtime│             │
│ │ Environment   │             │
│ │  - Runs proc  │             │
│ │  - Uses APIs  │             │
│ └───────┬───────┘             │
│         │                     │
│ ┌───────▼────────┐            │
│ │ Snowflake Data │            │
│ │ Storage & SQL  │            │
│ └────────────────┘            │
└───────────────────────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Do you think Python stored procedures in Snowflake can run any Python library without restrictions? Commit to yes or no.

Common Belief: Python stored procedures can use any Python library just like local Python scripts.

Reality: Snowflake supports only a limited set of Python libraries inside stored procedures for security and performance reasons.

Why it matters: Trying to use unsupported libraries causes errors and confusion, blocking procedure deployment.

Quick: Do you think stored procedures always improve performance compared to external scripts? Commit to yes or no.

Common Belief: Running Python code inside Snowflake stored procedures is always faster than running it outside the database.

Reality: While stored procedures avoid data transfer delays, complex Python logic can sometimes be slower than optimized SQL or external processing.

Why it matters: Assuming stored procedures are always faster can lead to poor design choices and slow workflows.

Quick: Do you think stored procedures can be called like regular SQL queries? Commit to yes or no.

Common Belief: You can use stored procedures anywhere you use SQL queries, like in SELECT statements.

Reality: Stored procedures are called with CALL statements and cannot be used directly inside SELECT queries.

Why it matters: Misusing stored procedures as queries causes syntax errors and confusion.

Quick: Do you think errors inside stored procedures always leave partial data changes? Commit to yes or no.

Common Belief: If a stored procedure fails halfway, some data changes remain applied.

Reality: Snowflake treats stored procedure calls as transactions; if an error occurs and is uncaught, all changes are rolled back.

Why it matters: Knowing this prevents unnecessary manual cleanup and helps write safer procedures.

Expert Zone

1

Python stored procedures run in a sandboxed environment with limited system access, which affects what code can do.

2

Using Snowflake's Snowpark API inside procedures allows chaining complex data transformations efficiently within Python.

3

Procedures can be versioned and managed like database objects, enabling controlled deployment and rollback.

When NOT to use

Avoid Python stored procedures for extremely high-performance, simple data operations better handled by pure SQL. For complex machine learning or heavy computation, use external Python environments integrated with Snowflake instead.

Production Patterns

In production, Python stored procedures are used for data validation, orchestration of multi-step workflows, and applying business logic close to data. Teams often combine them with Snowflake Tasks for scheduling and Snowflake Streams for change data capture.

Connections

Database Transactions

Python stored procedures run inside database transactions.

Understanding transactions helps grasp how stored procedures ensure data consistency and rollback on errors.

Cloud Function as a Service (FaaS)

Stored procedures are similar to cloud functions but run inside the database environment.

Knowing about FaaS clarifies how stored procedures provide serverless code execution close to data.

Software Engineering Modularization

Stored procedures modularize database logic like functions modularize code.

Seeing stored procedures as modular code blocks helps design maintainable and reusable data workflows.

Common Pitfalls

#1 Trying to use unsupported Python libraries inside stored procedures.

Wrong approach:

CREATE OR REPLACE PROCEDURE my_proc()
RETURNS STRING
LANGUAGE PYTHON
AS
$$
import pandas as pd
return 'Done'
$$;

Correct approach:

CREATE OR REPLACE PROCEDURE my_proc()
RETURNS STRING
LANGUAGE PYTHON
AS
$$
# Use only supported libraries or Snowpark API
return 'Done'
$$;

Root cause:

Misunderstanding Snowflake's restricted Python environment and available libraries.

#2 Calling stored procedures inside SELECT queries.

Wrong approach:

SELECT my_proc() FROM my_table;

Correct approach:

CALL my_proc();

Root cause:

Confusing stored procedures with user-defined functions that can be used in queries.

#3 Not handling errors inside stored procedures, causing unexpected rollbacks.

Wrong approach:

CREATE PROCEDURE my_proc()
LANGUAGE PYTHON
AS
$$
raise Exception('Error')
$$;

Correct approach:

CREATE PROCEDURE my_proc()
LANGUAGE PYTHON
AS
$$
try:
# code
except Exception as e:
# handle error
$$;

Root cause:

Ignoring error handling leads to procedure failures and transaction rollbacks.

Key Takeaways

Stored procedures in Python let you run reusable Python code directly inside Snowflake to automate data tasks.

They combine Python's flexibility with Snowflake's speed and security by running code close to the data.

Creating and calling stored procedures uses SQL commands but the logic is written in Python using Snowflake APIs.

Error handling and transaction control inside procedures ensure data consistency and reliable automation.

Understanding limitations and best practices helps you design efficient, secure, and maintainable data workflows.

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