Stored procedures in Python in Snowflake - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is a stored procedure in Snowflake using Python?

A stored procedure is a set of SQL and Python commands saved in the database that can be run repeatedly to perform tasks like data processing or automation.

beginner

How do you define a Python stored procedure in Snowflake?

You use the CREATE PROCEDURE statement with LANGUAGE PYTHON, then write your Python code inside a handler function that Snowflake runs when called.

intermediate

What is the role of the 'handler' function in a Snowflake Python stored procedure?

The handler function contains the Python code that executes when the stored procedure is called. It receives context and parameters and returns results.

intermediate

Why use stored procedures in Python on Snowflake instead of running scripts externally?

Stored procedures run close to the data inside Snowflake, reducing data transfer, improving speed, and allowing automation within the database environment.

advanced

What permissions are needed to create and run Python stored procedures in Snowflake?

You need the CREATE PROCEDURE privilege on the schema and USAGE privilege on the database and schema. Also, EXECUTE privilege to run the procedure.

Which keyword specifies that a stored procedure uses Python in Snowflake?

A LANGUAGE SHELL

B LANGUAGE SQL

C LANGUAGE PYTHON

D LANGUAGE JAVASCRIPT

In Snowflake, you specify LANGUAGE PYTHON to create a stored procedure using Python code.

What is the main function name Snowflake expects in a Python stored procedure?

A handler

B main

C run

D execute

Snowflake expects a function named 'handler' as the entry point for Python stored procedures.

Which privilege is NOT required to create a Python stored procedure in Snowflake?

A USAGE on schema

B DROP DATABASE

C EXECUTE on procedure

D CREATE PROCEDURE

DROP DATABASE privilege is unrelated to creating stored procedures.

Why is running Python code inside Snowflake stored procedures efficient?

A Because it uses external servers

B Because it uses JavaScript internally

C Because it requires no permissions

D Because it runs close to the data inside Snowflake

Running code inside Snowflake reduces data movement and speeds up processing.

How do you call a Python stored procedure in Snowflake after creation?

A CALL procedure_name()

B EXECUTE procedure_name()

C RUN procedure_name()

D START procedure_name()

The CALL statement is used to run stored procedures in Snowflake.

Explain how to create and use a Python stored procedure in Snowflake.

Think about the syntax, function name, and how you run it.

Describe the benefits of using Python stored procedures inside Snowflake compared to running Python scripts externally.

Consider where the code runs and how it affects performance.

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