DAA-C01 Question 190
Single answerUser-Defined Functions (UDFs)A data analyst team stores clickstream events in a VARIANT column named EVENT_DATA. Analysts frequently need to classify each event into a marketing channel based on several nested JSON attributes, and the same logic must be reused in ad hoc queries, views, and BI semantic models. The team wants a solution that is easy to call from SQL, encapsulates the logic in one place, and can return a simple VARCHAR label such as 'PAID_SEARCH', 'EMAIL', or 'DIRECT'. Which approach best meets these requirements in Snowflake?
- A
Create a SQL UDF that accepts the VARIANT payload (or relevant extracted fields) and returns a VARCHAR channel classification.
- B
Create a user-defined table function (UDTF) because any logic applied to semi-structured data must be implemented as a table-returning function.
- C
Create a stored procedure that returns the channel label, because stored procedures are the standard reusable object for scalar transformations in SELECT statements.
- D
Create an external function, because external functions are required whenever logic references values inside a VARIANT column.
Show answer and explanation
Correct answer: A
Explanation
The best answer is to use a scalar SQL UDF. This scenario calls for reusable business logic that returns one classification value per input row and can be invoked easily from SQL in multiple downstream artifacts. Snowflake UDFs are specifically intended for encapsulating reusable function logic, and SQL UDFs are a strong fit when the transformation can be expressed in SQL over Snowflake data types, including semi-structured data such as VARIANT. By contrast, UDTFs are for returning tabular result sets, stored procedures are better for control flow and administrative or multi-step operations, and external functions are for delegating logic to external services. Snowflake documentation on user-defined functions distinguishes scalar UDFs from table functions and stored procedures, and best practice is to choose the simplest native object that matches the required input/output pattern and execution context.
- A. Correct.
Correct. A SQL UDF is the most appropriate choice when the team needs reusable scalar logic that can be invoked directly in SQL expressions across queries, views, and BI-facing models. Since the desired output is a single VARCHAR classification per row, a scalar UDF fits naturally. Snowflake SQL UDFs can accept supported input types such as VARIANT and return a scalar value, making them well suited for encapsulating repeated classification logic over semi-structured data.
- B. Incorrect.
Incorrect. A UDTF is used when a function needs to return a set of rows, not a single scalar value. In this scenario, the requirement is to derive one channel label per event row. Choosing a UDTF would add unnecessary complexity and does not align with the scalar output requirement. A common misconception is that semi-structured inputs automatically require table functions, but VARIANT can be handled directly in SQL expressions and scalar UDFs.
- C. Incorrect.
Incorrect. Stored procedures are designed for procedural operations and orchestration, not for use as scalar expressions embedded directly in SELECT lists or predicates the way a UDF can be. While procedures can contain business logic, they are not the standard mechanism for row-by-row reusable scalar transformations in analytic SQL. Candidates may pick this because procedures also encapsulate logic, but they are not the best fit for this use case.
- D. Incorrect.
Incorrect. External functions are used to call out to remote services outside Snowflake, typically via API integration. They are not required simply because the source data is stored in VARIANT. Introducing an external function here would increase operational complexity, latency, and dependencies without solving a problem that Snowflake SQL UDFs already handle natively.