DELETE + INSERT vs MERGE in BigQuery

How do you merge changes from staging tables into target tables in BigQuery?

I've previously covered swapping out partitions using bq command and using constant false predicate "MERGE on FALSE", but I've learned that you can now DELETE entire partitions for free (provided a filter on the partitioned column is used) from tables.

That means that instead of merging your changes the old-fashioned way, it might be well worth DELETING the days you would like to update and INSERTING the entire days data back sourced from the staging table.

Here's a comparison of the two approaches for the same source and destination tables. As you can see the amount of processed data can be wildly different between the two.

DELETE FROM learning.data_source WHERE ds_date >= "2023-09-02";
INSERT INTO learning.data_source
SELECT * FROM learning.data_source_staging;

MERGE `learning.data_source` AS t
USING `learning.data_source_staging` AS s ON t.id = s.id AND t.ds_date = s.ds_date
WHEN NOT MATCHED THEN INSERT (id,value,ds_date) VALUES(id,value,ds_date)
WHEN MATCHED THEN UPDATE SET t.value = s.value

Happy querying!