Skip to main content

UDFs and Stored Procedures with Python in Snowflake

Snowflake enables organizations to implement custom business logic directly inside the data platform using Python (via Snowpark).

This allows both technical teams and business stakeholders to:

  • Standardize logic across reports
  • Avoid data movement
  • Improve performance and governance

🧭 Why This Matters (Business Perspective)

In real-world scenarios:

  • Different reports calculate metrics differently ❌
  • Logic is duplicated across tools ❌
  • Data is exported to Python/ETL tools ❌

πŸ‘‰ With UDFs & Stored Procedures:

  • Logic is centralized in Snowflake βœ…
  • Everyone uses the same definition of metrics βœ…
  • Processing happens close to data (faster & secure) βœ…

πŸ”Ή 1. Python UDF (User Defined Function)

πŸ“Œ What is a UDF?​

A UDF is a reusable function that applies business logic to each row of data.

πŸ‘‰ Think of it like:

β€œExcel formula, but standardized and reusable across the company”


🧠 When Business Users Care​

Use UDF when:

  • You want consistent calculations
  • You define business rules once
  • Analysts reuse logic in multiple reports

πŸ“Š Example: Customer Segmentation​

Business Requirement​

β€œClassify customers as Minor, Adult, or Senior based on age”


SQL: Create UDF​

CREATE OR REPLACE FUNCTION DEMO_DB.DEMO_SCHEMA.GET_AGE_CATEGORY(age INT)
RETURNS STRING
LANGUAGE PYTHON
RUNTIME_VERSION = '3.9'
HANDLER = 'age_category'
AS
$$
def age_category(age):
if age < 18:
return "Minor"
elif age < 60:
return "Adult"
else:
return "Senior"
$$;

▢️ Usage in Reports​

SELECT 
NAME,
AGE,
DEMO_DB.DEMO_SCHEMA.GET_AGE_CATEGORY(AGE) AS AGE_CATEGORY
FROM DEMO_DB.DEMO_SCHEMA.CUSTOMERS;

πŸ’‘ Business Value​

  • Same logic used in Power BI / Tableau / SQL
  • No manual duplication
  • Easy to update centrally

🧠 Key Characteristics​

  • Runs row-by-row
  • Lightweight and fast
  • Embedded directly in SQL queries

πŸ”Ή 2. Another UDF Example (Data Standardization)

Business Problem​

Customer names are inconsistent (upper/lowercase, spaces)


Solution​

CREATE OR REPLACE FUNCTION DEMO_DB.DEMO_SCHEMA.FORMAT_NAME(name STRING)
RETURNS STRING
LANGUAGE PYTHON
RUNTIME_VERSION = '3.9'
HANDLER = 'format_name'
AS
$$
def format_name(name):
return name.upper().strip()
$$;

▢️ Usage​

SELECT 
NAME,
DEMO_DB.DEMO_SCHEMA.FORMAT_NAME(NAME) AS CLEAN_NAME
FROM DEMO_DB.DEMO_SCHEMA.CUSTOMERS;

πŸ’‘ Business Impact​

  • Clean, standardized data across all reports
  • Improves data quality & trust

πŸ”Ή 3. Python Stored Procedure (Snowpark)

πŸ“Œ What is a Stored Procedure?​

A Stored Procedure is used for:

  • Multi-step processing
  • Data pipelines
  • Business workflows

πŸ‘‰ Think of it like:

β€œA backend job that prepares data for reporting”


🧠 When Business Users Care​

Use Stored Procedure when:

  • You need daily data refresh
  • You build aggregated datasets
  • You automate data pipelines

πŸ“Š Example: Customer Processing Pipeline​

Business Requirement​

β€œPrepare filtered and enriched customer dataset for reporting”


SQL: Create Stored Procedure​

CREATE OR REPLACE PROCEDURE DEMO_DB.DEMO_SCHEMA.PROCESS_CUSTOMERS()
RETURNS STRING
LANGUAGE PYTHON
RUNTIME_VERSION = '3.9'
PACKAGES = ('snowflake-snowpark-python')
HANDLER = 'main'
AS
$$
import snowflake.snowpark as snowpark
from snowflake.snowpark.functions import col, concat, lit

def main(session: snowpark.Session):

# Load source data
df = session.table("CUSTOMERS")

# Apply business rule: Age > 30
df_filtered = df.filter(col("AGE") > 30)

# Add derived column
df_result = df_filtered.with_column(
"AGE_GROUP",
concat(col("AGE").cast("string"), lit(" years"))
)

# Save curated dataset
df_result.write.save_as_table("CUSTOMERS_PROCESSED", mode="overwrite")

return "βœ“ Customers processed successfully"
$$;

▢️ Execute​

CALL DEMO_DB.DEMO_SCHEMA.PROCESS_CUSTOMERS();

πŸ“Š Output for Business​

SELECT * FROM DEMO_DB.DEMO_SCHEMA.CUSTOMERS_PROCESSED;

πŸ’‘ Business Value​

  • Automated data preparation
  • Ready-to-use dataset for dashboards
  • Reduces manual intervention

πŸ”₯ UDF vs Stored Procedure (Business View)

AspectUDFStored Procedure
PurposeApply logic to each rowPrepare datasets
Used byAnalysts / BI toolsData Engineers / Pipelines
ExecutionInside SELECTManual or scheduled
ComplexitySimpleComplex workflows
ExampleCategorize customerBuild reporting table

🧩 How They Work Together

Raw Data β†’ Stored Procedure β†’ Clean Table β†’ UDF β†’ Report

πŸ‘‰ Example:

  • Procedure prepares clean dataset
  • UDF applies business logic in reports

⚠️ Best Practices

UDF​

  • Keep logic simple
  • Avoid heavy computation
  • Use for reusable business rules

Stored Procedure​

  • Use for pipelines and transformations
  • Implement logging & error handling
  • Schedule using Snowflake Tasks

❌ Common Mistakes

  • Using UDF for heavy processing ❌
  • Repeating logic in multiple queries ❌
  • Not centralizing business rules ❌
  • Not automating pipelines ❌

🎯 Summary

  • UDF β†’ Standardize business logic across queries

  • Stored Procedure β†’ Build and automate data pipelines

  • Together β†’ Enable scalable, governed, and reusable data processing


πŸš€ Final Thought (For Business Users)

Instead of asking:

β€œWhich report is correct?”

You can confidently say:

β€œAll reports use the same centralized logic in Snowflake.”