COF-C03 Question 33
Single answerUser-Defined Functions (UDFs)A data engineering team stores customer event payloads in a VARIANT column named EVENT_DATA. Analysts frequently need the customer's loyalty tier from the JSON path $.customer.profile.tier, but many dashboards repeat the same expression and handle missing values inconsistently. The team wants a reusable database object that can be called directly in SQL queries, accepts a VARIANT input, and returns a VARCHAR tier value or NULL when the path does not exist. Which solution best meets this requirement?
- A
Create a SQL scalar UDF that accepts a VARIANT argument and returns VARCHAR using an expression such as EVENT_DATA:customer.profile.tier::VARCHAR
- B
Create a user-defined table function (UDTF) that accepts a VARIANT argument and returns one row containing the tier as VARCHAR
- C
Create an external function because JSON path extraction from VARIANT is not supported inside Snowflake UDFs
- D
Create a JavaScript stored procedure that accepts a VARIANT argument and returns the tier, then call it inline from 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 for reusable logic that returns a single value and can be invoked directly in SQL statements. Because the source column is VARIANT, Snowflake's native support for semi-structured data makes path extraction straightforward using dot or colon notation, and casting the result to VARCHAR satisfies the required return type. If the JSON path does not exist, Snowflake returns NULL for that expression, which aligns with the scenario. A UDTF would be unnecessarily complex because the requirement is not to return a result set. An external function is inappropriate because no remote service is needed; Snowflake already supports querying VARIANT data natively. A stored procedure is also not suitable because procedures are not designed to behave like inline scalar expressions in SELECT lists or predicates. This aligns with Snowflake documentation and best practices for choosing between scalar UDFs, table functions, external functions, and stored procedures.
- A. Correct.
Correct. A SQL scalar UDF is the appropriate choice when the requirement is to encapsulate reusable logic that takes one input value and returns one output value usable directly in SQL expressions. Snowflake supports scalar UDFs that accept VARIANT arguments, and semi-structured data can be queried with path notation such as EVENT_DATA:customer.profile.tier. Casting to VARCHAR returns the expected string value, and if the path is missing, the expression evaluates to NULL, which matches the requirement.
- B. Incorrect.
Incorrect. A UDTF returns a set of rows, which is useful when one input expands into multiple output rows or columns. In this scenario, the team needs a single reusable value that can be called directly in projections and filters, so a scalar UDF is simpler and more appropriate. Choosing a UDTF here reflects the misconception that all reusable SQL logic should be implemented as table functions.
- C. Incorrect.
Incorrect. External functions are used to call remote services outside Snowflake, typically through API integration. They are not required for JSON path extraction from VARIANT data. Snowflake natively supports semi-structured data access and allows that logic inside SQL UDFs. This distractor targets the misconception that specialized parsing of JSON requires off-platform processing.
- D. Incorrect.
Incorrect. Stored procedures are designed for procedural logic and administrative workflows, not for inline use as expressions in standard SELECT statements. Analysts needing a reusable expression in dashboards and queries should use a scalar UDF instead. This option may appeal to users who know procedures can contain logic, but procedures do not fit the requirement for direct use within SQL query expressions.