Skip to main content

User-Defined Functions (Python UDFs) in Snowpark

Sometimes, native SQL functions are not enough. If you need to perform complex natural language processing, advanced string matching, or custom mathematical modeling on a column of data, you need Python.

Snowpark allows you to write custom Python functions and deploy them into Snowflake as UDFs. Analysts can then call your Python code directly inside their SQL SELECT statements!

Topics Covered​

  • Scalar Python UDFs
  • Deploying UDFs (Permanent vs Temporary)
  • Using Third-Party Packages (Anaconda)
  • Vectorized (Pandas) UDFs for High Performance

1. Scalar Python UDFs​

A scalar UDF processes one row at a time. Let's create a Python function that determines if an Indian phone number is valid (it must start with +91 and be exactly 13 characters long).

First, write the standard Python function on your laptop:

def validate_indian_phone(phone_number: str) -> bool:
if phone_number is None:
return False
# Remove spaces
cleaned = phone_number.replace(" ", "")
# Check format
if cleaned.startswith("+91") and len(cleaned) == 13 and cleaned[3:].isdigit():
return True
return False

2. Deploying the UDF via Decorator​

The easiest way to push this function into Snowflake is using the @udf decorator provided by Snowpark.

(Make sure you have an active Snowpark session object)

from snowflake.snowpark.functions import udf
from snowflake.snowpark.types import BooleanType, StringType

# The decorator automatically registers the function in Snowflake
@udf(name="is_valid_phone_udf",
session=session,
is_permanent=True,
stage_location="@BEEMS_UDF_STAGE",
replace=True)
def validate_indian_phone_udf(phone_number: str) -> bool:
if phone_number is None:
return False
cleaned = phone_number.replace(" ", "")
if cleaned.startswith("+91") and len(cleaned) == 13 and cleaned[3:].isdigit():
return True
return False

print("Python UDF successfully deployed to Snowflake!")

3. Using the Python UDF in SQL​

Once the Python code is deployed, any BI analyst can use it in standard SQL without knowing any Python.

-- An analyst running standard SQL inside the Snowflake UI
SELECT
CUSTOMER_ID,
PHONE_NUMBER,
is_valid_phone_udf(PHONE_NUMBER) AS IS_VALID
FROM CUSTOMERS;

4. Using Third-Party Packages (Anaconda)​

The true power of Python is its ecosystem. Snowflake has partnered with Anaconda, meaning thousands of popular Python packages (like numpy, scipy, nltk) are natively available inside the Snowflake compute nodes.

Let's use the phonenumbers library to do advanced parsing instead of our manual string logic.

# 1. Tell the session which Anaconda packages this UDF will need
session.add_packages("phonenumbers")

# 2. Register the UDF
@udf(name="parse_phone_advanced", session=session, replace=True, is_permanent=True, stage_location="@BEEMS_UDF_STAGE")
def parse_phone_udf(phone: str) -> str:
import phonenumbers # Import happens INSIDE the function
try:
parsed = phonenumbers.parse(phone, "IN") # Default to India
if phonenumbers.is_valid_number(parsed):
return phonenumbers.format_number(parsed, phonenumbers.PhoneNumberFormat.E164)
return "INVALID"
except Exception:
return "ERROR"

5. Vectorized (Pandas) UDFs for Performance​

A standard Scalar UDF is somewhat slow because Snowflake has to pass data to the Python execution engine one row at a time. If you are processing 500 million rows, this overhead becomes a bottleneck.

Vectorized UDFs solve this. Instead of passing one row, Snowflake passes a "batch" of rows (as a Pandas Series) to the function. The function processes the entire batch at once using highly optimized C-level code, and returns a batch of results.

Vectorized UDFs can be 10x to 100x faster than Scalar UDFs.

from snowflake.snowpark.functions import pandas_udf
import pandas as pd

# The type hints MUST specify pandas.Series
@pandas_udf(name="calculate_discount_batch",
session=session,
is_permanent=True,
stage_location="@BEEMS_UDF_STAGE",
replace=True)
def calculate_discount_batch_udf(price_series: pd.Series) -> pd.Series:

# We apply the math to the entire Series at once! No loops required.
discounted_series = price_series * 0.90

return discounted_series

When to use Vectorized vs Scalar​

  • Use Scalar UDFs if the logic is very simple, or if it requires complex conditional if/else logic that is difficult to express in Pandas.
  • Use Vectorized (Pandas) UDFs if you are performing heavy mathematical calculations on massive datasets (e.g., machine learning inference).

Summary​

  • Python UDFs allow you to write Python functions and expose them as native SQL functions in Snowflake.
  • Use the @udf decorator to easily push local Python functions into Snowflake.
  • Use session.add_packages() to leverage the massive Anaconda ecosystem inside Snowflake.
  • Use Vectorized (Pandas) UDFs (@pandas_udf) to process data in batches, dramatically improving performance for large datasets.