COF-C03 Question 294
Single answer4.4 Perform data transformation techniquesA retail company loads clickstream events into a VARIANT column named EVENT_DATA in a raw table. Analysts need a relational view that returns one row per item in the array EVENT_DATA:items, while preserving the parent event_id and extracting item_id and quantity as strongly typed columns. Which Snowflake approach best meets this requirement?
- A
Use LATERAL FLATTEN on EVENT_DATA:items and select event_id, VALUE:item_id::STRING, and VALUE:quantity::NUMBER
- B
Use PIVOT on EVENT_DATA:items to convert the array elements into separate rows and cast the fields in the SELECT clause
- C
Use COPY INTO with a FILE_FORMAT that extracts nested array elements into columns during query time
- D
Use ARRAY_AGG on EVENT_DATA:items grouped by event_id to expand each array element into a separate row
Show answer and explanation
Correct answer: A
Explanation
In Snowflake, semi-structured data in VARIANT, OBJECT, and ARRAY columns is commonly transformed into relational output by combining path notation with the FLATTEN table function. For arrays, FLATTEN produces one output row per element. Using LATERAL enables access to columns from the parent table row alongside each flattened element. After flattening, the VALUE pseudocolumn can be traversed using JSON path syntax and cast into relational data types such as STRING or NUMBER. This is a core transformation technique for working with JSON data in Snowflake and aligns with Snowflake documentation on querying semi-structured data and using FLATTEN for nested arrays.
- A. Correct.
Correct. FLATTEN is the Snowflake table function designed to explode arrays or objects stored in semi-structured data into rows. When used with LATERAL, it allows each source row to be correlated with the flattened array elements. The VALUE column from FLATTEN contains each array element, so expressions such as VALUE:item_id::STRING and VALUE:quantity::NUMBER correctly extract and cast nested attributes while preserving the parent event_id.
- B. Incorrect.
Incorrect. PIVOT rotates distinct values from rows into columns; it does not expand elements of a semi-structured array into separate rows. This option reflects a common confusion between reshaping tabular data and traversing nested JSON structures.
- C. Incorrect.
Incorrect. COPY INTO is used for loading or unloading data, not for transforming nested VARIANT arrays at query time into relational rows. File formats help parse incoming files during load, but they do not replace query-side transformation functions like FLATTEN for already loaded semi-structured data.
- D. Incorrect.
Incorrect. ARRAY_AGG aggregates multiple values into a single array, which is the opposite of the requirement. The scenario requires expanding an array into multiple rows, not recombining values. This distractor targets the misconception that any array-related function can be used for row expansion.