Question

A data engineer maintains a Silver dimension table `dim_customer` and receives a daily batch of updated customer records in a staging view `stg_customer`. Both tables share the key `customer_id`. The requirement is: update existing customers with new attribute values, and insert customers that do not yet exist. Which single Spark SQL statement correctly implements this upsert?

Accepted Answer

MERGE INTO dim_customer t USING stg_customer s ON t.customer_id = s.customer_id WHEN MATCHED THEN UPDATE SET * WHEN NOT MATCHED THEN INSERT *

Answer

INSERT OVERWRITE dim_customer SELECT * FROM stg_customer

Answer

MERGE INTO dim_customer t USING stg_customer s ON t.customer_id = s.customer_id WHEN NOT MATCHED BY SOURCE THEN DELETE

Answer

INSERT INTO dim_customer SELECT * FROM stg_customer