DEA-C02 Data Transformation Practice Question
A Data Engineer needs to transform semi-structured JSON data loaded into a VARIANT column named 'raw_data'. The goal is to flatten the 'items' array into individual rows while preserving the 'order_id' from the root level. Which function is the most efficient choice for this transformation?
⚠ Common exam trap
Engineers often try to use standard SQL joins or array functions without LATERAL, which fails to properly correlate root-level identifiers with exploded array elements.
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
✓
FLATTEN
The FLATTEN function is specifically designed to transform semi-structured data into a relational format by producing a lateral view of array elements. By using it in a LATERAL join, the engineer can correlate the parent 'order_id' with each exploded array element effectively. This is a critical pattern in Snowflake for normalizing JSON structures before downstream analytics, ensuring that hierarchical data becomes queryable by standard SQL BI tools.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
JSON_EXTRACT_PATH_TEXT
Why it's wrong here
This function is used for extracting a single scalar value from a specific path in a JSON object. It does not perform the transformation required to explode an array into multiple rows, making it unsuitable for flattening operations that necessitate row-level expansion of nested collections.
- ✓
FLATTEN
Why this is correct
The FLATTEN function is the standard table function used to explode arrays or objects into separate rows. When applied with a LATERAL join, it maintains the relationship between the root object and the nested collection, providing the necessary tabular structure for further SQL-based data manipulation.
- ✗
OBJECT_CONSTRUCT
Why it's wrong here
This function is used to create a new JSON object from a sequence of keys and values. While it is useful for assembling semi-structured data, it does not provide the capability to decompose arrays or transform hierarchical data into relational rows as required by this scenario.
- ✗
ARRAY_TO_STRING
Why it's wrong here
This function concatenates elements of an array into a single delimited string. It is useful for data formatting or output generation but fails to meet the requirement of flattening nested arrays into distinct rows that can be joined with other relational data entities.
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.