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 a correlated subquery that selects the maximum `sale_amount` from the same table where `sale_date` is less than the current row's `sale_date`.

  Why it's wrong here

  A correlated subquery that selects the maximum sale_amount is incorrect because it retrieves the maximum value, not the previous row's value. Even if corrected to select the latest sale_date, correlated subqueries are generally inefficient for billions of rows and may not use partitioning effectively.

• ✓

  Use the `LAG` window function with a `PARTITION BY customer_id ORDER BY sale_date` clause.

  Why this is correct

  LAG is designed to access a previous row within a window. Partitioning by customer_id and ordering by sale_date ensures the previous sale for each customer is correctly identified, and Snowflake's optimizer can leverage clustering or partitioning to reduce data movement, making it efficient for large tables.

• ✗

  Use the `LEAD` window function with a `PARTITION BY customer_id ORDER BY sale_date` clause and then reverse the order.

  Why it's wrong here

  LEAD looks forward, not backward. Reversing the order would still require an additional sort and does not directly yield the previous row's value. This approach adds unnecessary complexity and can be less efficient than using LAG, which is purpose-built for accessing preceding rows.

• ✗

  Use a self-join on `customer_id` where the right table's `sale_date` is less than the left table's `sale_date`, then filter to the maximum right `sale_date`.

  Why it's wrong here

  A self-join with inequality and aggregation is a classic alternative, but it typically requires a large shuffle and can be far less efficient than a window function on billions of rows. It also risks incorrect results if multiple rows share the same sale_date, and it does not guarantee the immediate previous row.