DAA-C01 Question 87
Single answer1.7 Given a scenario, use Snowflake functions.A retail analytics team stores clickstream events in a Snowflake table named EVENTS with the columns SESSION_ID, EVENT_TS, and PAYLOAD (VARIANT). The PAYLOAD column contains JSON such as {"page":"product","sku":"A123","price":"19.99","coupon":null}. Analysts need a query that returns one row per event with these requirements: extract page and sku as text, convert price to a numeric value without failing if the field is missing or contains non-numeric text, and replace a JSON null coupon value with the string 'NO_COUPON'. Which expression set best meets these requirements?
- A
SELECT PAYLOAD:page::STRING AS page, PAYLOAD:sku::STRING AS sku, TRY_TO_DECIMAL(PAYLOAD:price::STRING, 10, 2) AS price, COALESCE(PAYLOAD:coupon::STRING, 'NO_COUPON') AS coupon FROM EVENTS
- B
SELECT GET(PAYLOAD, 'page') AS page, GET(PAYLOAD, 'sku') AS sku, TO_DECIMAL(GET(PAYLOAD, 'price')) AS price, NVL(GET(PAYLOAD, 'coupon'), 'NO_COUPON') AS coupon FROM EVENTS
- C
SELECT PARSE_JSON(PAYLOAD):page::STRING AS page, PARSE_JSON(PAYLOAD):sku::STRING AS sku, TRY_CAST(PAYLOAD:price AS NUMBER(10,2)) AS price, ZEROIFNULL(PAYLOAD:coupon::STRING) AS coupon FROM EVENTS
- D
SELECT PAYLOAD['page']::STRING AS page, PAYLOAD['sku']::STRING AS sku, TO_NUMBER(PAYLOAD['price']::STRING, 10, 2) AS price, IFF(PAYLOAD['coupon'] IS NULL, 'NO_COUPON', PAYLOAD['coupon']) AS coupon FROM EVENTS
Show answer and explanation
Correct answer: A
Explanation
This question tests practical use of Snowflake functions and semi-structured data access. For VARIANT columns, fields can be extracted using path notation like PAYLOAD:field or bracket notation. To return textual values, explicit casting such as ::STRING is appropriate. When converting potentially dirty data, Snowflake best practice is to use TRY_TO_NUMBER, TRY_TO_DECIMAL, or related TRY_ conversion functions so malformed values produce NULL instead of causing the query to fail. For null handling, COALESCE or NVL can replace NULL values, but you must ensure the expression types are appropriate. In semi-structured JSON, a JSON null commonly becomes usable as SQL NULL after casting, making COALESCE(PAYLOAD:coupon::STRING, 'NO_COUPON') a reliable pattern. These behaviors are consistent with Snowflake documentation for semi-structured data traversal, data type conversion functions, and conditional/null-handling functions.
- A. Correct.
Correct. Snowflake supports extracting JSON elements from a VARIANT using path notation such as PAYLOAD:page or bracket notation. Casting to STRING returns text values for page and sku. TRY_TO_DECIMAL returns NULL instead of raising an error if price is missing or contains invalid numeric text, which matches the requirement to avoid query failure. COALESCE on PAYLOAD:coupon::STRING correctly substitutes 'NO_COUPON' when the coupon field is SQL NULL or a JSON null that becomes NULL when cast to STRING.
- B. Incorrect.
Incorrect. GET can retrieve a field from a VARIANT object, but TO_DECIMAL is not tolerant of invalid input and can fail if the price value is malformed. That violates the requirement to avoid failure. In addition, returning GET(PAYLOAD, 'page') and GET(PAYLOAD, 'sku') without explicit casting would leave them as VARIANT rather than text, which does not fully meet the stated output requirement.
- C. Incorrect.
Incorrect. PARSE_JSON is used to parse a string into VARIANT; PAYLOAD is already stored as VARIANT, so parsing it again is unnecessary and invalid in this context. TRY_CAST can be useful, but the use of PARSE_JSON on an existing VARIANT makes this option unsuitable. Also, ZEROIFNULL is intended for numeric expressions, not for replacing a string coupon value.
- D. Incorrect.
Incorrect. Bracket notation for VARIANT access is valid, but TO_NUMBER with precision and scale in this form is not the safe choice here because it can error on invalid non-numeric text. The requirement specifically says conversion must not fail. Also, checking PAYLOAD['coupon'] IS NULL does not reliably handle all semi-structured null handling cases as cleanly as casting then using COALESCE; plus the else branch returns a VARIANT, causing type inconsistency with the string literal unless explicitly cast.