DEA-C02 Data Transformation Practice Question

Answer analysis

Option-by-option breakdown

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

• ✓

  Use the FLATTEN function in the FROM clause with the INPUT argument set to event_payload:tags.

  Why this is correct

  The FLATTEN table function is designed to explode semi-structured arrays into multiple rows. By specifying event_payload:tags as the INPUT, Snowflake returns one row per element in the array, allowing direct access to the 'name' and 'score' fields. This is the canonical way to transform nested arrays into a relational format while preserving the parent event ID.

• ✗

  Use the SPLIT_TO_TABLE function with a delimiter of comma on the string representation of the tags array.

  Why it's wrong here

  SPLIT_TO_TABLE splits a string into rows based on a delimiter, but the tags array is a VARIANT, not a delimited string. Converting it to a string and splitting would break on nested objects and lose the structured 'name' and 'score' keys. This method is unsuitable for semi-structured array elements.

• ✗

  Use the PARSE_JSON function on event_payload:tags and then apply a lateral join with a VALUES clause.

  Why it's wrong here

  PARSE_JSON converts a string to a VARIANT but does not expand arrays into rows. A lateral join with VALUES would require manually enumerating each element, which is impractical for dynamic arrays. This approach does not automatically produce one row per tag element and would fail to handle variable-length arrays efficiently.

• ✗

  Use the GET_PATH function to extract the array, then use a recursive CTE to iterate over its elements.

  Why it's wrong here

  GET_PATH can extract a value from a VARIANT, but it does not flatten arrays into rows. A recursive CTE could theoretically iterate, but it is overly complex, less performant, and not the intended Snowflake transformation pattern. FLATTEN is specifically optimized for this task and avoids manual recursion.