Databricks Data Engineer Associate Question 122
Single answerYou are tasked with deduplicating rows in a Delta Lake table named 'sales_data' based on the 'transaction_id' column, keeping the record with the most recent 'updated_at' timestamp in case of duplicates. Which of the following code snippets achieves this?
- A
df = spark.read.format('delta').load('/mnt/delta/sales_data') latest_records = df.orderBy('updated_at', ascending=False).dropDuplicates(['transaction_id']) latest_records.write.format('delta').mode('overwrite').save('/mnt/delta/sales_data')
- B
df = spark.read.format('delta').load('/mnt/delta/sales_data') latest_records = df.dropDuplicates(['transaction_id']).orderBy('updated_at', ascending=False) latest_records.write.format('delta').mode('overwrite').save('/mnt/delta/sales_data')
- C
df = spark.read.format('delta').load('/mnt/delta/sales_data') latest_records = df.groupBy('transaction_id').agg({'updated_at': 'max'}) latest_records.write.format('delta').mode('overwrite').save('/mnt/delta/sales_data')
- D
df = spark.read.format('delta').load('/mnt/delta/sales_data') windowSpec = Window.partitionBy('transaction_id').orderBy(F.desc('updated_at')) deduplicated_df = df.withColumn('rank', F.row_number().over(windowSpec)).filter(F.col('rank') == 1).drop('rank') deduplicated_df.write.format('delta').mode('overwrite').save('/mnt/delta/sales_data')
Show answer and explanation
Correct answer: D
Explanation
The correct solution for deduplicating rows in a Delta Lake table while keeping the most recent record based on a timestamp column involves using a window function. The window function allows partitioning by the 'transaction_id' column and ordering the rows within each partition by 'updated_at' in descending order. By assigning a rank to each row and filtering for the top-ranked row in each partition, the deduplication process ensures that the most recent record for each 'transaction_id' is retained. The other options fail to meet the requirements due to improper use of deduplication techniques or lack of proper reconstruction of the dataset.
- A. Incorrect.
While this code uses
orderByanddropDuplicates, it does not ensure that only the most recent record based on 'updated_at' is retained.dropDuplicateskeeps the first occurrence encountered, which in this case is not guaranteed to be the most recent record. - B. Incorrect.
This code incorrectly applies
orderByafterdropDuplicates. The deduplication process needs to consider the most recent record first, and applyingorderByafter deduplication does not achieve the desired result. - C. Incorrect.
This code uses
groupByto aggregate the maximum 'updated_at' for each 'transaction_id', but it does not retrieve the other columns from the original dataset, which is required to reconstruct the deduplicated table properly. - D. Correct.
This code correctly uses a window function to assign a rank to each record within the same 'transaction_id' partition, ordered by 'updated_at' in descending order. It then filters to keep only the top-ranked record for each 'transaction_id', effectively deduplicating the dataset while retaining the most recent record.