DAA-C01 Question 191
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_PATH. They want to enrich each session with a variable number of derived rows representing navigation transitions, such as '/home -> /search' and '/search -> /product'. The team wants to reuse this logic in SQL queries and join the generated rows back to SESSION metadata. Which approach best meets this requirement in Snowflake?
- A
Create a user-defined table function (UDTF) that accepts session-level input and returns one row per derived transition, then invoke it in the FROM clause with TABLE(...) so its output can be joined to other tables.
- B
Create a scalar user-defined function (UDF) that returns a VARCHAR containing all transitions concatenated together, then join that string directly to SESSION metadata.
- C
Create a stored procedure that loops through each session and returns a result set, then reference the procedure directly in the FROM clause of ad hoc analytical queries.
- D
Create a view with a single SELECT statement and pass SESSION_ID as a runtime parameter to the view so it emits the transition rows for that session.
Show answer and explanation
Correct answer: A
Explanation
The best answer is to use a user-defined table function. In Snowflake, UDTFs are specifically intended to return tabular results and are invoked in the FROM clause using TABLE(function_call(...)). This makes them a strong fit when business logic must generate multiple rows per input, such as session navigation transitions, tokenization, parsing, or exploding derived structures for analysis. By contrast, scalar UDFs return a single value and are unsuitable when the consumer needs one output row per derived element. Stored procedures are procedural and not the preferred mechanism for reusable, composable SQL table sources. Views are reusable but not parameterized. This aligns with Snowflake documentation and best practices around choosing UDFs for single-value expressions, UDTFs for row sets, and procedures for imperative workflows rather than relational query composition.
- A. Correct.
Correct. A UDTF is designed for logic that returns 0, 1, or many rows for each invocation, which matches the requirement to generate a variable number of transition rows per session. In Snowflake, UDTFs are queried in the FROM clause by wrapping the function call in TABLE(...). This makes the output relational and easy to join to SESSION metadata or other tables. This is the appropriate pattern when derived logic needs to emit multiple rows and be reusable in SQL.
- B. Incorrect.
Incorrect. A scalar UDF returns a single value, not a table. Although it could concatenate transitions into one string, that would not satisfy the requirement to produce one row per transition for downstream joins and analysis. This option reflects a common misconception that any reusable function can serve as a table source; in Snowflake, only table functions return tabular results.
- C. Incorrect.
Incorrect. Stored procedures can encapsulate procedural logic, but they are not used as relational table sources in standard SQL queries the way UDTFs are. Even if a procedure can produce a result set in some contexts, it is not the right construct for reusable row-generating logic intended to participate naturally in SELECT ... FROM ... JOIN patterns. This distractor targets confusion between procedural objects and queryable table functions.
- D. Incorrect.
Incorrect. Standard views in Snowflake do not accept runtime parameters. A view can encapsulate a fixed query, but it cannot be invoked with SESSION_ID as an argument to dynamically emit rows for a specific input the way a UDTF can. This option reflects a common misunderstanding between parameterized logic and static relational abstractions.