Stored procedures in Python in Snowflake - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ service_behavior

intermediate

Understanding Python Stored Procedure Output in Snowflake

What will be the output of this Snowflake Python stored procedure when called?

CREATE OR REPLACE PROCEDURE test_proc()
RETURNS STRING
LANGUAGE PYTHON
AS
$$
  def run():
    return 'Hello from Snowflake!'
$$;

A The procedure raises a syntax error due to missing RETURN keyword

B The procedure returns the string 'Hello from Snowflake!'

C The procedure returns NULL because no explicit RETURN statement is used

D The procedure returns an empty string

💡 Hint

Remember that the function inside the procedure must return a value explicitly.

In Snowflake Python stored procedures, the function named 'run' is executed and its return value is returned by the procedure. Here, 'run' returns the string 'Hello from Snowflake!'.

❓ Configuration

intermediate

Correct Syntax for Defining a Python Stored Procedure in Snowflake

Which of the following is the correct way to define a Python stored procedure in Snowflake that returns an integer 42?

A

CREATE OR REPLACE PROCEDURE get_answer()
RETURNS INTEGER
LANGUAGE PYTHON
AS
$$
  def run():
    return 42
$$;

B

CREATE PROCEDURE get_answer()
RETURNS INTEGER
LANGUAGE PYTHON
AS
$$
  return 42
$$;

C

CREATE OR REPLACE PROCEDURE get_answer()
RETURNS INTEGER
LANGUAGE PYTHON
AS
$$
  def run():
    return '42'
$$;

D

CREATE OR REPLACE PROCEDURE get_answer()
RETURNS INTEGER
LANGUAGE PYTHON
AS
$$
  def run():
    print(42)
$$;

💡 Hint

The procedure must define a run() function that returns the correct type.

Option A correctly defines a Python stored procedure with a run() function returning integer 42. Option A misses the run() function. Option A returns nothing (print outputs to console, not return). Option A returns a string instead of integer.

❓ Architecture

advanced

Best Practice for Handling Exceptions in Snowflake Python Stored Procedures

You want to ensure your Python stored procedure in Snowflake handles errors gracefully and returns a meaningful message instead of failing silently or crashing. Which approach is best?

A Use print statements inside run() to log errors but do not catch exceptions

B Let exceptions propagate naturally so Snowflake logs the error automatically

C Wrap the run() function code in try-except blocks and return error messages as strings

D Return NULL on any error without catching exceptions

💡 Hint

Think about how to provide clear feedback from your procedure to callers.

Catching exceptions inside the run() function and returning error messages as strings allows the procedure to communicate errors clearly without crashing. Letting exceptions propagate may cause abrupt failures. Print statements do not affect return values.

❓ security

advanced

Security Consideration When Using Python Stored Procedures in Snowflake

Which of the following is a key security best practice when creating Python stored procedures in Snowflake?

A Always run procedures with ACCOUNTADMIN role for full access

B Disable all access controls to simplify procedure execution

C Store sensitive credentials directly inside the procedure code for quick access

D Limit the procedure's privileges by assigning minimal roles and avoid using ACCOUNTADMIN

💡 Hint

Think about the principle of least privilege.

Assigning minimal required privileges to the procedure and avoiding high-privilege roles like ACCOUNTADMIN reduces security risks. Storing credentials in code or disabling access controls are insecure practices.

🧠 Conceptual

expert

Behavior of Variable Scope in Snowflake Python Stored Procedures

Consider this Python stored procedure in Snowflake:

CREATE OR REPLACE PROCEDURE scope_test()
RETURNS STRING
LANGUAGE PYTHON
AS
$$
  x = 'outer'
  def run():
    x = 'inner'
    return x
$$;

What is the value returned when calling scope_test()?

A 'inner' because the run() function returns its local x variable

B 'outer' because the outer x variable is returned by default

C NULL because x is not returned explicitly

D SyntaxError due to variable shadowing

💡 Hint

Remember how Python handles variable scope inside functions.

The run() function defines a local variable x = 'inner' and returns it. The outer x is not returned. Variable shadowing is allowed and does not cause syntax errors.

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