CertSafari
    Snowflake SnowPro Advanced: Data Analyst (DAA-C01)· Lessons

    Domain 3 · Lesson 13/19

    Snowflake UDFs and UDTFs: scalar and table functions

    Use SQL extensibility features.

    14 min read
    8% of exam
    8 sources
    Published 20 Oct 2026
    Docs as of 18 Oct 2026

    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:

    UDF variations and what each returns
    VariationBehaviour
    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 UDFReceives batches of rows as Pandas DataFrames, returns Pandas arrays or Series
    Vectorized UDTFReceives 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.

    A scalar UDF whose logic lives in a Python handler functionsql
    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
    $$;

    Checkpoint 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?

    Sources123

    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.

    Where each handler language can live, and whether the resulting UDF is sharable
    Handler languageHandler locationSharable
    JavaIn-line or stagedNo
    JavaScriptIn-lineYes
    PythonIn-line or stagedNo
    ScalaIn-line or stagedNo
    SQLIn-lineYes

    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?

    Sources415

    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.

    A minimal SQL UDTF and the TABLE() call that reads itsql
    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.

    Passing a column from the left-hand table into a UDTFsql
    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?

    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)

    0 of 2 selected

    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:

    1. 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.
    2. process runs once for each row in the partition. It can update the state and yield output tuples.
    3. end_partition is optional. It runs once after the last row of the partition, takes no arguments other than self, and can yield summary rows.
    Per-row output from process, plus a partition total from end_partitionpython
    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?

    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)

    0 of 2 selected

    Sources78

    Sources

    (8)

    Every claim above is drawn from one of these pages, quoted as it was written on the date shown.

    1. 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. 2.
      “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. 3.
      “Materialized views impact your costs for both storage and compute resources”
      ↩︎ Scalar UDFs and when a function is the right object
    4. 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. 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
    6. 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
    7. 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

    Continue to part 2 of 3

    Snowflake stored procedures: rights, exceptions and async child jobs

    Spotted a mistake, or was something unclear? Tell us.