ARA-C01 Question 350
Single answerQuerying semi-structured dataA retail company stores clickstream events in a Snowflake table named RAW_EVENTS. The table has two columns: EVENT_ID NUMBER and PAYLOAD VARIANT. Each PAYLOAD value contains JSON similar to: {"customer":{"id":"C123","tier":"gold"},"events":[{"type":"view","sku":"SKU1"},{"type":"purchase","sku":"SKU2"}],"source":"mobile"}. An architect must design a query that returns one row per purchased SKU, along with the customer ID, from the nested JSON. The solution should correctly handle the array of event objects and avoid returning non-purchase events. Which query is the MOST appropriate?
- A
SELECT payload:customer:id::STRING AS customer_id, f.value:sku::STRING AS sku FROM raw_events, LATERAL FLATTEN(input => payload:events) f WHERE f.value:type::STRING = 'purchase';
- B
SELECT payload.customer.id AS customer_id, payload.events.sku AS sku FROM raw_events WHERE payload.events.type = 'purchase';
- C
SELECT payload:customer:id::STRING AS customer_id, ARRAY_SIZE(payload:events) AS sku FROM raw_events WHERE payload:events:type::STRING = 'purchase';
- D
SELECT customer.id::STRING AS customer_id, events[0].sku::STRING AS sku FROM raw_events WHERE events.type = 'purchase';
Show answer and explanation
Correct answer: A
Explanation
The key architectural requirement is to correctly query semi-structured JSON stored in a VARIANT column when the relevant data is inside an array. In Snowflake, object fields are accessed with path notation using colon separators, and arrays often require LATERAL FLATTEN to expand each element into its own row. In this scenario, PAYLOAD:EVENTS is an array of objects, so the correct pattern is FROM RAW_EVENTS, LATERAL FLATTEN(INPUT => PAYLOAD:EVENTS) and then referencing F.VALUE to inspect each event object. Filtering on F.VALUE:TYPE and selecting F.VALUE:SKU ensures only purchase events are returned. This aligns with Snowflake best practices for querying semi-structured data, particularly the documented use of VARIANT path traversal, explicit casting to SQL data types, and FLATTEN for arrays.
- A. Correct.
Correct. In Snowflake, semi-structured JSON stored in a VARIANT column is queried with path notation such as payload:customer:id. Because events is an array of objects, LATERAL FLATTEN is the appropriate construct to produce one output row per array element. Filtering on f.value:type::STRING = 'purchase' correctly limits results to purchase events, and f.value:sku extracts the purchased SKU.
- B. Incorrect.
Incorrect. This option treats the JSON as if nested array elements could be projected directly without flattening. Since events is an array, payload.events.sku and payload.events.type do not correctly return one row per event object. A common misconception is assuming dot notation alone can iterate through arrays in Snowflake; it cannot without explicit indexing or FLATTEN.
- C. Incorrect.
Incorrect. ARRAY_SIZE(payload:events) returns the number of elements in the events array, not a SKU. Also, payload:events:type is not valid for filtering individual objects inside an array without flattening. This distractor reflects confusion between array metadata functions and extraction of values from nested elements.
- D. Incorrect.
Incorrect. This query references customer and events as if they were top-level relational columns rather than fields inside the PAYLOAD VARIANT column. It also hard-codes events[0], which would only inspect the first event and could miss purchases elsewhere in the array. This is a common error when moving from structured SQL to semi-structured querying.