Databricks Data Engineer Associate Question 334
Single answerA retail company maintains a Delta Lake table to track inventory levels for all its products. The table contains columns for product_id, location_id, and inventory_count. The company receives a daily update file with new inventory data, which includes both new products and updates to existing inventory levels. What is the most appropriate way to efficiently update the Delta Lake table with the daily update file?
- A
Use the MERGE operation to update existing records and insert new ones as necessary.
- B
Use the INSERT INTO operation to append the update file to the Delta Lake table.
- C
Use the DELETE operation to remove records that match the daily update file, followed by an INSERT INTO to add all records from the update file.
- D
Use the UPDATE operation to modify existing records and the INSERT INTO operation to add new records.
Show answer and explanation
Correct answer: A
Explanation
The MERGE operation is the ideal choice for scenarios where you need to upsert (update existing records and insert new ones) in a Delta Lake table. It combines these actions into a single, optimized operation, making it efficient and reliable for processing updates like the daily inventory file.
- A. Correct.
This is correct. MERGE is specifically designed for scenarios where you need to update existing records and insert new ones in a Delta Lake table, making it the most efficient option for this use case.
- B. Incorrect.
This is incorrect. INSERT INTO only appends data to a table and does not handle updates to existing records, so it would result in duplicate entries for existing products.
- C. Incorrect.
This is incorrect. Using DELETE followed by INSERT INTO is inefficient and error-prone compared to the MERGE operation, which combines both actions in a single, optimized step.
- D. Incorrect.
This is incorrect. Using separate UPDATE and INSERT INTO operations is not as efficient or straightforward as using a MERGE operation, which handles both actions in one command.