Answer-first summary for fast verification
Answer: MERGE INTO employees e USING employees_updates eu ON e.id = eu.id WHEN MATCHED AND eu.type = “former“ THEN DELETE WHEN NOT MATCHED AND eu.type = “new“ THEN INSERT *
Databricks utilizes the `MERGE` statement to perform updates, inserts, and deletes in a single command. For this scenario, when an employee in the `employees_updates` view is marked as 'former' and exists in the `employees` Delta table, they will be deleted. Conversely, if an employee is marked as 'new' and does not exist in the `employees` table, they will be inserted. The correct query that handles both conditions is: `MERGE INTO employees e USING employees_updates eu ON e.id = eu.id WHEN MATCHED AND eu.type = 'former' THEN DELETE WHEN NOT MATCHED AND eu.type = 'new' THEN INSERT *`. Learn more about the MERGE INTO statement on Databricks.
Author: LeetQuiz Editorial Team
Ultimate access to all questions.
No comments yet.
You have a view named employees_updates that displays data of employees who have either joined or resigned from your organization in the last month. The view includes a column type indicating the employee's status. There's also a Delta table employees containing data for all current employees. Your goal is to integrate this Change Data Capture (CDC) feed by adding new employees and removing former employees from the employees Delta table. Which query achieves this?
A
MERGE INTO employees e USING employees_updates eu ON e.id = eu.id WHEN MATCHED AND eu.type = “former“ THEN DELETE * WHEN NOT MATCHED AND eu.type = “new“ THEN INSERT ALL
B
UPSERT INTO employees e USING employees_updates eu ON e.id = eu.id WHEN MATCHED AND eu.type = “former“ THEN DELETE WHEN NOT MATCHED AND eu.type = “new“ THEN INSERT *
C
MERGE INTO employees e USING employees_updates eu ON e.id = eu.id WHEN MATCHED AND eu.type = “former“ THEN DELETE WHEN NOT MATCHED AND eu.type = “new“ THEN INSERT *
D
MERGE INTO employees e USING employees_updates eu ON e.id = eu.id IF MATCHED AND eu.type = “former“ THEN DELETE IF NOT MATCHED AND eu.type = “new“ THEN INSERT *
E
MERGE INTO employees e USING employees_updates eu ON e.id = eu.id WHEN MATCHED AND eu.type = “former“ THEN DELETE ALL WHEN NOT MATCHED AND eu.type = “new“ THEN INSERT *