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 `PIVOT` clause in a SELECT statement.

  Why it's wrong here

  PIVOT performs the opposite transformation: it rotates rows into columns. Using PIVOT would not unpivot the wide table; it would aggregate and pivot data further, which is not the desired outcome. The engineer needs to convert columns to rows, so PIVOT is incorrect.

• ✗

  Use a `LATERAL FLATTEN` on an array constructed from the quarter columns.

  Why it's wrong here

  LATERAL FLATTEN is designed for semi-structured data, such as arrays or VARIANT. Constructing an array from the quarter columns and then flattening it would require additional steps and may not be as efficient or straightforward as using the native UNPIVOT clause for relational data.

• ✓

  Use the `UNPIVOT` clause in a SELECT statement.

  Why this is correct

  UNPIVOT is a built-in Snowflake clause that rotates columns into rows. It is optimized for this operation and can handle millions of rows efficiently. By specifying the columns to unpivot and the output column names, the engineer can transform the wide table into a long format in a single statement.

• ✗

  Use a series of `UNION ALL` SELECT statements, one for each quarter column.

  Why it's wrong here

  While UNION ALL can achieve unpivoting, it requires multiple scans of the table and concatenation of results. For millions of rows, this is less efficient than the native UNPIVOT clause, which is optimized internally. UNION ALL also increases code complexity and maintenance overhead.