DAA-C01 Question 102
Single answerUser-Defined Functions (UDFs)A data analyst team stores raw event payloads in a VARIANT column named EVENT_DATA. Many dashboards need the same transformation to extract a nested product category, convert it to uppercase, and return NULL when the path does not exist. To avoid repeating the logic in dozens of queries, the team wants to implement a reusable function in Snowflake. Which approach best meets this requirement while staying aligned with Snowflake UDF capabilities and SQL-based analytics workflows?
- A
Create a SQL scalar UDF that accepts a VARIANT argument and returns VARCHAR using logic such as UPPER(event_data:"product":"category"::STRING).
- B
Create a table UDF because only table UDFs can read nested fields from VARIANT and return NULL when the path is missing.
- C
Create an aggregate UDF so the category extraction can be reused across SELECT statements without repeating the expression.
- D
Create a Python UDF because SQL UDFs cannot reference keys inside VARIANT values or perform string functions like UPPER on extracted values.
Show answer and explanation
Correct answer: A
Explanation
The best answer is to use a SQL scalar UDF because the requirement is to encapsulate a repeated row-level transformation into a reusable function for analytic queries. In Snowflake, scalar UDFs are intended for exactly this use case: accepting input values and returning one value per row. Snowflake SQL can directly query VARIANT data with path notation, cast extracted values to native SQL types, and apply built-in functions such as UPPER. Missing paths in semi-structured data typically evaluate to NULL, which aligns with the stated requirement. Table UDFs are used when a function must return multiple rows or columns, while aggregate UDFs are for combining values across rows. Python UDFs are valid in Snowflake, but best practice is to prefer SQL UDFs for simple SQL-native transformations because they are simpler to maintain and better aligned with standard analytics workflows. Relevant Snowflake documentation includes SQL UDFs, table functions, and querying semi-structured data in VARIANT columns.
- A. Correct.
Correct. A SQL scalar UDF is the most appropriate choice when encapsulating reusable expression logic that returns a single value per row. Snowflake SQL can directly navigate semi-structured data in VARIANT using path notation, cast the extracted value to STRING, and apply string functions such as UPPER. If the path is missing, Snowflake returns NULL for the extraction, which satisfies the requirement without extra complexity.
- B. Incorrect.
Incorrect. A table UDF returns a result set, not a single scalar value, so it is not the best fit for a reusable per-row expression that outputs one VARCHAR value. Snowflake does not require a table UDF to read nested VARIANT fields; SQL expressions and scalar UDFs can do that directly.
- C. Incorrect.
Incorrect. Aggregate UDFs are designed to compute a result over multiple input rows, similar to SUM or AVG. The scenario requires transforming each row's VARIANT payload into a single category value, so an aggregate UDF would be the wrong abstraction.
- D. Incorrect.
Incorrect. Python UDFs can be useful for specialized logic, but they are not required here. Snowflake SQL UDFs fully support semi-structured data access and built-in SQL functions such as UPPER. Choosing Python would add unnecessary complexity and runtime overhead for a straightforward SQL transformation.