DEA-C02 Data Movement Practice Question
A data engineer is designing a pipeline that loads semi-structured data from an external stage into a Snowflake table with a VARIANT column. The files contain nested arrays and keys that vary between records. Which TWO configuration choices should the engineer make to handle the variability and preserve the structure? (Choose two.)
⚠ Common exam trap
The trap here is treating semi-structured data like relational data; forcing a fixed schema or CSV mapping breaks nested arrays and varying keys that VARIANT is designed to hold.
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
✓
Enable STRIP_OUTER_ARRAY so a top-level JSON array is loaded as individual rows.
Loading JSON into a VARIANT column preserves nested arrays and varying keys without a fixed schema, and STRIP_OUTER_ARRAY turns a top-level array into individual rows. Together they handle the semi-structured variability. CSV cannot represent nesting, a fixed schema cannot accommodate unknown keys, and column-name matching requires pre-defined columns.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Set the file format TYPE to CSV with a delimiter that matches the JSON structure.
Why it's wrong here
CSV cannot represent nested arrays and objects natively, so using it for JSON content would flatten or corrupt the structure. Choosing a delimiter does not help parse nested JSON, and the varying keys would not be preserved as they are with a VARIANT column.
- ✗
Define a fixed relational schema for every possible key before loading.
Why it's wrong here
A fixed schema requires knowing all keys in advance, which contradicts the requirement that keys vary between records. New or missing keys would cause load errors or nulls, and nested arrays would need to be flattened, losing the original structure that VARIANT preserves.
- ✓
Enable STRIP_OUTER_ARRAY so a top-level JSON array is loaded as individual rows.
Why this is correct
When a file contains a single top-level array of objects, STRIP_OUTER_ARRAY removes that outer array and loads each element as a separate row. This is essential for making the nested records queryable as individual rows rather than one giant array value.
- ✗
Use MATCH_BY_COLUMN_NAME = CASE_SENSITIVE to map JSON keys to columns.
Why it's wrong here
MATCH_BY_COLUMN_NAME is intended for loading semi-structured data into separate relational columns by key name, which requires pre-defined columns and does not suit varying keys. It also does not preserve nested arrays as a single structure, so it does not meet the design goals.
- ✓
Set the file format TYPE to JSON and load into a single VARIANT column.
Why this is correct
JSON is the native format for semi-structured data, and loading it into a VARIANT column preserves nested arrays and keys without requiring a fixed schema. VARIANT can hold the varying structures as-is, so differing keys across records do not cause load failures or null columns.
About these practice questions
One of 229 original DEA-C02 practice questions on Courseiva, each with a full explanation and wrong-answer analysis — not exam dumps or protected exam content. 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.