Databricks Data Engineer Associate Question 336
Single answerYou are working with a Delta Lake table named sales_data that contains daily sales records. A new dataset is received daily with updated sales records and new entries. The goal is to update existing records in the table where the order_id matches and insert new records for orders that do not yet exist. Which approach should you use to handle this scenario?
- A
Use the MERGE INTO statement to match records based on the
order_idand specify update and insert actions. - B
Use the INSERT INTO statement to add all incoming records directly to the Delta Lake table without further processing.
- C
Use the UPDATE statement to update existing records based on
order_idand then use the INSERT INTO statement to add new records. - D
Use the DELETE statement to remove existing records with matching
order_idfrom the Delta Lake table and then append the new dataset.
Show answer and explanation
Correct answer: A
Explanation
The MERGE INTO statement is the optimal solution for scenarios where you need to perform both updates and inserts based on a matching condition, such as order_id. It ensures that existing records in the target table are updated while new records are inserted, all in a single atomic operation. This makes MERGE well-suited for handling incremental data updates in Delta Lake.
- A. Correct.
This is the correct approach. The MERGE INTO statement is specifically designed to handle scenarios where you need to match records between a source and target table to perform both updates to existing records and insertions of new records.
- B. Incorrect.
This is incorrect because the INSERT INTO statement alone does not check for existing records, leading to potential duplicates in the Delta Lake table.
- C. Incorrect.
This is incorrect because using separate UPDATE and INSERT INTO statements requires additional processing and is less efficient than using MERGE, which handles both actions in a single operation.
- D. Incorrect.
This is incorrect because deleting existing records and appending new data is inefficient and can lead to data loss or inconsistency.