MERGE ON FALSE in BigQuery

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.

-- NO PK/FK constraints  on the target table

MERGE `learning.data_source`  AS target

USING (

SELECT 44 AS id, 10 AS value, DATE('2023-09-01') AS ds_date

UNION ALL

SELECT 1000 AS id, 11 AS  value, DATE('2023-09-02') AS ds_date

) AS source

ON FALSE

WHEN NOT MATCHED BY TARGET THEN

INSERT ROW

WHEN MATCHED THEN

UPDATE SET target.value = source.value

-- NO PK/FK constraints  on the target table

MERGE `learning.data_source`  AS target

USING (

SELECT 33 AS id, 7 AS value, DATE('2015-02-11') AS ds_date

UNION ALL

SELECT 999 AS id, 10 AS  value, DATE('2023-09-16') AS ds_date

) AS source

ON source.id = target.id AND source.ds_date = target.ds_date

WHEN NOT matched BY TARGET THEN

INSERT (id,ds_date,value)

VALUES (source.id, source.ds_date, source.value)

WHEN MATCHED THEN

UPDATE SET target.value = source.value

Testing that the expected changes happened.

WITH test_cases AS (


SELECT 33 AS id, 7 AS value, DATE('2015-02-11') AS ds_date

UNION ALL

SELECT 999 AS id, 10 AS  value, DATE('2023-09-16') AS ds_date

UNION ALL

SELECT 44 AS id, 10 AS value, DATE('2023-09-01') AS ds_date

UNION ALL

SELECT 1000 AS id, 11 AS  value, DATE('2023-09-02') AS ds_date
)

SELECT ds.* FROM learning.data_source ds

JOIN test_cases t USING (id, ds_date)

Using the table version that has Primary Key and Foreign Key constraints has yielded even more impressive results.

-- PK/FK constraints  on the target table

MERGE `testing.data_source`  AS target

USING (

SELECT 44 AS id, 10 AS value, DATE('2023-09-01') AS ds_date

UNION ALL

SELECT 1000 AS id, 11 AS  value, DATE('2023-09-02') AS ds_date

) AS source

ON FALSE

WHEN NOT MATCHED BY TARGET THEN

INSERT ROW

WHEN MATCHED THEN

UPDATE SET target.value = source.value

-- PK/FK constraints  on the target table

MERGE `testing.data_source`  AS target

USING (

SELECT 33 AS id, 7 AS value, DATE('2015-02-11') AS ds_date

UNION ALL

SELECT 999 AS id, 10 AS  value, DATE('2023-09-16') AS ds_date

) AS source

ON source.id = target.id AND source.ds_date = target.ds_date

WHEN NOT matched BY TARGET THEN

INSERT (id,ds_date,value)

VALUES (source.id, source.ds_date, source.value)

WHEN MATCHED THEN

UPDATE SET target.value = source.value

Thanks for reading!