Question

A data engineer maintains a Silver Delta table `customers` and receives a daily source batch `updates` containing both new and existing customer records, keyed by `customer_id`. The requirement is: when a `customer_id` already exists in the target, update its `email` and `status` columns; when a `customer_id` does not exist in the target, insert the full new row. Which single Spark SQL statement correctly implements this in one pass?

Accepted Answer

MERGE INTO customers t USING updates s ON t.customer_id = s.customer_id WHEN MATCHED THEN UPDATE SET t.email = s.email, t.status = s.status WHEN NOT MATCHED THEN INSERT *

Answer

INSERT INTO customers SELECT * FROM updates; then run UPDATE customers SET email = ... to fix duplicates afterward

Answer

MERGE INTO customers t USING updates s ON t.customer_id = s.customer_id WHEN NOT MATCHED THEN UPDATE SET t.email = s.email WHEN MATCHED THEN INSERT *

Answer

INSERT OVERWRITE customers SELECT * FROM updates