DAA-C01 Question 88
Single answer1.7 Given a scenario, use Snowflake functions.A retail analytics team stores clickstream events in a Snowflake table named WEB_EVENTS. The EVENT_DATA column is VARIANT and contains JSON such as {"customer":{"id":"C123"},"device":{"type":"mobile"},"metrics":{"session_seconds":"305"},"event_ts":"2025-01-15T14:32:10Z"}. Analysts need a query that returns one row per event with these requirements: extract the customer ID and device type as strings, convert session_seconds to a numeric value for aggregation, and convert event_ts to a timestamp that preserves the UTC time from the JSON string. Which expression set best meets these requirements?
- A
SELECT EVENT_DATA:customer:id::STRING AS customer_id, EVENT_DATA:device:type::STRING AS device_type, TO_NUMBER(EVENT_DATA:metrics:session_seconds::STRING) AS session_seconds, TO_TIMESTAMP_TZ(EVENT_DATA:event_ts::STRING) AS event_ts FROM WEB_EVENTS;
- B
SELECT GET(EVENT_DATA,'customer.id')::STRING AS customer_id, GET(EVENT_DATA,'device.type')::STRING AS device_type, EVENT_DATA:metrics:session_seconds::NUMBER AS session_seconds, TO_TIMESTAMP_NTZ(EVENT_DATA:event_ts::STRING) AS event_ts FROM WEB_EVENTS;
- C
SELECT JSON_EXTRACT_PATH_TEXT(EVENT_DATA,'customer','id') AS customer_id, JSON_EXTRACT_PATH_TEXT(EVENT_DATA,'device','type') AS device_type, PARSE_JSON(EVENT_DATA:metrics:session_seconds)::NUMBER AS session_seconds, TO_TIMESTAMP(EVENT_DATA:event_ts) AS event_ts FROM WEB_EVENTS;
- D
SELECT EVENT_DATA['customer']['id'] AS customer_id, EVENT_DATA['device']['type'] AS device_type, AS_NUMBER(EVENT_DATA['metrics']['session_seconds']) AS session_seconds, CAST(EVENT_DATA['event_ts'] AS TIMESTAMP_LTZ) AS event_ts FROM WEB_EVENTS;
Show answer and explanation
Correct answer: A
Explanation
This scenario tests practical use of Snowflake functions and semi-structured data access. For VARIANT columns, Snowflake best practice is to traverse JSON using path notation such as column:path:to:element and then cast to the required SQL type. When numeric values are stored in JSON as quoted strings, converting with ::STRING followed by TO_NUMBER is a reliable pattern for downstream aggregation. For ISO 8601 timestamps that include a timezone designator such as Z, TO_TIMESTAMP_TZ is the most explicit choice when the requirement is to preserve timezone information from the source value. Candidates are often tempted by functions or syntax from other SQL engines, by dotted-path GET usage, or by NTZ/LTZ timestamp conversions that alter or discard timezone semantics. Relevant Snowflake documentation areas include querying semi-structured data, casting and conversion functions, and the TO_TIMESTAMP_* family of functions.
- A. Correct.
Correct. Snowflake supports semi-structured traversal with colon notation for top-level paths and additional path steps, and explicit casting to STRING is appropriate for scalar text values stored in VARIANT. Converting session_seconds by first casting to STRING and then using TO_NUMBER reliably handles JSON text such as "305". TO_TIMESTAMP_TZ on the ISO 8601 string with a trailing Z preserves the UTC time zone information, which matches the requirement to preserve UTC from the source JSON.
- B. Incorrect.
Incorrect. GET(EVENT_DATA,'customer.id') is a common mistake: GET does not interpret 'customer.id' as a dotted nested path in this form for VARIANT traversal. Nested JSON should be accessed with path notation such as EVENT_DATA:customer:id or nested GET calls. Also, TO_TIMESTAMP_NTZ removes time zone context, so it does not preserve the UTC offset from the original event_ts string.
- C. Incorrect.
Incorrect. JSON_EXTRACT_PATH_TEXT is not a Snowflake SQL function used for VARIANT traversal in this way; it is often confused with functions from other platforms. PARSE_JSON is also inappropriate here because EVENT_DATA:metrics:session_seconds is already a VARIANT scalar, not a raw JSON string requiring parsing. In addition, TO_TIMESTAMP(EVENT_DATA:event_ts) may work differently depending on input and session settings, but it does not explicitly preserve the timezone as clearly as TO_TIMESTAMP_TZ on the string value.
- D. Incorrect.
Incorrect. Bracket notation can be used with semi-structured data, but EVENT_DATA['customer']['id'] returns VARIANT values; without casting, the customer_id and device_type columns do not meet the requirement to return strings. AS_NUMBER on a VARIANT string value such as "305" is not the best choice here because the JSON value is stored as text and commonly requires explicit conversion from string. Casting event_ts to TIMESTAMP_LTZ converts to local time zone semantics rather than preserving the explicit UTC time zone from the source string.