Data Transformation · Topic 2 of 14

User-Defined Table Functions and Aggregate Functions

Learn when to use a UDTF or a UDAF in Snowflake: handler methods, TABLE() calls, PARTITION BY, lateral joins and the accumulate and merge lifecycle.

  • 4 min
In a nutshell
  • UDTF: RETURNS TABLE (...), called as TABLE(f(...)), one row in and many rows out.
  • UDTF handlers use process per row and end_partition or finalize per partition.
  • OVER (PARTITION BY ...) gives the handler whole groups.

What it is

A scalar UDF returns one value per row. Two other kinds of custom function change the shape of the data.

  • A user-defined table function (UDTF) returns zero, one or many rows for each input row, or for each partition of rows. It is called in the FROM clause.
  • A user-defined aggregate function (UDAF) takes many rows and returns one value, like a custom SUM or MEDIAN, and works with GROUP BY.
One input rowUDTFMany output rowsMany input rowsUDAFOne output value

How it works

UDTFs

A UDTF declares RETURNS TABLE (...) with its output columns. You call it with the TABLE keyword.

SELECT * FROM TABLE(split_words('the quick fox'));

-- apply to every row of a table with a lateral join
SELECT d.id, w.word
FROM docs d, TABLE(split_words(d.body)) w;

The second form is an implicit lateral join: the function runs once for each row of docs and its output rows are joined back to that row.

LanguageHow the UDTF is written
SQLRETURNS TABLE (...) AS 'SELECT ...', a query that can use the arguments
JavaScriptAn object with processRow(row, rowWriter, context), optional initialize and finalize; rowWriter.writeRow(...) emits rows
PythonA handler class with process, optional __init__ and end_partition; process returns or yields tuples
JavaA handler class with process, optional constructor and endPartition, and a getOutputClass method that describes the output
CREATE FUNCTION split_words(s VARCHAR)
  RETURNS TABLE (word VARCHAR)
  LANGUAGE PYTHON
  RUNTIME_VERSION = '3.11'
  HANDLER = 'Splitter'
AS $$
class Splitter:
    def process(self, s):
        for w in s.split():
            yield (w,)
$$;

The lifecycle is what the exam tests: the handler object is created, process is called for each input row, and end_partition (or finalize) runs after the last row of a partition and can emit extra rows, such as a total.

Partitioning

Snowflake can run a UDTF on a partition of rows, so the handler sees all rows of one group together.

SELECT *
FROM sales_rows,
     TABLE(running_total(amount) OVER (PARTITION BY customer_id ORDER BY order_date));

With PARTITION BY, the handler is created once per partition and end_partition fires at the partition boundary. Add ORDER BY inside OVER when row order matters. Without a partition clause, Snowflake decides how rows are grouped and processed in parallel.

Exam tip

"Return one row per word, one row per array element, or add a summary row per customer" are UDTF clues. If the output is one value per group, think UDAF instead.

UDAFs

A UDAF is written in Python and registered with CREATE AGGREGATE FUNCTION. Its handler class has a state that Snowflake builds in parallel and then combines, so it must implement five members.

MemberJob
__init__Create the empty state
aggregate_stateA property that returns the current state so Snowflake can move it between nodes; it must be serializable
accumulate(self, value)Fold one input row into the state
merge(self, other_state)Combine another partial state into this one
finish(self)Return the final result
CREATE AGGREGATE FUNCTION my_sum(v INT)
  RETURNS INT
  LANGUAGE PYTHON
  RUNTIME_VERSION = '3.11'
  HANDLER = 'MySum'
AS $$
class MySum:
    def __init__(self):
        self._total = 0
    @property
    def aggregate_state(self):
        return self._total
    def accumulate(self, v):
        self._total += v
    def merge(self, other):
        self._total += other
    def finish(self):
        return self._total
$$;

SELECT region, my_sum(amount) FROM orders GROUP BY region;
Partition ArowsaccumulateState AmergeCombined stateone objectfinishFinal valuePartition BrowsaccumulateState BmergeCombined state

Because partial states are merged, the aggregation must be associative: merging in any order gives the same answer. A median cannot simply keep a running number; its state must carry enough data, such as a list of values.

You can also register a UDAF from Snowpark in Python with session.udaf.register.

Watch out

Forgetting merge or storing a state that cannot be serialized breaks parallel aggregation. The state, not the instance, is what travels between nodes.

UDF kinds side by side

KindRows inRows outCalled
Scalar UDF11 valueIn any expression
UDTF1 or a partition0 to manyFROM TABLE(f(...))
UDAFMany1 valueWith GROUP BY

Common mistakes

Calling a UDTF in the SELECT list. It belongs in FROM, wrapped in TABLE(...).

Expecting row order without a partition clause. Rows are processed in parallel with no guaranteed grouping; add PARTITION BY and ORDER BY for ordered logic.

Writing a UDAF in JavaScript or SQL. UDAFs are Python only.

Skipping merge. Parallel partial states must be combinable.

What to remember for the exam

  • UDTF: RETURNS TABLE (...), called as TABLE(f(...)), one row in and many rows out.
  • UDTF handlers use process per row and end_partition or finalize per partition.
  • OVER (PARTITION BY ...) gives the handler whole groups.
  • UDAF: Python, CREATE AGGREGATE FUNCTION, with accumulate, merge, finish and aggregate_state.
  • UDAFs run with GROUP BY and must be mergeable.

Check yourself

  • Which function type turns each JSON array element of a row into its own output row?

    A UDTF, called with TABLE in the FROM clause.

  • What does merge do in a UDAF?

    It combines a partial state from another partition into the current state, enabling parallel aggregation.

  • How do you give a UDTF all rows of one customer together?

    Call it with OVER (PARTITION BY customer_id) so one handler instance processes the partition.

Topic 2 of 14 in Data Transformation

Test yourself on Data Transformation

The full Snowflake DEA-C02 question bank has 709 questions, with timed practice exams and an explanation for every answer.

This study guide is independent and is not affiliated with, endorsed by, or sponsored by Snowflake. Vendor names, certification names and exam codes are trademarks of their respective owners and are used for identification only.