SnowPro Associate: Platform Question 216
Single answer○ Semi-structured dataA retail company loads clickstream events from JSON files into a Snowflake table named EVENTS_RAW with a single column named EVENT_DATA of type VARIANT. Analysts need a query that returns the customer ID as a number and the page URL as text for rows where the event type is "page_view". Which query correctly extracts the values from the semi-structured data and applies the filter?
- A
SELECT EVENT_DATA:customer.id::NUMBER AS customer_id, EVENT_DATA:page.url::STRING AS page_url FROM EVENTS_RAW WHERE EVENT_DATA:event_type::STRING = 'page_view';
- B
SELECT PARSE_JSON(EVENT_DATA):customer.id::NUMBER AS customer_id, PARSE_JSON(EVENT_DATA):page.url::STRING AS page_url FROM EVENTS_RAW WHERE PARSE_JSON(EVENT_DATA):event_type = 'page_view';
- C
SELECT EVENT_DATA['customer']['id'] AS customer_id, EVENT_DATA['page']['url'] AS page_url FROM EVENTS_RAW WHERE EVENT_DATA['event_type'] = 'page_view'::STRING;
- D
SELECT TO_VARIANT(EVENT_DATA):customer:id::NUMBER AS customer_id, TO_VARIANT(EVENT_DATA):page:url::STRING AS page_url FROM EVENTS_RAW WHERE TO_VARIANT(EVENT_DATA):event_type::STRING = 'page_view';
Show answer and explanation
Correct answer: A
Explanation
In Snowflake, semi-structured JSON data is commonly stored in a VARIANT column. Once data is in VARIANT, nested elements can be accessed directly using path notation with a colon after the column name and dots for nested object keys, for example EVENT_DATA:customer.id. Because extracted values remain semi-structured unless cast, best practice is to explicitly cast to the needed SQL type, such as ::NUMBER or ::STRING, especially when returning data for reporting or filtering. PARSE_JSON is intended for converting JSON text into VARIANT during loading or transformation, not for re-parsing values that are already VARIANT. These behaviors align with Snowflake documentation on querying semi-structured data and using VARIANT, OBJECT, and ARRAY types.
- A. Correct.
Correct. EVENT_DATA is already stored as VARIANT, so Snowflake's path notation can be used directly to access nested JSON elements. Casting with ::NUMBER and ::STRING is the appropriate way to return typed relational values. Filtering with EVENT_DATA:event_type::STRING = 'page_view' is also valid and commonly used when querying semi-structured data.
- B. Incorrect.
Incorrect. PARSE_JSON is used to convert a string containing JSON into a VARIANT value. Since EVENT_DATA is already a VARIANT column, calling PARSE_JSON on it is unnecessary and not the correct approach in this scenario. This distractor reflects the common misconception that JSON functions must always be applied before querying nested attributes.
- C. Incorrect.
Incorrect. Bracket notation can be used with semi-structured data in Snowflake, but this option does not cast the extracted values to the requested relational types. The question specifically requires the customer ID as a number and the page URL as text. In addition, the filter expression is awkwardly written and may lead candidates to ignore the need for explicit casting when producing typed output.
- D. Incorrect.
Incorrect. TO_VARIANT converts a value to VARIANT, but EVENT_DATA is already VARIANT, so this conversion is redundant. More importantly, the path syntax shown here is invalid for nested object traversal because Snowflake uses dot notation after a path element, such as EVENT_DATA:customer.id, not EVENT_DATA:customer:id.