Databricks Data Engineer Associate Question 124
Single answerYou have a Delta Lake table named 'sales_data' that contains duplicate rows. You want to deduplicate the table based on the 'transaction_id' column, ensuring that only the latest record for each 'transaction_id' (based on the 'updated_at' column) is kept. Which of the following code snippets will correctly achieve this?
- A
df = spark.read.format('delta').load('/mnt/delta/sales_data'); df.dropDuplicates(['transaction_id']).write.format('delta').mode('overwrite').save('/mnt/delta/sales_data')
- B
df = spark.read.format('delta').load('/mnt/delta/sales_data'); df.orderBy('updated_at').dropDuplicates(['transaction_id']).write.format('delta').mode('overwrite').save('/mnt/delta/sales_data')
- C
df = spark.read.format('delta').load('/mnt/delta/sales_data'); from pyspark.sql.window import Window; from pyspark.sql.functions import row_number; windowSpec = Window.partitionBy('transaction_id').orderBy(df['updated_at'].desc()); deduped_df = df.withColumn('row_number', row_number().over(windowSpec)).filter('row_number = 1').drop('row_number'); deduped_df.write.format('delta').mode('overwrite').save('/mnt/delta/sales_data')
- D
spark.sql('CREATE OR REPLACE TABLE sales_data AS SELECT DISTINCT * FROM sales_data')
Show answer and explanation
Correct answer: C
Explanation
To deduplicate a Delta Lake table while ensuring that only the latest record for each 'transaction_id' is kept based on the 'updated_at' column, you need to use a window function. A window function allows you to partition the data by the 'transaction_id' column and order it by the 'updated_at' column in descending order. By assigning a row number to each row within these partitions, you can filter out all but the first record for each 'transaction_id'. This ensures the deduplication process retains only the latest records, which is not achievable with simple 'dropDuplicates' or 'DISTINCT' operations.
- A. Incorrect.
This option uses the 'dropDuplicates' method, but it does not ensure that the latest record based on the 'updated_at' column is kept. This approach is incorrect for the given scenario.
- B. Incorrect.
Although this option attempts to use 'orderBy' before calling 'dropDuplicates', Spark does not guarantee that the latest record will be kept when using 'dropDuplicates'. This approach is incorrect for the given scenario.
- C. Correct.
This option correctly uses a window function to partition the data by 'transaction_id' and order by the 'updated_at' column in descending order. It retains only the latest record for each 'transaction_id' and writes the deduplicated data back to the Delta table. This approach is correct.
- D. Incorrect.
This option uses an SQL query with 'DISTINCT', which does not account for retaining the latest record based on the 'updated_at' column. This approach is incorrect for the given scenario.