Databricks Data Engineer Associate Question 335
Single answerYou are working with a Delta table in Databricks that stores user account information. A new dataset is received daily, containing a mix of new user records and updates to existing user records. Which operation is the most appropriate to efficiently handle inserting new records and updating existing records in the Delta table?
- A
Use the MERGE operation to match records from the new dataset with the existing Delta table and perform updates or inserts accordingly.
- B
Use the INSERT INTO operation to append the new dataset to the Delta table, and then run an UPDATE query to modify existing records.
- C
Use the DELETE operation to remove all records from the Delta table and then reload it with the combined data from the Delta table and the new dataset.
- D
Use the COPY INTO operation to load the new dataset into the Delta table, as it supports both inserts and updates.
Show answer and explanation
Correct answer: A
Explanation
The MERGE operation in Databricks is designed for handling upsert scenarios, where you have a mix of new records to insert and existing records to update. It allows you to define matching conditions and specify actions for matched and unmatched rows in a single, efficient operation. This makes it the optimal choice for efficiently handling the described use case.
- A. Correct.
This is the correct option. MERGE is specifically designed to handle scenarios where you need to upsert (update existing records and insert new records) in a Delta table by matching records based on a condition.
- B. Incorrect.
This is not the most efficient approach. While you could use INSERT INTO followed by UPDATE, it requires multiple steps and operations, which is less efficient than using MERGE.
- C. Incorrect.
This is incorrect. Deleting all records and reloading the table from scratch is highly inefficient and unnecessary for upserting data.
- D. Incorrect.
This is incorrect. COPY INTO is used for loading data into Delta tables but does not support updating existing records in the table.