Databricks Data Engineer Associate Question 133
Single answerYou are working with a Delta table named 'sales_data' and need to remove duplicate rows based on the 'customer_id' and 'transaction_date' columns, keeping only the row with the highest 'transaction_amount' for each duplicate group. Which of the following approaches would achieve this?
- A
Use a window function with PARTITION BY 'customer_id' and 'transaction_date', ORDER BY 'transaction_amount' DESC, and filter rows where the rank is 1.
- B
Use the DISTINCT keyword on the 'customer_id' and 'transaction_date' columns.
- C
Use the DROP DUPLICATES command on the Delta table, specifying 'customer_id' and 'transaction_date' as the subset columns.
- D
Use a GROUP BY clause on 'customer_id' and 'transaction_date', then use the MAX function to get the highest 'transaction_amount'.
Show answer and explanation
Correct answer: A
Explanation
To deduplicate rows based on specific columns while retaining the row with the highest value in another column, a window function is the most appropriate approach. It allows you to partition the data based on the specified columns and rank rows within each partition. Filtering to keep only the top-ranked row ensures the desired deduplication criteria are met.
- A. Correct.
Correct: Using a window function allows you to partition the data by 'customer_id' and 'transaction_date', and rank the rows based on 'transaction_amount'. You can then filter to keep only the top-ranked row for each group, effectively deduplicating the data.
- B. Incorrect.
Incorrect: DISTINCT removes duplicate rows entirely but does not allow you to retain the row with the highest 'transaction_amount' for each group of duplicates.
- C. Incorrect.
Incorrect: DROP DUPLICATES removes rows with identical values in the specified subset of columns but does not consider the 'transaction_amount' column to retain the highest value.
- D. Incorrect.
Incorrect: GROUP BY can be used to find the maximum 'transaction_amount' for each 'customer_id' and 'transaction_date', but it does not retain the full row data, so additional steps would be required to join it back to the original dataset.