DELETE + INSERT vs MERGE in BigQuery

UpdatedMarch 2, 2026

•1 min read

Constantin Lungu

Senior Data Engineer • Contractor / Freelancer • GCP & AWS Certified

Part of seriesPractical SQL

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!

Found it useful? Check out to my Analytics newsletter at notjustsql.com.

Comments

Join the discussion

No comments yet. Be the first to comment.

Practical SQL

Part 1 of 50

Short, practical posts on SQL and BigQuery — from core language features to advanced query patterns. A reference for data practitioners at every level.

More from this blog

BigQuery BigLake Tables Explained: What They Are and When to Use Them

Query your data lake with warehouse-grade security and performance — without moving a single file.

Apr 26, 20264 min read

BigQuery BigLake Tables Explained: What They Are and When to Use Them

BigQuery Saves Your Query Results — Here's How to Find Them

Ever run a heavy BigQuery SQL query, processed gigabytes of data — and then accidentally closed the tab or forgot to save the results? 😬 Don't re-run it. Your results are still there. BigQuery automa

Mar 5, 20261 min read

BigQuery Saves Your Query Results — Here's How to Find Them

Parameters in BigQuery

You can use query parameters in BigQuery hashtag#SQL (now in the console as well!) — but how are they different from variables, and when should you use each? Both parameters and variables act as place

Feb 7, 20261 min read

Flattening JSON arrays in BigQuery

I've noticed that a new JSON function has been added (in Preview) in BigQuery SQL - JSON_FLATTEN(). It allows us to flatten JSON arrays and return a single flat ARRAY, no matter how many nested levels there are. So where is this actually useful?➡️ Ha...

Dec 7, 20251 min read

Using LAST_VALUE with STRUCTS

Even an “empty” STRUCT is still technically something. Not the same as a standalone NULL value. This is why, if you work with STRUCTs in SQL and try to find the latest non-empty struct using LAST_VALUE(...) IGNORE NULLS, you’ll notice it doesn’t help...

Oct 5, 20251 min read

Datawise — SQL, BigQuery & Python for Data Engineers

205 posts

Data Engineer with a passion for transforming complex data landscapes into insightful stories. Here on my blog, I share insights, challenges, and the ever-evolving dance of technology and business.

Command Palette

Comments

Practical SQL

More from this blog