COF-C03 Data Loading, Unloading, and Connectivity Practice Question
A data engineer is loading JSON data from an external stage into a Snowflake table using COPY INTO with a JSON file format. The JSON records contain nested arrays and objects. The engineer wants to load specific elements into separate columns. Which approach should the engineer use?
⚠ Common exam trap
The trap here is assuming that JSON must be flattened or converted before loading, when Snowflake's VARIANT type and semi-structured functions handle nested data natively.
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
✓
Load the entire JSON record into a single VARIANT column, then use a view with dot notation and array indexing to extract the desired elements.
Snowflake provides robust support for semi-structured data through the VARIANT data type. Loading JSON into a VARIANT column and then using dot notation and array indexing in a view or query allows flexible extraction of nested elements. This approach leverages Snowflake's built-in functions and avoids the need for complex transformations or external processing.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Use the COPY INTO command with a transformation that uses the SPLIT_TO_TABLE function to flatten the arrays into rows before loading.
Why it's wrong here
SPLIT_TO_TABLE is used to split a string into rows based on a delimiter, not to flatten JSON arrays. For JSON, you would use FLATTEN or LATERAL FLATTEN. SPLIT_TO_TABLE does not parse JSON structures and would not correctly handle nested arrays or objects, leading to incorrect or failed loads.
- ✗
Define the target table with columns matching the JSON keys and use the MATCH_BY_COLUMN_NAME option in the file format to automatically map nested elements to columns.
Why it's wrong here
MATCH_BY_COLUMN_NAME is used for CSV and other flat file formats to map columns by name, not for nested JSON. It does not traverse nested structures. For JSON, you must either load into VARIANT and extract, or use a transformation with functions like JSON_EXTRACT_PATH_TEXT. This option misunderstands the feature's applicability.
- ✓
Load the entire JSON record into a single VARIANT column, then use a view with dot notation and array indexing to extract the desired elements.
Why this is correct
Loading JSON into a VARIANT column preserves the semi-structured data, and Snowflake's native support for semi-structured data allows extraction using dot notation and array indexing in a view or query. This approach is flexible and avoids complex transformations during load, making it ideal for nested JSON.
- ✗
Convert the JSON to CSV using an external tool before loading, then use a standard CSV file format with column mapping.
Why it's wrong here
While converting JSON to CSV is possible externally, it adds unnecessary complexity and loses the advantages of Snowflake's native semi-structured data support. The engineer can directly load JSON into a VARIANT column and query nested elements, which is simpler and more maintainable. This option is not the recommended approach within Snowflake.
About these practice questions
One of 280 original COF-C03 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 COF-C03 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 COF-C03 exam.