COF-C03 Practice Question: Performance Optimization, Querying, and Transformation

Answer analysis

Option-by-option breakdown

For each option: why learners choose it and why it is or isn't the right answer here.

• ✗

  PARSE_JSON

  Why it's wrong here

  PARSE_JSON converts a JSON string into a VARIANT value. It is used to parse text into semi-structured data, not to flatten nested arrays. Once the data is in VARIANT, you still need a flattening mechanism. PARSE_JSON alone does not generate multiple rows from an array, so it does not satisfy the transformation requirement.

• ✓

  LATERAL FLATTEN

  Why this is correct

  LATERAL FLATTEN is a table function that expands nested arrays or objects in a VARIANT column into multiple rows. When used in the FROM clause with a lateral join, it preserves the correlation with the parent row, allowing each element of the array to become a separate row while retaining other columns from the original row. This is the standard method for normalizing semi-structured data.

• ✗

  ARRAY_AGG

  Why it's wrong here

  ARRAY_AGG is an aggregation function that combines multiple input values into a single ARRAY. It performs the opposite operation of flattening: it groups values into an array. Since the goal is to expand an array into multiple rows, ARRAY_AGG would not help; it would actually condense data instead of normalizing it.

• ✗

  OBJECT_CONSTRUCT

  Why it's wrong here

  OBJECT_CONSTRUCT creates a new OBJECT from key-value pairs. It is used to build semi-structured objects, not to flatten arrays into rows. For the scenario of expanding nested arrays into a relational format, OBJECT_CONSTRUCT would not produce multiple rows; it would only create a single object per input row, so it does not meet the requirement.