Databricks Data Engineer Associate Question 132
Select 2You are tasked with removing duplicate rows in a Delta table based on specific columns user_id and event_time. You want to keep only the most recent record for each user_id. Which of the following approaches will correctly deduplicate the data?
- A
Use the
dropDuplicatesmethod on the DataFrame specifyinguser_idandevent_timeas arguments. - B
Use the
ROW_NUMBER()window function partitioned byuser_idand ordered byevent_timein descending order, then filter for rows withROW_NUMBER = 1. - C
Use the
distinct()method on the DataFrame to remove duplicate rows. - D
Use the
groupBymethod onuser_idand aggregate withmax(event_time)to get the most recent record, then join it back to the original DataFrame to retain other columns.
Show answer and explanation
Correct answers: B, D
Explanation
Deduplicating rows based on specific columns requires an approach that not only identifies duplicates but also determines which record to preserve. The ROW_NUMBER() window function and the combination of groupBy with max(event_time) both provide mechanisms to achieve this by partitioning the data by user_id and selecting the most recent event_time. Methods like dropDuplicates and distinct() are insufficient in this scenario, as they lack the ability to determine which record is the most recent.
- A. Incorrect.
dropDuplicatesis used for removing duplicates based on exact matches of specified columns. However, it does not allow you to keep the most recent record, as it cannot determine which row to preserve. - B. Correct.
Using the
ROW_NUMBER()window function allows you to assign a unique rank to each row within a partition ofuser_idbased on the order ofevent_time. Filtering forROW_NUMBER = 1ensures you keep only the most recent record. - C. Incorrect.
distinct()removes exact duplicate rows across all columns in the DataFrame. It does not provide functionality to deduplicate based on specific columns while keeping the most recent record. - D. Correct.
Using
groupByandmax(event_time)allows you to determine the most recentevent_timefor eachuser_id. Joining this result back to the original DataFrame ensures you retain other columns from the original dataset.