DAA-C01 Question 103
Single answerUser-Defined Functions (UDFs)A data analyst team stores semi-structured clickstream events in a VARIANT column named EVENT_DATA. Analysts repeatedly need to extract a normalized marketing channel from different JSON paths and apply the same logic across dashboards and ad hoc SQL queries. The team wants to centralize this transformation in Snowflake so it can be called directly from SQL, return a single text value, and remain easy to maintain. Which approach best meets this requirement?
- A
Create a SQL scalar UDF that accepts a VARIANT input, uses Snowflake SQL expressions to inspect the relevant JSON paths, and returns a VARCHAR channel name.
- B
Create a user-defined table function (UDTF) that returns one row per possible channel, then join it back to each event record when only a single channel value is needed.
- C
Create an aggregate UDF so the channel can be derived during GROUP BY operations, even when analysts need the value at the individual event level.
- D
Create a stored procedure that returns a result set containing the channel and require analysts to call it instead of using the logic inline in SELECT statements.
Show answer and explanation
Correct answer: A
Explanation
The best answer is to create a SQL scalar UDF. In Snowflake, scalar UDFs are intended to accept input parameters and return a single value, making them ideal for reusable row-level transformations such as normalizing fields extracted from VARIANT data. This allows analysts to call the function directly in SQL statements used by BI tools, worksheets, and views. A UDTF would be appropriate only if the logic needed to emit multiple rows or columns per input. Aggregate functions are for calculations across sets of rows, not deriving a single attribute from one record. Stored procedures are better suited for workflow orchestration and procedural tasks than inline query expressions. Snowflake documentation and best practices distinguish these object types by return shape and execution pattern: scalar UDFs for single-value expressions, UDTFs for tabular output, and stored procedures for procedural logic.
- A. Correct.
Correct. A SQL scalar UDF is the most appropriate choice when the requirement is to encapsulate reusable logic that takes an input value such as VARIANT and returns a single value such as VARCHAR for use directly in SQL expressions. This matches the scenario exactly: analysts want a centralized transformation callable from SELECT statements, filters, and other SQL logic. Snowflake SQL UDFs are well suited for wrapping JSON path extraction, CASE logic, COALESCE, and casting into a reusable function.
- B. Incorrect.
Incorrect. A UDTF is designed to return a set of rows rather than a single scalar value. While it could be made to work, it is unnecessarily complex and less natural for this use case because each event needs one derived channel value usable inline in standard SQL expressions. Choosing a table function here reflects the misconception that any reusable logic should be implemented as a table-returning object.
- C. Incorrect.
Incorrect. Aggregate functions operate across multiple rows and are intended for summarized calculations, not row-level derivations from a single event payload. The team needs to derive a channel for each event record before or independent of aggregation. This option confuses row-wise transformation requirements with grouped analytic calculations.
- D. Incorrect.
Incorrect. Stored procedures are useful for procedural orchestration, control flow, and administrative or multi-step operations, but they are not the best mechanism for a reusable expression that analysts need directly inside ad hoc queries and dashboards. Analysts typically want to call a function in a SELECT list or WHERE clause, which is what a scalar UDF is designed for. This option reflects the misconception that procedures and UDFs are interchangeable for query-time value derivation.