Databricks Data Engineer Associate Question 123
Select 2You are working with a Delta Lake table named 'sales_data' that contains duplicate rows. You need to deduplicate the table by keeping only the latest record for each unique 'transaction_id' based on a column named 'timestamp'. Which of the following code snippets would correctly deduplicate the table?
- A
Use the SQL query:
CREATE OR REPLACE TABLE sales_data AS SELECT * FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY transaction_id ORDER BY timestamp DESC) AS row_num FROM sales_data) WHERE row_num = 1 - B
Use the SQL query:
MERGE INTO sales_data USING (SELECT DISTINCT transaction_id, MAX(timestamp) AS timestamp FROM sales_data GROUP BY transaction_id) ON sales_data.transaction_id = transaction_id WHEN MATCHED THEN UPDATE SET * - C
Use the PySpark code:
df = spark.table('sales_data').dropDuplicates(['transaction_id']).orderBy(desc('timestamp')) - D
Use the PySpark code:
from pyspark.sql.functions import row_number, desc, col; window_spec = Window.partitionBy('transaction_id').orderBy(desc('timestamp')); deduped_df = spark.table('sales_data').withColumn('row_num', row_number().over(window_spec)).filter(col('row_num') == 1).drop('row_num'); deduped_df.write.format('delta').mode('overwrite').saveAsTable('sales_data')
Show answer and explanation
Correct answers: A, D
Explanation
Deduplicating a Delta Lake table requires ensuring that only one row is retained for each unique key (in this case, 'transaction_id'), and that the retained row is based on a specified condition (e.g., the latest 'timestamp'). Both the SQL query with ROW_NUMBER() and the PySpark code with the row_number() window function achieve this goal by ranking rows and filtering for the top-ranked row within each group. The other options either fail to preserve the correct record or misuse the available functions.
- A. Correct.
This SQL query uses the ROW_NUMBER() function to assign a unique rank to each row within a 'transaction_id' partition based on the descending 'timestamp'. The query then filters to keep only the row with a rank of 1. This is a correct approach to deduplicating the Delta Lake table.
- B. Incorrect.
This SQL query is incorrect because the
MERGE INTOstatement is not suited for deduplication in this way. TheSELECT DISTINCTandMAXfunctions do not ensure that all columns from the latest record are preserved. - C. Incorrect.
This PySpark code is incorrect because the
dropDuplicates()function alone does not ensure that the latest record is kept when multiple duplicates exist. Additionally, theorderBymethod is not applied correctly in this context. - D. Correct.
This PySpark code correctly uses the
row_number()function within a window specification to rank rows by 'transaction_id' and descending 'timestamp'. It then filters for rows with rank 1 and overwrites the Delta Lake table, which is a valid deduplication approach.