BigQuery MERGE ON FALSE: Atomic Delete and Insert

Merge statements are essential for crafting incremental datasets. They let us INSERT, UPDATE, and DELETE in a single command. 🛠️
Recently, I dived into an insightful blog post about the ON FALSE clause in merge statements. Ever heard of it?

Typical merge:

MERGE table1 AS target
USING table2 AS source ON table1.column = table2.column
WHEN MATCHED -- e.g., update target
WHEN NOT MATCHED BY source -- e.g., delete from target
WHEN NOT MATCHED BY target -- e.g., insert in target

But with ON FALSE in the merge_condition? BigQuery docs call it a "constant false predicate", perfect for atomic DELETEs on the target and INSERTs from a source. Essentially, a REPLACE operation.

I tested this on some data, especially after my previous post on Primary and Foreign Keys. The outcomes are looking super promising.