DAA-C01 Question 132
Single answerHandle nullsA retail analytics team stores clickstream events in a Snowflake table with a VARIANT column named EVENT_DATA. The JSON payload contains an optional field, coupon_code. Analysts need a query that returns only rows where coupon_code is truly missing or explicitly set to JSON null, but must exclude rows where coupon_code exists as an empty string (''). Which expression should they use in the WHERE clause?
- A
EVENT_DATA:coupon_code IS NULL
- B
IS_NULL_VALUE(EVENT_DATA:coupon_code)
- C
COALESCE(EVENT_DATA:coupon_code::STRING, '') = ''
- D
EVENT_DATA:coupon_code::STRING = '' OR EVENT_DATA:coupon_code IS NULL
Show answer and explanation
Correct answer: A
Explanation
This question tests practical null handling in Snowflake semi-structured data. A key distinction is between SQL NULL, JSON null, and ordinary values such as an empty string. When navigating a VARIANT path like EVENT_DATA:coupon_code, a missing key yields SQL NULL. A JSON null can also be detected in Snowflake, but for a simple filter that should include both missing and null-valued keys while excluding empty strings, IS NULL is the most appropriate predicate. IS_NULL_VALUE is narrower because it checks specifically for JSON null stored in a VARIANT and does not cover absent paths. Best practice is to choose predicates that align precisely with the business rule rather than masking values with COALESCE, which can accidentally collapse distinct cases such as NULL and ''. This behavior is consistent with Snowflake documentation for querying semi-structured data, NULL values, and functions such as IS_NULL_VALUE and COALESCE.
- A. Correct.
Correct. In Snowflake, when querying semi-structured data, accessing a missing path returns SQL NULL, and a JSON null value is also treated as NULL in SQL predicate evaluation for IS NULL. This condition therefore captures both cases the analysts want: the field is absent or explicitly set to JSON null. It does not match an empty string, because '' is a non-NULL string value.
- B. Incorrect.
Incorrect. IS_NULL_VALUE is used to test whether a VARIANT contains a JSON null value specifically. It does not return TRUE for a missing path, which evaluates to SQL NULL instead. Using only this function would miss rows where coupon_code is absent entirely.
- C. Incorrect.
Incorrect. This expression converts both missing values and JSON nulls to an empty string through COALESCE, but it also matches rows where coupon_code is actually the empty string. The requirement is to exclude empty strings, so this logic is too broad.
- D. Incorrect.
Incorrect. This condition explicitly includes empty strings with EVENT_DATA:coupon_code::STRING = ''. Although the OR ... IS NULL portion would capture missing and JSON null values, the first part violates the requirement to exclude empty strings.