SnowPro Associate: Platform Question 203
Single answer○ Semi-structured dataA retail company loads clickstream events from JSON files into a Snowflake table named RAW_EVENTS with a single VARIANT column called EVENT_DATA. Analysts need a query that returns the customer ID as a string and the numeric order total for only those events where the JSON attribute eventType equals "purchase". Which query correctly extracts the values and applies the filter?
- A
SELECT EVENT_DATA:customerId::STRING AS customer_id, EVENT_DATA:orderTotal::NUMBER AS order_total FROM RAW_EVENTS WHERE EVENT_DATA:eventType::STRING = 'purchase';
- B
SELECT PARSE_JSON(EVENT_DATA):customerId AS customer_id, PARSE_JSON(EVENT_DATA):orderTotal AS order_total FROM RAW_EVENTS WHERE PARSE_JSON(EVENT_DATA):eventType = 'purchase';
- C
SELECT EVENT_DATA.customerId AS customer_id, EVENT_DATA.orderTotal AS order_total FROM RAW_EVENTS WHERE EVENT_DATA.eventType = 'purchase';
- D
SELECT GET_PATH(EVENT_DATA, 'customerId') AS customer_id, GET_PATH(EVENT_DATA, 'orderTotal') AS order_total FROM RAW_EVENTS WHERE GET(EVENT_DATA, 'eventType') = 'purchase';
Show answer and explanation
Correct answer: A
Explanation
Snowflake stores semi-structured data such as JSON in the VARIANT type. When querying VARIANT data, the most common and readable approach is path notation, for example column:field. Because extracted values remain semi-structured unless converted, explicit casting is a best practice when a query requires a SQL type such as STRING or NUMBER. In this scenario, EVENT_DATA:customerId::STRING and EVENT_DATA:orderTotal::NUMBER correctly return typed values, and EVENT_DATA:eventType::STRING = 'purchase' correctly filters purchase events. This aligns with Snowflake documentation on querying semi-structured data, path notation, and casting values extracted from VARIANT columns.
- A. Correct.
Correct. For semi-structured data stored in a VARIANT column, Snowflake supports path notation using a colon to access top-level JSON elements, such as EVENT_DATA:customerId. Casting with ::STRING and ::NUMBER is appropriate when analysts need scalar values in specific SQL data types. Filtering on EVENT_DATA:eventType::STRING = 'purchase' is also the correct way to compare the JSON string value to a SQL string literal.
- B. Incorrect.
Incorrect. PARSE_JSON is used to convert a string containing JSON into VARIANT. In this scenario, EVENT_DATA is already stored as VARIANT, so calling PARSE_JSON(EVENT_DATA) is unnecessary and not the correct pattern. This distractor reflects the common misconception that JSON must always be parsed at query time even after it has already been loaded into a semi-structured column.
- C. Incorrect.
Incorrect. Dot notation in this form is not the standard SQL path syntax for extracting fields from a VARIANT column in Snowflake. Snowflake uses colon-based path notation for the first-level element, for example EVENT_DATA:customerId, with additional levels accessed by dot or bracket notation as needed. This option also omits explicit casting, which is typically needed when returning typed scalar values for reporting or filtering.
- D. Incorrect.
Incorrect. GET and GET_PATH can be used with semi-structured data, but this option is incomplete for the stated requirement because it does not cast the extracted values to STRING and NUMBER. In addition, comparing GET(EVENT_DATA, 'eventType') directly to a SQL string can lead to type mismatch issues because the extracted result remains semi-structured unless cast. This distractor targets the misconception that extraction alone automatically returns a native SQL type.