COF-C03 Question 297
Single answerUsing dataA data engineering team loads raw event files into a Snowflake table named EVENTS_RAW. The table contains a VARIANT column called PAYLOAD with semi-structured JSON data. Analysts frequently need to report on the customer ID and order total from records shaped like {"customer":{"id":123},"order":{"total":49.95}}. The team wants a query that returns these values as relational columns and ensures the order total is treated as a numeric value for aggregation. Which query best meets this requirement?
- A
SELECT PAYLOAD:customer:id AS customer_id, PAYLOAD:order:total AS order_total FROM EVENTS_RAW;
- B
SELECT PAYLOAD.customer.id AS customer_id, PAYLOAD.order.total AS order_total FROM EVENTS_RAW;
- C
SELECT PAYLOAD:customer.id::NUMBER AS customer_id, PAYLOAD:order.total::NUMBER(10,2) AS order_total FROM EVENTS_RAW;
- D
SELECT GET(PAYLOAD,'customer'):'id'::NUMBER AS customer_id, GET(PAYLOAD,'order'):'total'::NUMBER(10,2) AS order_total FROM EVENTS_RAW;
Show answer and explanation
Correct answer: D
Explanation
In Snowflake, semi-structured data stored in VARIANT can be queried using path notation or functions such as GET. When values from VARIANT are needed for joins, filtering, or aggregation, it is a best practice to cast them to the expected SQL data type rather than leave them as semi-structured values. For this scenario, the key requirement is not just extracting nested JSON fields, but returning order_total as a numeric value that can be reliably summed or averaged. Snowflake documentation on querying semi-structured data describes using path expressions on VARIANT columns and casting extracted values to relational types when needed. While multiple syntactic approaches can work in Snowflake, the correct answer here is the one that clearly and validly extracts the nested values and performs the required numeric casts.
- A. Incorrect.
Incorrect. Snowflake uses colon notation to traverse semi-structured data in VARIANT, so PAYLOAD:customer:id and PAYLOAD:order:total are valid path expressions. However, the values are still returned as semi-structured values unless explicitly cast. Since the requirement states that order_total must be treated as a numeric value for aggregation, this query does not fully meet the requirement.
- B. Incorrect.
Incorrect. Dot notation in this form is not the standard way to directly traverse VARIANT columns in Snowflake SQL. A common misconception is to treat VARIANT like an object in programming languages. In Snowflake, path traversal is typically done with colon notation from the VARIANT column, optionally combined with dot or bracket notation after the first level. As written, this is not the best correct query for the scenario.
- C. Incorrect.
Incorrect. This option is close, because it does cast the extracted values. However, the path syntax is not correctly formed for the nested fields shown. After referencing the VARIANT column, the path should be expressed consistently using valid Snowflake semi-structured path notation. This option mixes path elements in a way that is not the best valid answer for the given JSON example.
- D. Correct.
Correct. GET retrieves the object at the specified key from the VARIANT value, and the subsequent path extraction accesses the nested field. Casting with ::NUMBER and ::NUMBER(10,2) converts the semi-structured values into relational numeric columns, which is appropriate when analysts need to aggregate order totals. This query satisfies both requirements: extracting nested JSON fields and returning the order total as a numeric type.