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.