DELETE + INSERT vs MERGE in BigQuery

Photo by Sam Pak on Unsplash

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.

Happy querying!