ARA-C01 Question 353
Single answerFlattenA retail company stores clickstream events in a VARIANT column named EVENT_DATA in table RAW_EVENTS. Each row contains JSON similar to: {"sessionId":"S1","items":[{"sku":"A100","qty":2},{"sku":"B200","qty":1}],"attributes":{"channel":"web"}}. An architect needs to design a query that returns one row per item purchased, while preserving rows even when the items array is missing or empty so downstream reconciliation can identify incomplete events. Which approach should be used?
- A
Use TABLE(FLATTEN(INPUT => EVENT_DATA:items, OUTER => TRUE)) in the FROM clause, and select RAW_EVENTS columns together with VALUE:sku and VALUE:qty.
- B
Use FLATTEN with RECURSIVE => TRUE on EVENT_DATA so that all nested elements are expanded automatically, then filter rows where KEY = 'items'.
- C
Use EVENT_DATA:items[0]:sku and EVENT_DATA:items[0]:qty in the SELECT list, because array traversal returns all elements in Snowflake.
- D
Use TABLE(FLATTEN(INPUT => EVENT_DATA:attributes, OUTER => TRUE)) because FLATTEN can only expand OBJECT values, not ARRAY values.
Show answer and explanation
Correct answer: A
Explanation
The best solution is to use FLATTEN directly on the known array path, EVENT_DATA:items, with OUTER => TRUE. In Snowflake, FLATTEN is a table function commonly used in a lateral join context to transform semi-structured arrays or objects into relational rows. For this scenario, the business requirement is not just to explode the items array, but also to retain source events when items is missing or empty. OUTER => TRUE is specifically intended for that behavior, emitting a row even when no elements are present. By contrast, RECURSIVE => TRUE is better suited for exploring unknown nested structures, not for targeted extraction from a known array path. Snowflake documentation for FLATTEN describes key output columns such as VALUE, INDEX, KEY, PATH, and SEQ, and notes that FLATTEN supports ARRAY and OBJECT inputs as well as the OUTER parameter for preserving non-expandable rows. This pattern is a common best practice when normalizing JSON arrays for downstream analytics while maintaining data quality visibility.
- A. Correct.
Correct. FLATTEN is designed to explode ARRAY or OBJECT values from VARIANT, OBJECT, or ARRAY data into a lateral inline view. Using INPUT => EVENT_DATA:items returns one row per array element, and OUTER => TRUE preserves the parent row even when the path does not exist or the array is empty by producing a single row with NULL for the flattened columns. This matches the requirement to keep incomplete events available for reconciliation.
- B. Incorrect.
Incorrect. RECURSIVE => TRUE expands all nested sub-elements, which can produce more rows than needed and complicate filtering. While it can be useful for traversing unknown or deeply nested structures, it is not the best approach when the architect specifically needs one row per element of a known array path. Filtering on KEY = 'items' is also problematic because array elements are typically identified by INDEX, and the rows produced recursively may include many unrelated nested nodes.
- C. Incorrect.
Incorrect. Direct indexing such as EVENT_DATA:items[0] retrieves only a specific element from the array, not all elements. This is a common misconception when working with semi-structured data. To return one row per array member, Snowflake requires FLATTEN or another row-generating construct.
- D. Incorrect.
Incorrect. FLATTEN works with both ARRAY and OBJECT values in Snowflake. Flattening EVENT_DATA:attributes would expand the object members under attributes, not the item array that contains the purchased products. This option reflects a misunderstanding of what data types FLATTEN supports.