DEA-C02 Data Transformation Practice Question
A data engineer is building a transformation pipeline that processes semi-structured event logs stored in a VARIANT column named `event_data`. The logs contain a nested array under the key `items`. The engineer needs to produce one output row per element in the array, preserving all other columns from the source table. Which Snowflake construct should be used to achieve this transformation?
⚠ Common exam trap
The trap here is assuming that any function that references the array will automatically expand it, when only FLATTEN (used laterally) produces multiple rows.
Answer choices
Why each option matters
Answer the question above first, then reveal the full breakdown to understand why each option is right or wrong.
Correct answer & explanation
✓
LATERAL FLATTEN(input => event_data:items)
To expand a nested array into multiple rows while keeping the original columns, a lateral join with FLATTEN is required. LATERAL FLATTEN allows the FLATTEN function to access the VARIANT column from the preceding table and returns one row per array element, which is exactly the needed behavior. The other functions either aggregate, construct objects, or parse strings, none of which achieve row expansion.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✓
LATERAL FLATTEN(input => event_data:items)
Why this is correct
LATERAL FLATTEN is designed to expand a VARIANT array into multiple rows, one per element. Because it is a lateral join, it can reference the VARIANT column from the source table and preserve all other columns, making it ideal for this scenario.
- ✗
ARRAY_AGG(event_data:items)
Why it's wrong here
ARRAY_AGG is an aggregation function that collects values into an array, which is the opposite of what is needed. It would collapse rows rather than expand the nested array into individual rows, so it is not suitable for this transformation.
- ✗
OBJECT_CONSTRUCT('items', event_data:items)
Why it's wrong here
OBJECT_CONSTRUCT builds a new object from key-value pairs; it does not expand an array into multiple rows. Using it here would leave the array intact and produce a single row per original record, failing to meet the one-row-per-element requirement.
- ✗
PARSE_JSON(event_data:items)
Why it's wrong here
PARSE_JSON converts a string containing JSON into a VARIANT, but the data is already in VARIANT format. It does not perform any row expansion, so it would not produce one row per array element and is irrelevant to the required transformation.
About these practice questions
This DEA-C02 question is part of Courseiva's 229-question bank — original exam-style content with full explanations and wrong-answer analysis, never real exam questions or exam dumps. Learn why practice questions differ from exam dumps →
JA
Written and reviewed by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
Last reviewed September 2026 · checked against the official Snowflake exam blueprint
This DEA-C02 practice question is part of Courseiva's free Snowflake certification practice question bank. Courseiva provides original exam-style practice questions with explanations, topic-based practice, mock exams, readiness tracking, and study analytics to help learners prepare for the DEA-C02 exam.