Question

A data engineer is writing a MERGE statement to update a Delta table of customer accounts. Business rules require that when a matched record has a status of 'CLOSED' in the source, the target row should be deleted; for all other matched records, the target row should be updated with the new balance. Which MERGE clause construction correctly implements both behaviors?

Accepted Answer

Use two WHEN MATCHED clauses: the first with condition `s.status = 'CLOSED'` THEN DELETE, followed by a second WHEN MATCHED THEN UPDATE SET t.balance = s.balance.

Answer

Use a single WHEN MATCHED THEN UPDATE clause and rely on a separate DELETE statement run before the MERGE.

Answer

Use two WHEN MATCHED clauses ordered as UPDATE first, then the DELETE with condition `s.status = 'CLOSED'`, since order does not affect evaluation.

Answer

Use a single WHEN MATCHED clause with a CASE expression that returns DELETE or UPDATE based on `s.status`.