User-defined functions with Snowpark in Snowflake - Mini Project: Build & Apply

User-defined functions with Snowpark

📖 Scenario: You are working with Snowflake's Snowpark to create a user-defined function (UDF) that processes data inside the database. This helps you run custom logic close to your data for faster and scalable processing.

🎯 Goal: Build a simple user-defined function (UDF) using Snowpark that takes a string input and returns the string in uppercase.

📋 What You'll Learn

Create a Snowpark session variable called session.

Define a Python function called to_upper that takes one string argument.

Register the to_upper function as a UDF named uppercase_udf in Snowflake.

Use the UDF in a Snowpark DataFrame to transform a column of strings to uppercase.

💡 Why This Matters

🌍 Real World

User-defined functions let you run custom code close to your data in Snowflake, improving performance and enabling complex logic inside the database.

💼 Career

Knowing how to create and use UDFs with Snowpark is valuable for data engineers and analysts who want to extend Snowflake's capabilities with custom processing.

1

Create a Snowpark session

Create a Snowpark session variable called session using Session.builder.configs() with an empty dictionary {} and then call .create().

Snowflake

# Create a Snowpark session variable called session
# Your code here

Hint

Use Session.builder.configs({}).create() to create the session.

2

Define the Python function to convert strings to uppercase

Define a Python function called to_upper that takes one argument input_str and returns input_str.upper().

Snowflake

from snowflake.snowpark import Session

session = Session.builder.configs({}).create()

# Define the function to_upper that returns the uppercase of input_str
# Your code here

Hint

Use the upper() method on the string argument.

3

Register the Python function as a Snowflake UDF

Register the function to_upper as a Snowflake UDF named uppercase_udf using session.udf.register() with to_upper and name='uppercase_udf'.

Snowflake

from snowflake.snowpark import Session

session = Session.builder.configs({}).create()

def to_upper(input_str):
    return input_str.upper()

# Register to_upper as a UDF named uppercase_udf
# Your code here

Hint

Use session.udf.register() with the function and the name.

4

Use the UDF in a Snowpark DataFrame

Create a Snowpark DataFrame called df from a list of strings [('hello',), ('world',)] with column name 'word'. Then create a new DataFrame called df_upper by selecting the column 'word' and applying the UDF uppercase_udf on it as uppercase_word.

Snowflake

from snowflake.snowpark import Session

session = Session.builder.configs({}).create()

def to_upper(input_str):
    return input_str.upper()

uppercase_udf = session.udf.register(to_upper, name='uppercase_udf')

# Create DataFrame df and apply uppercase_udf to create df_upper
# Your code here

Hint

Use session.create_dataframe() with the list and schema, then use select() with the UDF and alias().

Practice

1. What is the main purpose of user-defined functions (UDFs) in Snowflake using Snowpark?

easy

A. To manage user permissions and roles

B. To create new database tables automatically

C. To run custom code inside Snowflake and reuse logic

D. To monitor Snowflake account usage

Solution

1. Step 1: Understand what UDFs do

   User-defined functions let you write your own code to run inside Snowflake, which helps reuse logic.

2. Step 2: Identify the main purpose

   UDFs simplify queries by encapsulating custom logic, not managing tables, permissions, or monitoring.

3. Final Answer:

   To run custom code inside Snowflake and reuse logic -> Option C

4. Quick Check:

   UDFs = Custom code reuse [OK]

Hint: UDFs run your own code inside Snowflake [OK]

Common Mistakes:

• Thinking UDFs create tables
• Confusing UDFs with security roles
• Assuming UDFs monitor usage

2. Which of the following is the correct way to define a simple Python UDF in Snowpark that adds 10 to an integer input?

easy

A. def add_ten(x): return x + 10

B. def add_ten(x): x + 10

C. def add_ten(x): print(x + 10)

D. def add_ten(x): return x - 10

Solution

1. Step 1: Check function syntax

   A Python function must use 'return' to output a value; def add_ten(x): return x + 10 correctly returns x + 10.

2. Step 2: Verify logic correctness

   def add_ten(x): return x + 10 adds 10 to input x, matching the requirement; others either miss return or subtract.

3. Final Answer:

   def add_ten(x): return x + 10 -> Option A

4. Quick Check:

   Return correct value with 'return' keyword [OK]

Hint: Always use 'return' to output from Python UDFs [OK]

Common Mistakes:

• Omitting the return statement
• Using print instead of return
• Returning wrong calculation

3. Given this Snowpark Python UDF code:

def multiply_by_two(x):
    return x * 2

result = multiply_by_two(5)

What is the value of result?

medium

A. 5

B. None

C. 25

D. 10

Solution

1. Step 1: Understand the function logic

   The function multiplies input x by 2 and returns the result.

2. Step 2: Calculate the function output for input 5

   5 multiplied by 2 equals 10, so result is 10.

3. Final Answer:

   10 -> Option D

4. Quick Check:

   5 * 2 = 10 [OK]

Hint: Multiply input by 2 to get output [OK]

Common Mistakes:

• Confusing multiplication with addition
• Assuming function returns None without return
• Mistaking input for output

4. You wrote this Snowpark Python UDF:

def square(x):
    x * x

When you run it, you get no output. What is the problem?

medium

A. Function name is invalid

B. Missing return statement to output the result

C. Syntax error due to missing colon

D. Input parameter x is not defined

Solution

1. Step 1: Check function syntax

   The function lacks a 'return' statement, so it does not output any value.

2. Step 2: Understand Python function behavior

   Without 'return', the function returns None by default, causing no output.

3. Final Answer:

   Missing return statement to output the result -> Option B

4. Quick Check:

   Functions need 'return' to output values [OK]

Hint: Always include 'return' to output from functions [OK]

Common Mistakes:

• Assuming last expression auto-returns
• Thinking function name causes error
• Confusing missing colon with missing return

5. You want to create a Snowpark Python UDF that returns the length of a string only if it is not empty; otherwise, it returns 0. Which function correctly implements this?

hard

A. def str_length(s):
if s:
return len(s)
else:
return 0

B. def str_length(s):
return len(s) if s != '' else 0

C. def str_length(s):
if s == '':
return len(s)
else:
return 0

D. def str_length(s):
return len(s) or 0

Solution

1. Step 1: Understand the condition for empty string

   Empty strings are falsy in Python, so 'if s:' checks if string is not empty.

2. Step 2: Verify correct return values

   If string is not empty, return its length; else return 0. def str_length(s): if s: return len(s) else: return 0 matches this logic exactly.

3. Final Answer:

   def str_length(s): if s: return len(s) else: return 0 -> Option A

4. Quick Check:

   Check string truthiness to decide length [OK]

Hint: Use 'if s:' to check non-empty string [OK]

Common Mistakes:

• Checking 's != \"\"' crashes on None
• Returning length for empty string incorrectly
• Using 'or' without proper condition