ARA-C01 Question 368
Single answerUser-Defined Table Functions (UDTFs)A data engineering team stores application logs in a Snowflake table with columns EVENT_ID, EVENT_TS, and PAYLOAD, where PAYLOAD contains semi-structured JSON. Different event types include arrays of varying length under PAYLOAD:items, and downstream analysts need a reusable object that returns one row per array element while preserving the originating EVENT_ID and EVENT_TS. The architects want a solution that can be invoked in SQL, composed in joins, and encapsulate the row-expansion logic for use across multiple schemas. Which approach best meets these requirements?
- A
Create a user-defined table function (UDTF) that accepts the VARIANT payload and returns a table of item-level rows, then invoke it in the FROM clause with the TABLE(...) syntax alongside the base event row.
- B
Create a scalar SQL UDF that returns a VARIANT array of item objects, then rely on analysts to query the returned value directly wherever row expansion is needed.
- C
Create a stored procedure that loops through the JSON array and inserts expanded rows into a temporary table each time analysts need the data.
- D
Create a user-defined aggregate function (UDAF) that collects the payload array elements and use it in the SELECT list to emit one row per item.
Show answer and explanation
Correct answer: A
Explanation
The best answer is the UDTF approach because Snowflake UDTFs are intended for logic that returns a set of rows and columns, making them appropriate when semi-structured data such as arrays in VARIANT must be transformed into relational output. They are invoked with TABLE(...) in the FROM clause, which enables composition with joins and filtering in standard SQL. This is more appropriate than a scalar UDF, which returns only a single value; a stored procedure, which is procedural and less composable in SQL queries; or a UDAF, which performs aggregation rather than row expansion. In practice, architects often pair UDTFs with semi-structured processing patterns when they need reusable table-producing logic for downstream consumers. This aligns with Snowflake documentation on table functions and SQL query composition, where UDTFs are treated as first-class relational sources.
- A. Correct.
Correct. A UDTF is specifically designed to return tabular results with zero, one, or many rows for each input row. This is the right fit when the requirement is to encapsulate row-expansion logic, preserve context columns by joining or correlating with the source row, and make the logic reusable in SQL. In Snowflake, UDTFs are invoked in the FROM clause using TABLE(...), which allows them to participate naturally in joins and other relational operations. This matches the scenario's need for reusable item-level expansion from semi-structured data.
- B. Incorrect.
Incorrect. A scalar UDF returns a single value, not a relational result set. While it could return a VARIANT containing an array, that does not satisfy the requirement to expose reusable row-level logic directly as a table-producing object. Analysts would still need to repeat flattening logic themselves, which undermines the goal of encapsulation and reuse. This option reflects the common misconception that returning a complex value is equivalent to returning rows.
- C. Incorrect.
Incorrect. A stored procedure can orchestrate SQL and procedural logic, but it is not the best fit for a reusable relational object that analysts can invoke directly in SQL joins and FROM clauses. Using a procedure to populate a temporary table adds operational complexity, introduces procedural side effects, and is less composable than a UDTF. This approach may work operationally, but it does not best meet the architectural requirement for SQL-native reuse.
- D. Incorrect.
Incorrect. A UDAF is intended to aggregate multiple input rows into a summarized result, not expand one input row into multiple output rows. The scenario requires the opposite pattern: taking a row containing an array and emitting one row per array element. This distractor targets confusion between table-producing functions and aggregate functions.