DEA-C02 Data Transformation Practice Question
A data engineer is transforming a large table with a VARIANT column that contains nested arrays. The goal is to produce one row per element in the array, preserving all other columns. The engineer uses the FLATTEN function with the LATERAL keyword. Which behavior should the engineer expect when the VARIANT column contains an empty array?
⚠ Common exam trap
The trap here is assuming that an empty array will yield a row with NULLs, when in fact it yields no rows and can silently drop data in an inner lateral join.
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
✓
The row is omitted from the result set because FLATTEN produces no rows for an empty array.
FLATTEN with LATERAL produces one row per element in the array. When the array is empty, there are no elements, so no rows are generated, and the outer row is excluded from the result. This is consistent with inner join semantics. To retain such rows, an outer join with LATERAL FLATTEN is needed. The correct expectation is that the row is omitted.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
The row is retained with NULL values for the flattened columns.
Why it's wrong here
FLATTEN does not produce a NULL row for an empty array; it produces no rows at all. Therefore, the outer row is not retained unless an outer join is used. This option confuses the behavior of FLATTEN with that of functions that return NULL for empty inputs. The scenario does not mention an outer join, so the row would be dropped.
- ✓
The row is omitted from the result set because FLATTEN produces no rows for an empty array.
Why this is correct
When FLATTEN is applied to an empty array, it generates zero output rows. With a LATERAL join, the outer row is eliminated because there are no matching rows from the flattened side. This is standard SQL behavior for lateral joins with empty sets. The engineer must be aware that empty arrays cause data loss unless handled with an outer join or a default value.
- ✗
The query fails with an error because FLATTEN cannot process empty arrays.
Why it's wrong here
FLATTEN handles empty arrays gracefully by returning no rows; it does not raise an error. This option incorrectly suggests a failure, which would be surprising and unhelpful. The function is designed to handle various semi-structured inputs, including empty ones, without error. The engineer should not expect an exception in this case.
- ✗
The row is retained with an empty array in the flattened column.
Why it's wrong here
FLATTEN expands arrays into individual elements; it does not preserve the original array as a single value. For an empty array, there are no elements to expand, so no row is produced. This option misconstrues the purpose of FLATTEN, which is to unnest, not to pass through the original array. The result would not contain an empty array in the output column.
About these practice questions
Courseiva writes every DEA-C02 question from scratch — 229 in total, each with an explanation and a wrong-answer breakdown. None are copied from real exams or 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.