COF-C03 Practice Question: Performance Optimization, Querying, and Transformation
A developer needs to flatten a VARIANT column named payload that contains a nested JSON array of order line items into individual rows, preserving the parent order attributes alongside each line item. Which Snowflake construct accomplishes this in a single SELECT statement?
⚠ Common exam trap
The trap here is reaching for generic SQL techniques such as recursive CTEs or aggregation when Snowflake provides a purpose-built FLATTEN table function for semi-structured data.
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
✓
A LATERAL FLATTEN of the payload:line_items array joined back to the parent row.
LATERAL FLATTEN is Snowflake's dedicated mechanism for exploding semi-structured arrays and objects into relational rows. Used laterally, it correlates each element with its parent row, so order attributes remain available alongside each line item. This yields a fully relational result set from nested JSON in one statement without manual recursion or aggregation.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
A PARSE_JSON call applied to the payload column in the SELECT list.
Why it's wrong here
PARSE_JSON converts a string into a VARIANT value, but the payload is already stored as VARIANT. It does not expand arrays into rows and returns a single value per input row. This function is useful when ingesting JSON text, not when exploding nested arrays, so it cannot deliver the one-row-per-line-item result the developer needs.
- ✓
A LATERAL FLATTEN of the payload:line_items array joined back to the parent row.
Why this is correct
LATERAL FLATTEN is the native Snowflake table function that expands a VARIANT array or object into one row per element, and using it as a lateral join preserves the parent row's columns. This directly satisfies the requirement to produce one row per line item while retaining order-level attributes, all within a single SELECT statement.
- ✗
A recursive common table expression that walks the JSON hierarchy level by level.
Why it's wrong here
Recursive CTEs can traverse hierarchical data, but they require an explicit anchor and recursive member and are cumbersome for simple array expansion. They do not automatically expose VARIANT array elements as rows and would need substantial manual parsing. For flattening a JSON array, the built-in FLATTEN table function is simpler, more performant, and purpose-built for this exact scenario.
- ✗
A GROUP BY on the VARIANT column with an ARRAY_AGG of the line items.
Why it's wrong here
GROUP BY aggregates rows, and ARRAY_AGG collects values into an array, which is the opposite of flattening. This approach would collapse line items rather than expand them into separate rows. The requirement is to transform one parent row into many child rows, so aggregation functions move in the wrong direction and cannot produce the desired relational output.
About these practice questions
This COF-C03 question is part of Courseiva's 280-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 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.