Explanation:
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.
Ultimate access to all questions.
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 *
No comments yet.