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)
| Aspect | UDF | Stored Procedure |
|---|---|---|
| Purpose | Apply logic to each row | Prepare datasets |
| Used by | Analysts / BI tools | Data Engineers / Pipelines |
| Execution | Inside SELECT | Manual or scheduled |
| Complexity | Simple | Complex workflows |
| Example | Categorize customer | Build 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.β