Databricks Data Engineer Associate Question 337
Single answerYou are working with a Delta table named 'customer_transactions' that tracks customer purchases. Your team needs to ensure that the table is updated with the latest transaction data from a source DataFrame, 'new_transactions'. The source DataFrame may contain new transactions, updates to existing transactions, or records that should not be updated if already present. Which operation should you use to efficiently update the Delta table?
- A
Use the MERGE operation to handle inserts, updates, and conditional logic based on matching records.
- B
Use the INSERT INTO statement to append all records from the source DataFrame to the Delta table.
- C
Use the UPDATE statement to update records in the Delta table based on a condition.
- D
Use the TRUNCATE TABLE statement to clear the Delta table and then reload all records from the source DataFrame.
Show answer and explanation
Correct answer: A
Explanation
The MERGE operation is the ideal choice for this scenario because it allows you to combine data from a source DataFrame with a Delta table by performing conditional inserts, updates, or no action based on whether a match is found. This ensures that the Delta table is updated efficiently and accurately with the latest transaction data.
- A. Correct.
The MERGE operation is specifically designed to handle complex use cases where data needs to be merged into a Delta table with conditional logic for inserts and updates. This is the most efficient approach for this scenario.
- B. Incorrect.
The INSERT INTO statement only appends data to a Delta table, and it cannot handle updates or conditional logic. This would result in duplicate or incomplete data in this scenario.
- C. Incorrect.
The UPDATE statement is only capable of modifying existing records but cannot handle new inserts from the source DataFrame. It is not suitable for this use case.
- D. Incorrect.
The TRUNCATE TABLE statement clears all data from the Delta table, which would result in loss of existing data and is not appropriate when only updates and inserts are required.