User-Defined Functions & UDTFs
Snowflake · Stored Procedures & UDFs
Theory
A user-defined function (UDF) extends SQL with your own logic. A scalar UDF returns one value per input row and is used inline like a built-in function, for example SELECT amount, calc_tax(amount, state) FROM products. A user-defined table function (UDTF) returns a set of rows for each call and is used in the FROM clause with TABLE(...), for example generating one row per day in a date range.
UDFs can be written in SQL, JavaScript, Python, Java or Scala. Unlike procedures, functions cannot run DML or DDL; they only compute results. Secure UDFs hide their definition, and external functions call code hosted outside Snowflake, such as an HTTPS endpoint behind an API gateway, through an API integration.
Why we use it
Reusable business rules, such as tax calculation, scoring or parsing, should be written once rather than copied into many queries. UDFs make that logic callable everywhere and keep it consistent.
When to use it
Use a scalar UDF for per-row calculations, a UDTF to turn one input into many rows, and external functions only when logic must run in an outside service. Prefer built-in functions or plain SQL expressions when they are enough, since they are usually faster.
Scalar UDFs
Written in SQL, JavaScript, Python, or Java, a scalar UDF takes input values and returns one output value — usable directly inside a SELECT list exactly like UPPER() or ROUND().
UDTFs: Returning a Table
A UDTF can return multiple rows and columns per call, similar in spirit to FLATTEN — useful for logic like splitting one row into several, or running a repeatable multi-row calculation.
External Functions
An external function calls out to an API hosted outside Snowflake (via API Gateway), letting SQL invoke arbitrary external logic — the escape hatch when logic can't practically live inside Snowflake.
How it works
- Need reusable logic in a query
- Does it return one value per row? Yes: Create a scalar UDF and call it in SELECT. No: Consider a UDTF.
- Does it return several rows per input? Yes: Create a UDTF and use TABLE(...) in FROM. No: Use a stored procedure for multi-step work.
- Grant USAGE on the function to roles that need it
- Call it like a built-in function
Example
CREATE OR REPLACE FUNCTION calc_tax(amount FLOAT, state VARCHAR)
RETURNS FLOAT AS
$$
amount * CASE state WHEN 'Maharashtra' THEN 0.18 WHEN 'Delhi' THEN 0.12 ELSE 0.10 END
$$;
SELECT amount, calc_tax(amount, 'Maharashtra') AS tax FROM products;
CREATE OR REPLACE FUNCTION date_range(start_date DATE, end_date DATE)
RETURNS TABLE (dt DATE) LANGUAGE SQL AS
$$
SELECT DATEADD(DAY, SEQ4(), start_date) FROM TABLE(GENERATOR(ROWCOUNT => DATEDIFF(DAY,start_date,end_date)+1))
$$;Key points
- Scalar UDFs return one value per call, usable inline in SELECT
- UDTFs return multiple rows and are called with TABLE(...)
- Functions cannot run DML or DDL; procedures can
- Secure UDFs hide their definition from non-owners
- External functions call APIs hosted outside Snowflake via an API integration