Databricks Data Engineer Associate Question 121
Select 2You are working with a Delta Lake table named 'sales_data' which contains duplicate rows. You need to deduplicate the table based on the 'transaction_id' column while retaining only the most recent record for each duplicate group based on the 'timestamp' column. Which of the following steps will correctly address this requirement?
- A
Use a SQL query with the ROW_NUMBER() window function partitioned by 'transaction_id', ordered by 'timestamp' in descending order, and filter rows where the row number equals 1.
- B
Use the DISTINCT keyword in a SQL query to remove duplicate rows from the 'sales_data' table.
- C
Apply the DROP DUPLICATES command in PySpark on the 'sales_data' DataFrame and specify the 'transaction_id' column.
- D
Perform a MERGE operation on the 'sales_data' table, matching rows on 'transaction_id' and keeping only the most recent 'timestamp'.
- E
Write a Delta Lake SQL query that uses GROUP BY 'transaction_id' and MAX('timestamp') to deduplicate the table.
Show answer and explanation
Correct answers: A, E
Explanation
To deduplicate a Delta Lake table based on a specific column ('transaction_id') while retaining the most recent record based on another column ('timestamp'), you can use either the ROW_NUMBER() window function or a GROUP BY query with MAX(). Both approaches ensure that the deduplication process meets the stated requirements. DISTINCT and DROP DUPLICATES do not provide the necessary control over determining which record to retain, and MERGE is not suitable for this use case.
- A. Correct.
This is correct. Using the ROW_NUMBER() window function allows you to deduplicate by identifying the most recent record for each 'transaction_id' based on the 'timestamp' column. This is a common and efficient approach for deduplication in Delta Lake.
- B. Incorrect.
This is incorrect. While the DISTINCT keyword removes rows that are completely identical, it does not help in selecting the most recent 'timestamp' for each 'transaction_id', which is a key requirement in this scenario.
- C. Incorrect.
This is incorrect. The DROP DUPLICATES command in PySpark removes duplicate rows based on the specified columns, but it does not allow you to retain the most recent record based on an additional ordering column like 'timestamp'.
- D. Incorrect.
This is incorrect. MERGE is used for upserts or updating data between two tables, but it is not the right approach for deduplication based on a specific column like 'timestamp'.
- E. Correct.
This is correct. Using GROUP BY and MAX('timestamp') ensures that you retain only the most recent record for each 'transaction_id'. This approach directly addresses the deduplication requirement while considering the 'timestamp' column.