ARA-C01 Question 369
Single answerUser-Defined Table Functions (UDTFs)A data engineering team stores clickstream events in a VARIANT column named EVENT_JSON. Each record contains an array of product IDs viewed during a session. Multiple reporting teams need a reusable database object that, given one input row, returns one output row per product ID so the results can be joined to a product dimension table in SQL. The architect wants to avoid copying logic into every query and wants the solution to behave like a table source in the FROM clause. Which approach best meets these requirements?
- A
Create a user-defined table function (UDTF) that accepts the VARIANT value and returns a table of product IDs, then invoke it in the FROM clause with the input row.
- B
Create a scalar SQL UDF that returns an ARRAY of product IDs, and join directly to the ARRAY result in the SELECT list.
- C
Create a stored procedure that loops through the array and inserts product IDs into a temporary table for each reporting query.
- D
Create a materialized view on the source table that dynamically executes FLATTEN for each consumer-specific filter at runtime.
Show answer and explanation
Correct answer: A
Explanation
The best choice is a user-defined table function because Snowflake UDTFs are specifically intended to return a set of rows and columns that can be referenced in the FROM clause, making them suitable for reusable row-expansion logic such as parsing arrays from semi-structured data. This is a common architectural pattern when working with VARIANT data and downstream dimensional joins. Scalar UDFs return a single value and therefore do not directly provide tabular output. Stored procedures are useful for orchestration and administrative workflows, but they are not as natural or efficient for inline relational composition in reporting queries. Materialized views are for persisted, incrementally maintained query results and are not a substitute for parameterized table-returning logic. Snowflake documentation on table functions and UDTFs describes their use for returning tabular data, while documentation on semi-structured data commonly pairs row expansion with table-oriented constructs such as FLATTEN or table functions.
- A. Correct.
Correct. A UDTF is designed to return tabular results and can be used as a table source in the FROM clause. This matches the requirement to accept input from each row and emit multiple output rows, such as one row per product ID extracted from a JSON array. It also centralizes reusable logic so reporting teams do not duplicate array-expansion code in every query. In Snowflake, table functions are the appropriate abstraction when one input row must produce zero, one, or many output rows.
- B. Incorrect.
Incorrect. A scalar UDF returns a single value, not a table. Even if that single value is an ARRAY, it does not satisfy the requirement for a reusable object that behaves like a relational table source in the FROM clause. Consumers would still need additional logic, such as FLATTEN, in each query to expand the ARRAY into rows, which defeats the goal of centralizing the row-producing logic.
- C. Incorrect.
Incorrect. A stored procedure is procedural and can orchestrate SQL statements, but it is not the best fit when consumers need a reusable object that can be queried like a table and joined inline in standard SQL. Using a procedure plus temporary tables adds operational complexity, introduces side effects, and is less composable than a UDTF for analytic queries.
- D. Incorrect.
Incorrect. Materialized views do not dynamically execute consumer-specific logic at runtime, and they are intended for precomputed query results subject to Snowflake's materialized view limitations. This option also does not address the requirement for a reusable parameterized object that takes an input row and emits multiple rows per input. The misconception is treating a materialized view as a substitute for row-wise table function behavior.