COF-C03 Practice Question: Performance Optimization, Querying, and Transformation
A data engineer has a large table SALES_RAW with a VARIANT column PAYLOAD that stores semi-structured JSON. The engineer needs to flatten an array of product objects inside PAYLOAD into separate rows, keeping all other columns intact. Which Snowflake construct should be used in the SELECT statement to achieve this?
⚠ Common exam trap
Many exam-takers confuse functions that manipulate semi-structured data (like OBJECT_CONSTRUCT or ARRAY_AGG) with the specific table function that performs row expansion.
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
✓
LATERAL FLATTEN(input => PAYLOAD:products)
The LATERAL FLATTEN table function is the standard Snowflake mechanism for expanding semi-structured arrays into rows while keeping the parent row's other columns. It accepts a VARIANT input and returns one row per element, making it ideal for converting nested JSON into a relational result set without losing context.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✓
LATERAL FLATTEN(input => PAYLOAD:products)
Why this is correct
LATERAL FLATTEN is the correct Snowflake table function that expands an array or object into multiple rows, and using it with a lateral join preserves the original row's columns. It accepts an input expression such as PAYLOAD:products and produces one row per array element, which is exactly what this scenario requires.
- ✗
PARSE_JSON(PAYLOAD):products
Why it's wrong here
PARSE_JSON converts a string to a VARIANT, and the colon path accesses an element, but neither operation expands an array into multiple rows. It would return the array as a single value, leaving the engineer to manually iterate, which is not the intended flattening behavior.
- ✗
ARRAY_AGG(PAYLOAD:products)
Why it's wrong here
ARRAY_AGG is an aggregation function that collects values into an array, the opposite of flattening. Using it would collapse rows instead of expanding the products array, so it cannot transform the nested array into relational rows as required.
- ✗
OBJECT_CONSTRUCT('products', PAYLOAD:products)
Why it's wrong here
OBJECT_CONSTRUCT builds a new object from key-value pairs; it does not expand an array into rows. Applying it here would simply recreate a nested structure instead of flattening the products array, so it does not meet the requirement of producing one row per product element.
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.