Stored procedures in Python in Snowflake - Mini Project: Build & Apply

Stored procedures in Python

📖 Scenario: You are working with Snowflake, a cloud data platform. You want to create a stored procedure using Python to automate a simple task inside your database.Stored procedures let you run code inside Snowflake to manage data or perform operations automatically.

🎯 Goal: Create a Python stored procedure in Snowflake that returns a greeting message including a given name.

📋 What You'll Learn

Create a stored procedure named greet_user that takes one string parameter user_name.

The procedure should return a greeting message: 'Hello, ' + user_name.

Use Python language for the stored procedure.

Call the stored procedure with a sample name to verify it works.

💡 Why This Matters

🌍 Real World

Stored procedures automate repetitive tasks inside the Snowflake database, such as data transformation or validation.

💼 Career

Knowing how to write stored procedures in Python for Snowflake is valuable for data engineers and cloud database administrators.

1

Create the stored procedure header

Write the SQL command to create a stored procedure named greet_user that takes one parameter user_name of type STRING. Use LANGUAGE PYTHON and RETURNS STRING. Do not write the procedure body yet.

Snowflake

-- Write the CREATE PROCEDURE statement header here
-- Your code here

Hint

Start with CREATE OR REPLACE PROCEDURE greet_user(user_name STRING). Specify RETURNS STRING and LANGUAGE PYTHON. Use $$ to mark the start and end of the procedure body.

2

Add the procedure body with a return statement

Inside the procedure body between $$, write Python code that returns the string 'Hello, ' + user_name. Use the return statement.

Snowflake

CREATE OR REPLACE PROCEDURE greet_user(user_name STRING)
RETURNS STRING
LANGUAGE PYTHON
AS
$$
# Write Python code to return 'Hello, ' + user_name
# Your code here
$$;

Hint

Inside the $$ block, write return 'Hello, ' + user_name to send back the greeting.

3

Call the stored procedure with a sample name

Write a SQL statement to call the stored procedure greet_user with the argument 'Alice'. Use CALL greet_user('Alice').

Snowflake

CREATE OR REPLACE PROCEDURE greet_user(user_name STRING)
RETURNS STRING
LANGUAGE PYTHON
AS
$$
return 'Hello, ' + user_name
$$;

-- Call the procedure with 'Alice'
-- Your code here

Hint

Use CALL greet_user('Alice') to run the procedure with the name Alice.

4

Add exception handling to the procedure

Modify the Python code inside the procedure to catch any exceptions. Use a try block around the return statement and an except Exception as e block that returns 'Error: ' + str(e).

Snowflake

CREATE OR REPLACE PROCEDURE greet_user(user_name STRING)
RETURNS STRING
LANGUAGE PYTHON
AS
$$
# Add try-except to handle errors
# Your code here
$$;

CALL greet_user('Alice');

Hint

Use try: before the return and except Exception as e: to catch errors and return an error message.

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