COF-C03 Question 301
Single answerSemi-structuredA retail company loads clickstream data 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 event type and customer ID for only those records where the JSON attribute customer.id exists and is not JSON null. Which query best meets this requirement?
- A
SELECT EVENT_DATA:eventType::STRING AS event_type, EVENT_DATA:customer.id::STRING AS customer_id FROM RAW_EVENTS WHERE EVENT_DATA:customer.id IS NOT NULL;
- B
SELECT EVENT_DATA:eventType::STRING AS event_type, EVENT_DATA:customer.id::STRING AS customer_id FROM RAW_EVENTS WHERE IS_NULL_VALUE(EVENT_DATA:customer.id) = FALSE;
- C
SELECT EVENT_DATA['eventType']::STRING AS event_type, EVENT_DATA['customer']['id']::STRING AS customer_id FROM RAW_EVENTS WHERE EVENT_DATA['customer']['id'] <> NULL;
- D
SELECT GET_PATH(EVENT_DATA, 'eventType')::STRING AS event_type, GET_PATH(EVENT_DATA, 'customer.id')::STRING AS customer_id FROM RAW_EVENTS WHERE GET(EVENT_DATA, 'customer.id') IS NOT NULL;
Show answer and explanation
Correct answer: B
Explanation
This question tests practical querying of semi-structured data stored in VARIANT, a core Snowflake skill. Snowflake supports path traversal with colon and dot notation, bracket notation, and functions such as GET and GET_PATH. A key nuance is the distinction between SQL NULL and JSON null inside VARIANT values. For semi-structured data, IS_NULL_VALUE is the documented function used to identify JSON null values. Candidates should also recognize that standard SQL comparisons such as <> NULL are invalid for null checks. In Snowflake best practice, when working with nested JSON attributes, use path expressions like EVENT_DATA:customer.id and explicitly account for JSON null behavior when filtering data.
- A. Incorrect.
Incorrect. In Snowflake semi-structured data, SQL NULL and JSON null are not the same thing. A path expression that resolves to a JSON null value can still behave differently from a missing path, and filtering only with IS NOT NULL does not reliably enforce the requirement that the attribute exists and is not JSON null. This option may include rows where the key exists but contains a JSON null value, which violates the requirement.
- B. Correct.
Correct. IS_NULL_VALUE is designed to test whether a VARIANT value is a JSON null. Using EVENT_DATA:customer.id in the predicate targets the nested attribute, and filtering with IS_NULL_VALUE(...)=FALSE returns rows where the element is present and not JSON null. This is the safest option among the choices for distinguishing JSON null handling in semi-structured data while extracting the values with path notation and casting them to STRING.
- C. Incorrect.
Incorrect. Comparing anything to NULL with <> NULL does not work in SQL because NULL comparisons using = or <> evaluate to UNKNOWN rather than TRUE. The correct approach would require IS NULL / IS NOT NULL semantics or a function appropriate for VARIANT and JSON null handling. This option reflects a common SQL mistake.
- D. Incorrect.
Incorrect. GET on a VARIANT object retrieves a direct child element by key. Passing 'customer.id' to GET treats it as a literal key name, not as a nested path, so it would not correctly access the nested customer.id attribute in typical JSON documents. While GET_PATH can navigate paths, this option inconsistently uses GET for filtering and therefore does not correctly implement the requirement.