SnowPro Associate: Platform Question 214
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 to report on the customer ID, event type, and purchase amount for only purchase events. Some JSON records do not contain a purchaseAmount field. Which query best returns the required data while preserving rows where purchaseAmount is missing?
- A
SELECT EVENT_DATA:customerId::STRING AS customer_id, EVENT_DATA:eventType::STRING AS event_type, EVENT_DATA:purchaseAmount::NUMBER AS purchase_amount FROM RAW_EVENTS WHERE EVENT_DATA:eventType::STRING = 'purchase';
- B
SELECT PARSE_JSON(EVENT_DATA):customerId::STRING AS customer_id, PARSE_JSON(EVENT_DATA):eventType::STRING AS event_type, PARSE_JSON(EVENT_DATA):purchaseAmount::NUMBER AS purchase_amount FROM RAW_EVENTS WHERE PARSE_JSON(EVENT_DATA):eventType::STRING = 'purchase';
- C
SELECT GET(EVENT_DATA, 'customerId')::STRING AS customer_id, GET(EVENT_DATA, 'eventType')::STRING AS event_type, GET(EVENT_DATA, 'purchaseAmount')::NUMBER AS purchase_amount FROM RAW_EVENTS WHERE GET(EVENT_DATA, 'purchaseAmount') IS NOT NULL;
- D
SELECT EVENT_DATA.customerId AS customer_id, EVENT_DATA.eventType AS event_type, EVENT_DATA.purchaseAmount AS purchase_amount FROM RAW_EVENTS WHERE EVENT_DATA.eventType = 'purchase';
Show answer and explanation
Correct answer: A
Explanation
In Snowflake, semi-structured data such as JSON is commonly stored in VARIANT columns. When querying VARIANT data, the recommended approach is to use path notation like column:key or column:key.subkey, then cast the extracted values to the required SQL type. If an attribute is missing in a VARIANT object, Snowflake returns NULL for that path, which makes it possible to preserve rows even when some fields are absent. In this scenario, filtering should be based on eventType = 'purchase', not on purchaseAmount, because purchaseAmount may legitimately be missing in some purchase records. PARSE_JSON is appropriate when starting with a JSON string, not when the data is already stored as VARIANT. This aligns 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 semi-structured data path notation can be used directly. Casting with ::STRING and ::NUMBER returns scalar values in usable SQL types. Filtering on EVENT_DATA:eventType::STRING = 'purchase' returns only purchase events, and records missing purchaseAmount are still preserved because the filter is based on eventType, not purchaseAmount. Missing attributes in VARIANT evaluate to NULL when selected.
- B. Incorrect.
Incorrect. PARSE_JSON is used to convert a string containing JSON into VARIANT. Since EVENT_DATA is already a VARIANT column, calling PARSE_JSON on it is unnecessary and not the appropriate pattern. A candidate might choose this because PARSE_JSON is commonly used during ingestion or when working with VARCHAR JSON strings, but not when querying existing VARIANT data.
- C. Incorrect.
Incorrect. GET can retrieve object fields from VARIANT, so part of this query is syntactically plausible. However, the WHERE clause filters on purchaseAmount being non-NULL, which excludes purchase events where purchaseAmount is missing. That violates the requirement to preserve those rows. This distractor targets the misconception that filtering on the requested field is harmless.
- D. Incorrect.
Incorrect. Snowflake does not use standard dot notation for traversing VARIANT values in SQL queries. Semi-structured element access is typically done with colon path notation, bracket notation, or functions such as GET. In addition, without explicit casting, the selected values remain semi-structured values rather than clean SQL scalar types. This option reflects a common mistake from users familiar with other SQL or document databases.