ARA-C01 Question 352
Single answerFlattenA retail company stores clickstream events in a VARIANT column named EVENT_DATA in table RAW_EVENTS. Each row contains a JSON document with an array field named ITEMS, where each element is an object containing PRODUCT_ID, QTY, and PRICE. The analytics team needs a query that returns one row per item while preserving parent row context such as EVENT_ID and USER_ID. Some events may have an empty ITEMS array or the ITEMS field may be missing entirely. Which approach best meets the requirement?
- A
Use TABLE(FLATTEN(INPUT => EVENT_DATA:ITEMS, OUTER => TRUE)) in the FROM clause, joined laterally to RAW_EVENTS, and select parent columns along with flattened VALUE fields.
- B
Use GET(EVENT_DATA, 'ITEMS') directly in the SELECT list; Snowflake automatically expands array elements into separate rows when GET is applied to arrays.
- C
Use TABLE(FLATTEN(INPUT => EVENT_DATA)) without a path, because FLATTEN automatically returns only nested array elements and ignores other keys in the JSON document.
- D
Use ARRAY_SIZE(EVENT_DATA:ITEMS) in the WHERE clause to expand the array and then project PRODUCT_ID, QTY, and PRICE from EVENT_DATA:ITEMS.
Show answer and explanation
Correct answer: A
Explanation
The best solution is to use the FLATTEN table function laterally against the ITEMS array. In Snowflake, FLATTEN converts elements of an array or object stored in VARIANT, OBJECT, or ARRAY into a relational result set with columns such as VALUE, INDEX, KEY, PATH, and THIS. When architects need to preserve source-table columns like EVENT_ID and USER_ID while exploding nested arrays, they typically use RAW_EVENTS with a lateral join to TABLE(FLATTEN(...)). Targeting EVENT_DATA:ITEMS is more precise than flattening the entire document. OUTER => TRUE is important when empty arrays or missing paths must still be represented so parent rows are not silently dropped. This aligns with Snowflake documentation and best practices for querying semi-structured data: use path notation to access nested elements and FLATTEN when array elements must become separate rows.
- A. Correct.
Correct. FLATTEN is a table function that produces one output row per array or object element. To preserve columns from the base table, it is typically used with a lateral join so each source row can emit zero or more child rows. Using INPUT => EVENT_DATA:ITEMS targets the specific array instead of traversing the full JSON document. OUTER => TRUE is appropriate when the requirement includes handling empty or missing arrays without losing the parent row context, because it can return a row for rows that would otherwise produce no FLATTEN output. The flattened VALUE column can then be referenced as VALUE:PRODUCT_ID, VALUE:QTY, and VALUE:PRICE.
- B. Incorrect.
Incorrect. GET can retrieve a field from a VARIANT object or an element from an array, but it does not explode arrays into multiple result rows. A common misconception is to assume semi-structured accessors automatically unnest arrays. In Snowflake, row expansion requires FLATTEN or another explicit transformation.
- C. Incorrect.
Incorrect. FLATTEN(INPUT => EVENT_DATA) would iterate over the top-level elements of the full JSON object, not just the ITEMS array. That means the output would include all top-level keys and values, which is not the requested one-row-per-item design. The PATH or direct INPUT targeting the array is needed to flatten the correct structure.
- D. Incorrect.
Incorrect. ARRAY_SIZE can tell how many elements are in an array, but it does not generate one row per array element. This option reflects a common error of confusing array inspection functions with set-generating functions. To project PRODUCT_ID, QTY, and PRICE as separate rows, FLATTEN is required.