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

Need a 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
$$;

Need a 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

Need a 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');

Need a hint?

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