DAA-C01 Question 192
Single answerUser-Defined Table Functions (UDTFs)A retail analytics team stores clickstream events in a table named WEB_EVENTS with columns SESSION_ID, EVENT_TS, and PAGE_SEQUENCE, where PAGE_SEQUENCE is a VARIANT array of page names viewed during the session. Analysts want a reusable object that, for each input row, returns one row per page view with the page name and its position in the sequence so they can join the expanded results to other tables in ad hoc SQL. The team also wants to call the object directly in the FROM clause and correlate it to columns from WEB_EVENTS. Which approach best meets these requirements?
- A
Create a user-defined table function (UDTF) that accepts PAGE_SEQUENCE and returns a table of PAGE_NAME and POSITION, then invoke it with WEB_EVENTS columns in the FROM clause using TABLE(...)
- B
Create a scalar SQL UDF that accepts PAGE_SEQUENCE and returns a delimited VARCHAR, then use it directly in the FROM clause to produce one row per page
- C
Create a stored procedure that loops through PAGE_SEQUENCE and inserts rows into a temporary table, then select from the procedure result in the same FROM clause
- D
Create a user-defined aggregate function (UDAF) that accepts PAGE_SEQUENCE and emits one row per element when grouped by SESSION_ID
Show answer and explanation
Correct answer: A
Explanation
The best solution is a user-defined table function because Snowflake UDTFs return tabular results and are designed for use in the FROM clause with the TABLE(...) syntax. This is the appropriate pattern when one input row, such as a VARIANT array, must be transformed into multiple output rows that can then participate in joins and downstream analytics. By contrast, scalar UDFs return only one value, stored procedures are not queried as relational row sources in the same way, and UDAFs summarize rather than expand data. In Snowflake documentation, table functions are the documented mechanism for returning a set of rows, and they can be used with correlated input from preceding tables in the FROM clause. In practice, analysts may also consider built-in table functions such as FLATTEN for semi-structured data, but when custom reusable logic and a defined output schema are required, a UDTF is the appropriate choice.
- A. Correct.
Correct. A UDTF is specifically designed to return a set of rows for each input invocation. In Snowflake, table functions are used in the FROM clause through TABLE(...), and they can be correlated with preceding table expressions so each row from WEB_EVENTS can be passed into the function. This matches the requirement to expand each array into multiple rows and make the logic reusable in ad hoc SQL joins.
- B. Incorrect.
Incorrect. A scalar UDF returns a single value, not a result set with multiple rows. While a scalar UDF could potentially serialize array contents into a string, it cannot itself be used as a table source to emit one row per page view in the FROM clause. This option reflects the common misconception that any UDF can be used where a table expression is needed.
- C. Incorrect.
Incorrect. Stored procedures are intended for procedural logic and orchestration, not as relational objects directly queried in the FROM clause like a table function. Even if a procedure writes to a temporary table, that adds operational complexity and does not satisfy the requirement for a reusable object analysts can directly invoke as a correlated table source in ad hoc SQL.
- D. Incorrect.
Incorrect. A UDAF aggregates many input rows into a summary result; it is not intended to expand one input row into multiple output rows. This distractor targets confusion between aggregate functions and table functions. The requirement is row expansion, which is the role of a table function, not an aggregate.