ExamworthyExamworthy
Open the appStart free

COF-C03 - Performance Optimization, Querying, and Transformation - 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.

More in this domain

Back to all Performance Optimization, Querying, and Transformation objectives, or the COF-C03 cert hub.

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