Databricks Data Engineer Associate Question 131
Single answerYou are working with a Delta table named 'sales_data' that contains multiple rows with duplicate data based on the 'transaction_id' column. You want to deduplicate the table by retaining only the most recent record for each 'transaction_id' based on the 'updated_at' timestamp column. Which of the following code snippets correctly achieves this deduplication?
- A
df = (sales_data.withColumn('rank', row_number().over(Window.partitionBy('transaction_id').orderBy(desc('updated_at')))) .filter(col('rank') == 1) .drop('rank'))
- B
df = (sales_data.groupBy('transaction_id') .agg(max('updated_at').alias('latest_update')))
- C
df = (sales_data.withColumn('rank', rank().over(Window.partitionBy('transaction_id').orderBy(desc('updated_at')))) .filter(col('rank') == 1) .drop('rank'))
- D
df = (sales_data.filter(row_number().over(Window.partitionBy('transaction_id').orderBy(desc('updated_at'))) == 1))
Show answer and explanation
Correct answer: A
Explanation
The deduplication task requires identifying the most recent record within each group of rows sharing the same 'transaction_id'. The correct approach involves using a window function to assign a unique rank to rows based on the 'updated_at' column, then filtering for rows with rank 1. Option 1 achieves this correctly by utilizing the row_number() function with a properly defined window specification and filtering logic.
- A. Correct.
This is the correct option. The code uses the
row_number()function with a window partitioned by 'transaction_id' and ordered by 'updated_at' in descending order to assign a unique rank to each row within the partition. Filtering rows where the rank is 1 ensures only the most recent record is retained, followed by dropping the 'rank' column. - B. Incorrect.
This code incorrectly uses the
groupByandaggfunctions to deduplicate data, which only produces aggregated results without retaining the full row details of the most recent record for each 'transaction_id'. - C. Incorrect.
While the code uses the
rank()window function, it does not guarantee unique ranking if there are ties (i.e., duplicate 'updated_at' values within a partition). This may result in multiple rows being retained, which does not meet the deduplication requirement. - D. Incorrect.
This code snippet is invalid because the
row_number()function cannot be directly used as a filter condition without being assigned to a column using a window function.