Databricks Data Engineer Professional Question 104
Single answerYou are working on a data engineering project where a source table contains customer data. The source table is being updated using Change Data Feed (CDF), and occasionally, records are deleted from the source table. You need to ensure that these deletions are propagated to a target Delta table that is used for downstream analytics. What is the best approach to implement this functionality?
- A
Use the 'MERGE INTO' operation with a condition to match deleted records using the '_change_type' column from the source CDF data.
- B
Perform an 'INSERT INTO' operation from the source CDF data into the target Delta table to capture deletions.
- C
Filter the CDF data for rows where '_change_type' is 'delete' and use the 'DELETE' statement on the target Delta table.
- D
Replace the entire target Delta table with the source Delta table to ensure deletions are reflected in the target.
Show answer and explanation
Correct answer: A
Explanation
Change Data Feed (CDF) provides the '_change_type' column, which can be used to determine if a record was inserted, updated, or deleted. The 'MERGE INTO' operation is specifically designed to handle all these scenarios in a single atomic operation, making it the most efficient and reliable way to propagate deletes (and other changes) from a source table to a target Delta table. Other approaches either lack the ability to handle all change types or are inefficient.
- A. Correct.
Correct: The 'MERGE INTO' operation is the most appropriate way to propagate deletions because it allows you to handle updates, inserts, and deletes in a single transaction. By using the '_change_type' column provided by CDF, you can identify deleted records and apply the necessary changes to the target table.
- B. Incorrect.
Incorrect: 'INSERT INTO' only appends data to the target table and does not handle deletions. This approach would not propagate deletions from the source table.
- C. Incorrect.
Incorrect: While filtering the CDF data for '_change_type' as 'delete' is correct, the 'DELETE' statement alone cannot handle other changes (e.g., updates or inserts) and is not optimal for comprehensive CDF handling.
- D. Incorrect.
Incorrect: Replacing the entire target Delta table with the source table is inefficient and does not leverage the benefits of CDF for incremental updates and deletes. This approach is not recommended in a production setting.