DAA-C01 Question 189
Single answerUser-Defined Functions (UDFs)A data analyst team stores customer event payloads in a VARIANT column named RAW_EVENT. The payload schema varies by event type, but analysts repeatedly need a normalized marketing channel value such as "paid_search", "email", or "organic" for use in dashboards and ad hoc SQL. The team wants a reusable solution that can be called directly in SELECT statements across many worksheets without rewriting the parsing logic each time. They also want the function to remain queryable in SQL and avoid unnecessary operational complexity. Which approach is the best fit?
- A
Create a SQL UDF that accepts the VARIANT payload, extracts the relevant attributes, and returns the normalized channel string.
- B
Create a user-defined aggregate function (UDAF) so each query can aggregate all event payloads and derive one channel per row.
- C
Create an external function for the normalization logic because any reusable parsing logic in Snowflake must be implemented outside Snowflake.
- D
Create a stored procedure that loops through rows and writes the normalized channel to a temporary table whenever analysts need to query it.
Show answer and explanation
Correct answer: A
Explanation
The best answer is to create a SQL UDF. In Snowflake, scalar UDFs are designed to encapsulate reusable logic that returns a single value for each invocation. This is ideal when analysts repeatedly need the same transformation from semi-structured data stored in VARIANT. Because the function remains callable directly from SQL, it supports self-service analytics, reduces repeated CASE/path extraction logic, and keeps implementation lightweight.
The other options reflect common misconceptions. UDAFs are for combining values across rows, not transforming each row independently. External functions are useful only when logic must execute in an external service; they are not required for standard parsing or normalization. Stored procedures are better for control flow and operational tasks, but they are not the most natural or efficient way to expose reusable scalar logic to analysts in SELECT statements.
This aligns with Snowflake best practices: use SQL UDFs for reusable scalar expressions in SQL, especially when working with semi-structured data through path extraction and conditional logic. Snowflake documentation on user-defined functions distinguishes scalar UDFs from aggregate functions and stored procedures based on exactly this kind of usage pattern.
- A. Correct.
Correct. A SQL UDF is well suited for encapsulating reusable scalar logic that takes an input such as VARIANT and returns a single derived value. Analysts can call it directly in SELECT, WHERE, GROUP BY, and other SQL expressions, which matches the requirement for repeated use in ad hoc SQL and dashboards. This minimizes duplicated parsing logic while keeping the solution simple and native to Snowflake.
- B. Incorrect.
Incorrect. A UDAF is used for aggregation across multiple rows, not for deriving one scalar value from each individual row's payload. The scenario requires row-level normalization of a channel value from each event, so an aggregate function is the wrong abstraction.
- C. Incorrect.
Incorrect. External functions are used when Snowflake needs to call remote services outside Snowflake, typically through API integration. They add operational complexity and network dependency. For straightforward parsing and normalization of a VARIANT value inside SQL, a native SQL UDF is a better fit.
- D. Incorrect.
Incorrect. Stored procedures are intended for procedural workflows, orchestration, and multi-step operations, not the simplest reusable row-level expression logic in analyst queries. A stored procedure cannot be used as naturally as a scalar function inside a SELECT expression for each row, and writing to temporary tables adds unnecessary complexity.