COF-C03 - Performance Optimization, Querying, and Transformation (21% of the exam) - Section 4.6

Query and transform semi-structured data using the VARIANT, OBJECT, and ARRAY types, FLATTEN, and dot and bracket notation.

Work with semi-structured data such as JSON: load it into a VARIANT column, navigate nested OBJECT and ARRAY values with dot and bracket notation, cast extracted values to typed columns, and use the FLATTEN table function with a LATERAL join to expand arrays into rows. Recognise when to keep data in a VARIANT versus shred it into columns.

VARIANTOBJECT and ARRAYdot and bracket notationLATERAL FLATTENsemi-structured JSON

Practice question for this objective

Free samplePerformance Optimization, Querying, and Transformationmedium

A VARIANT column orders holds a JSON object whose items key is an array of line-item objects. An analyst needs one output row for every element of that array, with each row exposing the individual element so its fields can be projected. Which construct produces that one-row-per-element expansion?

SELECT f.value:sku::STRING AS sku
FROM orders o, <construct>;

ALATERAL FLATTEN(input => o.orders:items) f, because FLATTEN expands the array into one row per element and exposes each element through the value column Correct
BTABLE(SPLIT_TO_TABLE(o.orders:items, ',')) f, because SPLIT_TO_TABLE turns the JSON array into one row per element and exposes each element as value
CCROSS JOIN ARRAY_AGG(o.orders:items) f, because ARRAY_AGG unrolls the stored array into separate rows that each carry one element in value
DLATERAL OBJECT_KEYS(o.orders:items) f, because OBJECT_KEYS iterates the array and returns one row per element with the element placed in value

LATERAL FLATTEN is the table function that expands a VARIANT array into one row per element exposed through its value column. FLATTEN is a table function that takes a VARIANT array or object as its input argument and returns a row for each element, with the value column holding each element so nested fields can be cast and projected; pairing it with LATERAL correlates the expansion to each base table row.

Why A is correct: FLATTEN is a table function that returns one row per array element, and its value column exposes each element so its inner fields can be projected, which is exactly the requirement.

Why B is wrong: SPLIT_TO_TABLE splits a delimited VARCHAR on a separator into rows, so it cannot correctly expand a JSON array of objects into per-element rows.

Why C is wrong: ARRAY_AGG aggregates many rows into a single array, the reverse of what is needed, so it cannot expand an array into one row per element.

Why D is wrong: OBJECT_KEYS lists the key names of an OBJECT, not the elements of an array, so it neither expands the array nor populates a value column.

See more COF-C03 practice questions, answers explained.

Exam traps in Performance Optimization, Querying, and Transformation

Answers that look right on this material and are not. Each one is a distractor from a different question in the COF-C03 bank for this domain.

TO_VARIANT(profile), because TO_VARIANT reads the JSON text, parses its structure, and returns a VARIANT object whose keys can then be addressed with colon and dot path navigation
Why it is wrong: TO_VARIANT only wraps the existing value as a VARIANT scalar without parsing it, so the result stays a single string element and the keys are still not addressable by path.
RECURSIVE => TRUE, which tells FLATTEN to also descend into nested sub-arrays and therefore emits at least one row for an empty array so the parent order is retained
Why it is wrong: RECURSIVE makes FLATTEN walk nested structures top to bottom, but it does not generate a retained row for an empty or missing array, so parent orders with nothing to expand would still vanish.
The warehouse is undersized for the inputs, so the only fix is a larger warehouse because the inputs were too big to hold the join in memory at this size
Why it is wrong: The inputs are described as modest, so the row explosion comes from the join logic producing many output rows, not from large inputs overflowing memory at the current size.

Examworthy is not affiliated with or endorsed by Snowflake. Original, blueprint-aligned practice material only.