UDFs & Stored Procedures: Complete Guide to Snowflake Custom Logic

In Snowflake, custom logic that goes beyond the built-in functions is implemented with UDFs (User Defined Functions) and Stored Procedures. UDFs are used inside SELECT statements to transform or compute values, while Stored Procedures are invoked with CALL and can run complex flows that include DDL and DML.

This article gives a comprehensive walkthrough of the differences between UDFs and Stored Procedures, the CREATE syntax in each supported language, Vectorized UDFs, and the privilege models (Caller's Rights / Owner's Rights). These topics show up frequently in the Data Transformation domain of the SnowPro Core exam.

UDF vs Stored Procedure Comparison

Here are the differences between UDFs and Stored Procedures at a glance. The SnowPro exam frequently asks which one fits a given situation.

UDF Types: Scalar UDFs and Table UDFs (UDTFs)

Scalar UDF

Takes one or more input values and returns a single value. It can be used anywhere — in the SELECT list, WHERE clause, JOIN conditions, and more — for things like custom hash functions or business-rule calculations.

-- SQL scalar UDF: tax-inclusive price calculation
CREATE OR REPLACE FUNCTION calc_tax_price(price NUMBER, tax_rate NUMBER)
RETURNS NUMBER(12,2)
LANGUAGE SQL
AS
$
  price * (1 + tax_rate)
$;

-- Example usage
SELECT product_name, calc_tax_price(price, 0.10) AS tax_included
FROM products;

Table UDF (UDTF)

A function that returns a tabular result (multiple rows and columns). It is invoked in the FROM clause as TABLE(my_udtf(args)), and is used for unpivoting one input row into many output rows, or for aggregations that span multiple rows.

-- Python UDTF: split a CSV string into rows
CREATE OR REPLACE FUNCTION split_csv(csv_string VARCHAR)
RETURNS TABLE(item VARCHAR)
LANGUAGE PYTHON
RUNTIME_VERSION = '3.11'
HANDLER = 'SplitCSV'
AS
$
class SplitCSV:
    def process(self, csv_string):
        for item in csv_string.split(','):
            yield (item.strip(),)
$;

-- Example usage
SELECT t.item
FROM my_table, TABLE(split_csv(my_table.tags)) t;

Language Support Matrix

Vectorized UDFs (Pandas UDFs)

Vectorized UDFs are the high-performance variant of Python UDFs. While a regular scalar UDF calls the Python function once per row, a Vectorized UDF receives input as a batch of pandas.Series and uses vectorized operations. Expect 5-50x throughput gains on numeric computation over large row counts.

-- Vectorized UDF: receive pandas.Series and process in batches
CREATE OR REPLACE FUNCTION normalize_score(score NUMBER)
RETURNS NUMBER
LANGUAGE PYTHON
RUNTIME_VERSION = '3.11'
PACKAGES = ('pandas')
HANDLER = 'normalize'
AS
$
import pandas as pd

def normalize(score: pd.Series) -> pd.Series:
    return (score - score.mean()) / score.std()
$;

With Snowpark, you can define the equivalent Vectorized UDF by setting PandasSeries in theinput_types argument of the @udf decorator.

Stored Procedure Implementation Patterns

A Stored Procedure is program logic invoked with CALL. It can implement complex flows that include DDL/DML execution, transaction control, conditional branching, and exception handling.

-- Snowflake Scripting (SQL) Stored Procedure
CREATE OR REPLACE PROCEDURE archive_old_orders(cutoff_date DATE)
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS OWNER
AS
$
BEGIN
  INSERT INTO orders_archive
    SELECT * FROM orders WHERE order_date < :cutoff_date;
  DELETE FROM orders WHERE order_date < :cutoff_date;
  RETURN 'Archived orders before ' || :cutoff_date;
EXCEPTION
  WHEN OTHER THEN
    ROLLBACK;
    RETURN 'Error: ' || SQLERRM;
END;
$;

-- Run
CALL archive_old_orders('2025-01-01');

Privilege Model: Owner's Rights vs Caller's Rights

-- Example of Caller's Rights
CREATE OR REPLACE PROCEDURE list_tables(db_name VARCHAR)
RETURNS TABLE(table_name VARCHAR)
LANGUAGE SQL
EXECUTE AS CALLER
AS
$
BEGIN
  RETURN TABLE(
    SELECT table_name FROM information_schema.tables
    WHERE table_catalog = :db_name
  );
END;
$;

Authoring with Snowpark

With the Snowpark API, you define functions using the @udf, @udtf, and @sproc decorators and register them in Snowflake through the session.

• Snowflake type mappings are generated automatically from Python type hints
• Specify third-party packages via the packages parameter
• Upload custom modules via the imports parameter
• Use is_permanent=True to persist as a permanent UDF/Stored Procedure

Sample Question

Frequently Asked Questions