Skip to content
Tutorials Center
My learning

Topics

Lesson 35 of 38 ยท Snowpark and Cost

UDFs, UDTFs and Stored Procedures

Write Snowflake user-defined functions in SQL and Python, table functions, vectorized UDFs, stored procedures with Snowflake Scripting and Python, and learn when to use each.

SQL has built-in functions, but you will eventually want your own logic: a business rule used in forty queries, a parser for an awkward string format, a call to a Python library. Snowflake offers two kinds of reusable code: functions (used inside queries, returning a value or rows) and stored procedures (called on their own, able to run statements and control flow).

These objects live in Snowflake and cannot run here. Their code is shown read-only. The runnable examples show the same logic as plain Python and SQLite so you can test it before you wrap it.

SQL UDFs

The simplest function is a SQL expression with a name:

SQL
CREATE OR REPLACE FUNCTION net_price(gross NUMBER, vat_rate NUMBER)
  RETURNS NUMBER
AS
$$
  gross / (1 + vat_rate)
$$;

SELECT order_id, net_price(amount, 0.2) FROM orders;

A SQL UDF is inlined into the query, so it is as fast as writing the expression yourself. Use it to give business rules one definition. A SQL UDF can also return a table (RETURNS TABLE (...)) with a SELECT body, making a parameterized view.

SQL The same rule as a SQL expression ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Python UDFs

When SQL gets awkward, write the function in Python. Snowflake runs it in a secure sandbox on the warehouse, and you can import packages from the Anaconda channel that Snowflake hosts.

SQL
CREATE OR REPLACE FUNCTION clean_phone(raw STRING)
  RETURNS STRING
  LANGUAGE PYTHON
  RUNTIME_VERSION = '3.12'
  HANDLER = 'clean'
AS
$$
import re

def clean(raw):
    if raw is None:
        return None
    digits = re.sub(r"\D", "", raw)
    return digits[-10:] if len(digits) >= 10 else None
$$;

SELECT clean_phone('+1 (555) 010-9999');

Points to remember:

  • HANDLER names the Python function to call for each row.
  • Use PACKAGES = ('numpy', 'pandas') to add libraries, and IMPORTS to load files from a stage.
  • A NULL input arrives as None; handle it, or declare RETURNS NULL ON NULL INPUT.
  • Python UDFs run row by row, so they are slower than built-in SQL. Prefer a SQL function when one exists.

Test the logic locally first:

Python Test the handler as plain Python

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Vectorized UDFs and UDAFs

A vectorized Python UDF processes batches of rows as a pandas Series instead of one row at a time, which cuts call overhead considerably for numeric work:

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

@pandas_udf(return_type="float", input_types=["float"], packages=["pandas"])
def zscore(s: pd.Series) -> pd.Series:
    return (s - s.mean()) / s.std()

A user-defined aggregate function (UDAF) supplies accumulate, merge and finish methods so you can write custom aggregations like a weighted median.

Table functions (UDTFs)

A UDTF returns rows, and is called in the FROM clause with TABLE(...). A Python UDTF is a class with process (called per input row, yielding output rows) and optionally end_partition.

SQL
CREATE OR REPLACE FUNCTION split_words(text STRING)
  RETURNS TABLE (word STRING, position INT)
  LANGUAGE PYTHON
  RUNTIME_VERSION = '3.12'
  HANDLER = 'Splitter'
AS
$$
class Splitter:
    def process(self, text):
        for i, w in enumerate((text or "").split()):
            yield (w, i)
$$;

SELECT s.word, s.position
FROM TABLE(split_words('stay curious about data')) AS s;
Python The UDTF class, tested locally

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Stored procedures

A stored procedure is code you CALL. Unlike a function it can run many SQL statements, use loops and branches, and change data (INSERT, DELETE, CREATE). Typical jobs are maintenance, multi-step loads and administration tasks that are then scheduled with tasks.

Snowflake Scripting (SQL with BEGIN ... END):

SQL
CREATE OR REPLACE PROCEDURE purge_old_rows(days INT)
  RETURNS STRING
  LANGUAGE SQL
AS
$$
DECLARE
  removed INT;
BEGIN
  DELETE FROM events WHERE event_time < DATEADD('day', -:days, CURRENT_TIMESTAMP());
  removed := SQLROWCOUNT;
  RETURN 'removed ' || removed || ' rows';
END;
$$;

CALL purge_old_rows(90);

Python stored procedure with Snowpark, where the first argument is the session:

SQL
CREATE OR REPLACE PROCEDURE daily_summary()
  RETURNS STRING
  LANGUAGE PYTHON
  RUNTIME_VERSION = '3.12'
  PACKAGES = ('snowflake-snowpark-python')
  HANDLER = 'run'
AS
$$
from snowflake.snowpark.functions import col, sum as sum_

def run(session):
    df = session.table("ORDERS").group_by("ORDER_DATE").agg(sum_("AMOUNT").alias("REVENUE"))
    df.write.mode("overwrite").save_as_table("DAILY_SUMMARY")
    return "ok"
$$;

Procedures run with the owner's rights by default (the caller needs only USAGE on the procedure) or caller's rights (EXECUTE AS CALLER, using the caller's role). Choose caller's rights when the procedure should act within the user's own permissions.

The control flow of a procedure, modelled in Python with SQLite:

Python A procedure: several statements and a return value (stand-in)

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Functions or procedures?

Function (UDF/UDTF) Stored procedure
Called Inside a query With CALL
Returns Value or rows used by the query A value or table to the caller
Can run DML/DDL No Yes
Typical use Reusable logic in SELECT Multi-step jobs, admin work

Also available: external functions (call a remote API through a gateway) and external network access for UDFs, which must be explicitly allowed by integration and network rules for security.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Which can modify tables with INSERT and DELETE?

    Functions are used inside queries; procedures run statements and can change data.
  2. 2. How do you call a stored procedure?

    Procedures are invoked with the CALL statement.
  3. 3. What does a Python UDTF's process method do?

    A UDTF is a class whose process method yields tuples, producing rows.
  4. 4. Why prefer a vectorized UDF for numeric work?

    Batching cuts call overhead compared with row-at-a-time functions.
  5. 5. Which is the cheapest to run for simple arithmetic?

    SQL UDFs are inlined into the query and as fast as writing the expression directly.

Summary

  • SQL UDFs are inlined and fast; Python UDFs add flexibility at a per-row cost.
  • UDTFs return rows; vectorized UDFs and UDAFs handle batches and custom aggregation.
  • Stored procedures run multiple statements with control flow and can change data.
  • Choose owner's or caller's rights deliberately; test logic locally first.

Next: Cost monitoring and resource monitors