ARA-C01 Question 351
Single answerQuerying semi-structured dataA retail company stores clickstream events in a Snowflake table named RAW_EVENTS. The table has a VARIANT column named EVENT_DATA containing JSON documents with a structure similar to: {"eventType":"purchase","customer":{"id":"C123"},"items":[{"sku":"A1","qty":2},{"sku":"B9","qty":1}],"attributes":{"channel":"mobile"}}. Architects need to create a query that returns one row per purchased item with the customer ID, SKU, quantity, and channel, but only for events where eventType = 'purchase'. Which approach should be used?
- A
Select EVENT_DATA:customer:id, EVENT_DATA:items.sku, EVENT_DATA:items.qty, EVENT_DATA:attributes:channel from RAW_EVENTS where EVENT_DATA:eventType = 'purchase';
- B
Select EVENT_DATA:customer:id::string as customer_id, f.value:sku::string as sku, f.value:qty::number as qty, EVENT_DATA:attributes:channel::string as channel from RAW_EVENTS, lateral flatten(input => EVENT_DATA:items) f where EVENT_DATA:eventType::string = 'purchase';
- C
Select customer.id, items[].sku, items[].qty, attributes.channel from RAW_EVENTS where eventType = 'purchase';
- D
Select parse_json(EVENT_DATA):customer:id::string, flatten(EVENT_DATA:items):sku::string, flatten(EVENT_DATA:items):qty::number, parse_json(EVENT_DATA):attributes:channel::string from RAW_EVENTS where parse_json(EVENT_DATA):eventType::string = 'purchase';
Show answer and explanation
Correct answer: B
Explanation
To return one row per array element from semi-structured data in Snowflake, use the FLATTEN table function with LATERAL. This is the standard pattern for querying arrays inside VARIANT, OBJECT, or ARRAY data. Path notation such as EVENT_DATA:customer:id accesses nested object fields, while f.value accesses each array element produced by FLATTEN. Explicit casting from VARIANT to STRING or NUMBER is a best practice when returning typed relational results. Snowflake documentation on querying semi-structured data and using FLATTEN describes this approach: arrays must be flattened to produce multiple rows, and colon notation is used to traverse nested JSON structures stored in VARIANT.
- A. Incorrect.
Incorrect. EVENT_DATA:customer:id is a valid path expression, but EVENT_DATA:items.sku and EVENT_DATA:items.qty do not correctly expand an array into one row per element. The ITEMS field is an array, so querying it directly without FLATTEN will not produce one row per purchased item. This is a common mistake when moving from scalar JSON attributes to arrays in Snowflake.
- B. Correct.
Correct. This approach uses Snowflake's semi-structured data path notation to access nested object fields and LATERAL FLATTEN to explode the ITEMS array into separate rows. Casting with ::string and ::number is also appropriate because VARIANT values should typically be cast to the required scalar types for downstream use. Filtering on EVENT_DATA:eventType::string = 'purchase' correctly restricts the result set to purchase events.
- C. Incorrect.
Incorrect. This uses SQL/JSON-style dot and wildcard notation that is not valid in standard Snowflake SQL for querying VARIANT columns. In Snowflake, nested JSON fields are typically referenced using colon notation against a VARIANT column, and arrays that need row expansion must be handled with FLATTEN. This option reflects a misconception that semi-structured data can be queried exactly like native relational columns or with unsupported wildcard projection syntax.
- D. Incorrect.
Incorrect. PARSE_JSON is used to convert a string into VARIANT. If EVENT_DATA is already stored as VARIANT, reparsing is unnecessary. More importantly, FLATTEN in Snowflake is a table function that must be used in the FROM clause, typically with LATERAL, not as a scalar function in the SELECT list. This option combines two common errors: unnecessary reparsing and incorrect use of FLATTEN.