What you will be able to do
- Choose between a scalar UDF, a UDTF, a stored procedure and a materialized view for a reusable calculation
- Pick a handler language based on whether it can be staged and whether the UDF can be shared
- Call a UDTF correctly from the FROM clause, including laterally against another table
- Explain how a Python UDTF handler class uses __init__, process and end_partition across partitions
Key concept
Function versus procedure — A UDF or UDTF runs inside a query and gives back a value or a set of rows, so it belongs in SELECT, WHERE or FROM. A stored procedure is a separate unit of procedural work that you run with its own CALL statement.
1.Scalar UDFs and when a function is the right object
A user-defined function (UDF) puts logic you write into something a query can call. You write one when no built-in function does the job, or when a calculation is standard across the organisation, such as a tax rule or a margin formula, and should be defined once and reused. UDFs are called the same way as built-in functions, so a dashboard query can use one in its SELECT list, its WHERE clause or a join condition.
The basic UDF is scalar: one value comes out for every row that goes in. The other variations differ in what goes in and what comes out:
| Variation | Behaviour |
|---|---|
| User-defined function (UDF) | Scalar: one output row with a single value for each input row |
| User-defined aggregate function (UDAF) | Operates on values across multiple rows, e.g. sum, average, standard deviation |
| User-defined table function (UDTF) | Returns a tabular value for each input row |
| Vectorized UDF | Receives batches of rows as Pandas DataFrames, returns Pandas arrays or Series |
| Vectorized UDTF | Receives batches of rows as Pandas DataFrames, returns tabular results |
Three other objects can look like they fit, but none of them can be called inside an expression. A stored procedure is created with CREATE PROCEDURE and runs as a separate CALL statement. It is meant for branching, looping and multi-step work, not for producing a value partway through a query. A UDTF returns rows, so it is read from the FROM clause. A materialized view stores results that were computed ahead of time, and it keeps costing storage and compute to stay current. That suits an expensive aggregation that runs often. It is waste for a formula that is cheap to compute when needed. A reusable arithmetic expression with no side effects is therefore a scalar UDF that returns a number.
CREATE OR REPLACE FUNCTION addone(i INT)
RETURNS INT
LANGUAGE PYTHON
RUNTIME_VERSION = '3.12'
HANDLER = 'addone_py'
AS $$
def addone_py(i):
return i+1
$$;You then call it like any built-in: SELECT addone(3);. The function name addone and the handler name addone_py are different, and they do not have to match. Code inside a handler also has its own context. If it calls CURRENT_DATABASE or CURRENT_SCHEMA, the result is the database or schema that contains the UDF, not the one the session is using. That catches people out when a UDF builds object names from those functions.
Checkpoint 1 of 7· Fill the gap
A team wants a reusable "add one" calculation that a dashboard can call inline, as in SELECT addone(3);, and that returns one value per input row. Which keyword completes the definition?
CREATE OR REPLACE ? addone(i INT)
RETURNS INT
LANGUAGE PYTHON
RUNTIME_VERSION = '3.12'
HANDLER = 'addone_py'
AS $$
def addone_py(i):
return i+1
$$;A cheap calculation with no side effects that is called inside an expression is a scalar UDF, so it is created with CREATE FUNCTION. A procedure only runs through its own CALL statement and cannot be used inside SELECT. A materialized view stores precomputed results and keeps costing storage and compute, which is wasted on a formula this cheap.
Source: docs.snowflake.comCheckpoint 2 of 7· Exam question
A finance analyst needs one reusable tax calculation that BI dashboards can call inside SELECT lists and WHERE clauses across many queries. The logic is a single arithmetic expression with no side effects. Which object fits best?
Correct answer: C — A SQL scalar UDF created with CREATE FUNCTION that returns a NUMBER from its input arguments
- A. A materialized view precomputes query results from one table, not an arbitrary calculation, and cannot take parameters the way a function does.
- B. Stored procedures run through a standalone CALL statement and cannot be embedded in a SELECT list or WHERE clause, so dashboards cannot apply one row by row.
- C. A scalar UDF returns one value per input row and can be invoked anywhere an expression is allowed, including SELECT lists and WHERE clauses. A SQL body keeps the logic simple and lets the optimizer inline it.
- D. A table function is queried in the FROM clause through TABLE(), so it cannot be used as a scalar expression in a SELECT list or filter without extra joins.
2.Handler languages, in-line versus staged code, and sharing
A UDF's handler can be written in Java, JavaScript, Python, Scala or SQL. You can put the handler in-line in the CREATE FUNCTION body. For some languages you can instead package the code and copy it to a stage. Languages also differ in what the definition needs. Java, Python and Scala handlers need a HANDLER clause naming the class or method to run, plus a runtime version. If you rely on the default runtime, remember that the default changes over time. For JavaScript and SQL, all of the in-line code is the handler.
| Handler language | Handler location | Sharable |
|---|---|---|
| Java | In-line or staged | No |
| JavaScript | In-line | Yes |
| Python | In-line or staged | No |
| Scala | In-line or staged | No |
| SQL | In-line | Yes |
For a data provider, the Sharable column matters most. A sharable UDF can be used with Secure Data Sharing, and only SQL and JavaScript handlers produce one. A Python scoring function cannot be handed to a consumer that way, however well written it is. Sharing itself is covered in data sharing and the Marketplace. Security is a separate concern from sharing: sensitive information has to stay concealed from users who should not have access to it, and a function is no exception. Snowflake's mechanism for that is the secure UDF, which is also available for stored procedures.
Checkpoint 3 of 7· Check yourself
A data provider has written a well-tested Python UDF that scores customers. Consumer accounts need to use it through Secure Data Sharing. What should the provider do?
Whether a UDF can be shared depends on its handler language, not on where the code lives or how the definition is pinned. Only SQL and JavaScript handlers produce a UDF that works with Secure Data Sharing. Java, Python and Scala UDFs are not sharable, whether their handlers are in-line or staged.
“SQL | In-line | Yes”Source: docs.snowflake.com
3.UDTFs: functions that return rows
A user-defined table function (UDTF) returns zero, one or many rows, and each row has one or more columns. You declare the output shape with RETURNS TABLE (col type, ...), which needs at least one column. In a SQL UDTF the body must be a SELECT, and its result has to match the declared columns in both number and data type.
CREATE FUNCTION t()
RETURNS TABLE(msg VARCHAR)
AS
$$
SELECT 'Hello'
UNION
SELECT 'World'
$$;
SELECT msg
FROM TABLE(t())
ORDER BY msg;The calling rule is strict. A UDTF goes in the FROM clause as TABLE(udtf_name(args)). It does not work as a value in the SELECT list, as the right side of IN in a WHERE clause, or with CALL, which runs procedures. Once the UDTF is in FROM, its output columns can be used anywhere a normal table column can.
UDTFs are most useful when they run once per row of another table. You pass a column from a table listed earlier in FROM as the argument, and each input row produces its own output rows. An explicit JOIN works, and so does a comma between the table and TABLE(...). Any extra conditions go in WHERE, not ON.
select *
from favorite_years y join table(favorite_colors(y.year)) c
order by year, color;A UDTF can take up to 500 input arguments and return up to 500 output columns. A simple parameterised UDTF like orders_for_product can also be built as a view. The UDTF wins when the argument changes on every call.
Checkpoint 4 of 7· Check yourself
Table fashion has columns year and fashion_color. For each fashion row, you want to pass its year to the UDTF favorite_colors(the_year), which returns a color column. You only want to keep rows where the returned color equals fashion_color. Which query is written correctly?
A UDTF returns rows, so it goes in the FROM clause inside TABLE(...), and it can take a column from the table listed before it. Any extra condition on the joined result goes in WHERE, not ON. A UDTF cannot be used as a value in the SELECT list or as the right side of IN.
“Use a WHERE clause, rather than ON, for additional predicates:”Source: docs.snowflake.com
Checkpoint 5 of 7· Exam question
A table customers has a column tags holding comma-separated text. An analyst wrote a UDTF split_tags(STRING) returning one row per tag. Select TWO queries that correctly return one output row per customer-tag pair.(Select 2)
Correct answers: A, E — SELECT c.id, t.tag FROM customers c CROSS JOIN TABLE(split_tags(c.tags)) t; SELECT c.id, t.tag FROM customers c, TABLE(split_tags(c.tags)) t
- A. A cross join to TABLE(function(column)) is also a correlated lateral call, so each customer row is expanded to its tags.
- B. A table function cannot be used as a value list in a WHERE predicate, and the column
tagis not defined at that point. - C. A UDTF cannot be called like a scalar function in the SELECT list; it must be queried through TABLE() in the FROM clause.
- D. TABLE() belongs in the FROM clause; placing it in the SELECT list is a syntax error.
- E. Listing the table function with TABLE() after a comma in FROM performs an implicit lateral join, so the UDTF runs once for each customer row.
- F. CALL is for stored procedures, and it cannot reference columns of another table, so it cannot invoke a UDTF per row.
Sources6
4.How a Python UDTF handler processes partitions
A Python UDTF handler is a class, not a single function, and Snowflake calls its methods in a fixed order for each partition of input rows:
- When a partition starts,
__init__creates a fresh instance of the class. This is where you set up state for that partition, such as a running total. processruns once for each row in the partition. It can update the state and yield output tuples.end_partitionis optional. It runs once after the last row of the partition, takes no arguments other thanself, and can yield summary rows.
class StockSaleSum:
def __init__(self):
self._cost_total = 0
self._symbol = ""
def process(self, symbol, quantity, price):
self._symbol = symbol
cost = quantity * price
self._cost_total += cost
yield (symbol, cost)
def end_partition(self):
yield (self._symbol, self._cost_total)You call it with TABLE(stock_sale_sum(symbol, quantity, price) OVER (PARTITION BY symbol)) in the FROM clause. Each symbol then gets its own instance, its own running total and its own end-of-partition row. process receives rows in the order given by the partition's ORDER BY, if there is one. Partitioning happens even without a PARTITION BY clause, because Snowflake partitions the data implicitly. So never assume a single instance sees every row. A handler that needs no state can leave out __init__ and end_partition and implement only process.
Vectorized UDTFs work differently. With a vectorized end_partition, the class has no process method. Instead, end_partition receives the whole partition as a pandas DataFrame and returns a DataFrame, and the function must be called with PARTITION BY. A regular UDTF never hands back one DataFrame from a single call. It yields tuples row by row.
Checkpoint 6 of 7· Check yourself
You want a Python table function's handler to get each partition's rows all at once as a pandas DataFrame and return tabular results. You do not want a handler that yields tuples one row at a time from process. Which function variation do you need?
Only a vectorized UDTF takes DataFrames in and returns tabular results. A regular UDTF always yields tuples row by row and never returns one DataFrame from a single call. A vectorized UDF returns pandas arrays or Series, not a table.
“Vectorized user-defined table function (UDTF) | Receive batches of input rows as Pandas DataFrames and return tabular results.”Source: docs.snowflake.com
Checkpoint 7 of 7· Exam question
A team writes a Python UDTF to compute a running summary inside each region group. Select TWO statements that correctly describe how the handler class works when the function is called with OVER (PARTITION BY region).(Select 2)
Correct answers: A, D — The optional end_partition method is called after the last row of a partition so it can emit summary rows; The process method is called once for every input row in the partition and yields output tuples
- A. end_partition runs once per partition after all rows have been processed, which suits aggregate-style output such as totals.
- B. end_partition fires once per partition, not per row; per-row work belongs in process.
- C. Snowflake honors PARTITION BY and creates handler state per partition, so rows from different regions are processed separately.
- D. process receives the argument values of each row and can yield zero or more result tuples, which become output rows.
- E. A standard UDTF handler is a class whose methods yield tuples; returning a DataFrame is only part of vectorized handlers, not a general rule.
Sources
(8)
Every claim above is drawn from one of these pages, quoted as it was written on the date shown.
- 1.
“Also known as a scalar function, returns one output row for each input row.”
↩︎ Scalar UDFs and when a function is the right object“UDFs you create can be called in a way similar to built-in functions.”
↩︎ Scalar UDFs and when a function is the right object“the function returns the database or schema that contains the UDF, not the database or schema in use for the session.”
↩︎ Scalar UDFs and when a function is the right object“as code in-line with the function definition or as code external to the definition, such as code packaged and copied to a stage”
↩︎ Handler languages, in-line versus staged code, and sharing“Ensure that sensitive information is concealed from users who should not have access to it.”
↩︎ Handler languages, in-line versus staged code, and sharing“A function always returns a value explicitly by specifying an expression, so it’s a good choice for calculating and return a value.”
↩︎ Key concept“A sharable UDF can be used with the Snowflake Secure Data Sharing feature.”
↩︎ Exam trap 3“SQL | In-line | Yes”
↩︎ Checkpoint“Vectorized user-defined table function (UDTF) | Receive batches of input rows as Pandas DataFrames and return tabular results.”
↩︎ Checkpoint - 2.https://docs.snowflake.com/en/developer-guide/stored-procedure/stored-procedures-overviewOfficial docs
“Once you have a handler, you can create a procedure with a CREATE PROCEDURE command, then call the procedure with a CALL statement.”
↩︎ Scalar UDFs and when a function is the right object - 3.
“Materialized views impact your costs for both storage and compute resources”
↩︎ Scalar UDFs and when a function is the right object - 4.
“You need specify a handler name only for handlers written in Java, Python, and Scala.”
↩︎ Handler languages, in-line versus staged code, and sharing“Keep in mind that if you use the default version, that default will change over time.”
↩︎ Handler languages, in-line versus staged code, and sharing - 5.
“Specifies that the procedure is secure.”
↩︎ Handler languages, in-line versus staged code, and sharing - 6.
“A UDTF can be accessed in the FROM clause of a query.”
↩︎ UDTFs: functions that return rows“The main body (aka “definition”) of a SQL UDTF must be a SELECT expression.”
↩︎ UDTFs: functions that return rows“Tabular functions (UDTFs) have a limit of 500 input arguments and 500 output columns.”
↩︎ UDTFs: functions that return rows“Use a WHERE clause, rather than ON, for additional predicates:”
↩︎ UDTFs: functions that return rows“When calling a UDTF, you must include the UDTF name and arguments inside parentheses following the TABLE keyword.”
↩︎ Exam trap 1“When calling a UDTF, you must include the UDTF name and arguments inside parentheses following the TABLE keyword.”
↩︎ Prediction - 7.
“Snowflake uses the __init__ method of your handler class to create an instance of the class.”
↩︎ How a Python UDTF handler processes partitions“For each row in the partition, Snowflake invokes the process method.”
↩︎ How a Python UDTF handler processes partitions“Your end_partition method won’t receive any arguments from Snowflake, which simply invokes it after you process the last row in the partition.”
↩︎ How a Python UDTF handler processes partitions“Even when a call to the UDTF doesn’t include a PARTITION BY clause, Snowflake partitions the data implicitly.”
↩︎ Exam trap 2 - 8.
“Define an end_partition method that takes in a DataFrame argument and returns or yields a pandas.DataFrame”
↩︎ How a Python UDTF handler processes partitions“A UDTF with a vectorized end_partition method must be called with a PARTITION BY clause to build the partitions.”
↩︎ How a Python UDTF handler processes partitions