BigQuery MERGE Performance: Why Incremental Pipelines Become Slower Every Month
Most BigQuery pipelines do not fail.
They simply become slower, more expensive, and increasingly unpredictable until engineers accept the degradation as “normal.”
That assumption costs companies millions of dollars every year.
This investigation began after a fintech company noticed that its nightly data pipelines were gradually extending into business hours. Nothing dramatic had happened overnight. There was no failed deployment, no infrastructure outage, and no sudden increase in customer traffic. Instead, execution time had increased so gradually that nobody could identify when the problem had actually started.
Twelve months earlier, the nightly transformation pipeline completed in 52 minutes. Six months later it required 1 hour and 18 minutes. By the beginning of the investigation, the same workload consistently ran for 2 hours and 47 minutes. Several downstream reports were no longer available before the start of the business day, forcing Finance and Risk teams to delay reconciliation and fraud analysis by nearly an hour every morning.
The obvious explanation was data growth.
During the previous year, the main transaction table had expanded from 2.1 billion to 8.9 billion rows. At first glance, the slower execution appeared completely justified.
The engineering team decided to verify that assumption rather than accept it.
Using BigQuery INFORMATION_SCHEMA.JOBS_BY_PROJECT, they extracted execution statistics for every production pipeline over the previous twelve months. Execution time, bytes processed, slot consumption, shuffle volume, and destination tables were plotted against data growth.
The relationship looked surprisingly weak.
Data volume had increased by approximately 4.2 times.
Pipeline duration had increased by more than 3.2 times.
However, bytes processed during several transformation stages had increased by almost ten times.
Something other than simple growth was occurring.
The investigation narrowed to the ten most expensive jobs.
Nine of them shared one common characteristic.
Each relied on MERGE statements to update incremental tables.
MERGE is one of BigQuery’s most useful features. It allows engineers to insert new records, update existing ones, and delete obsolete data using a single SQL statement. Correctly implemented, it simplifies incremental processing and dramatically reduces operational complexity.
Incorrectly implemented, it becomes one of the most expensive SQL patterns in large analytical platforms.
The company maintained 64 incremental tables.
Every night, each table received only the previous day’s transactions—approximately 11 million new records.
That sounded efficient.
The destination tables, however, already contained several billion historical rows.
The engineering team downloaded execution plans for representative MERGE operations.
The results immediately explained the cost increase.
Although only 11 million rows required modification, BigQuery was reading almost the entire destination table before deciding which records needed updating.
One representative pipeline processed:
- Source table: 11.3 million rows
- Destination table: 8.9 billion rows
- Data actually changed: 0.13%
- Data scanned during MERGE: 27.4 TB
The SQL was technically correct.
The economics were disastrous.
The engineers estimated the financial impact.
The MERGE executed every night.
Average execution cost was approximately $137.
That single transformation cost almost $50,000 per year.
The platform contained 64 similar workloads.
Not every pipeline was equally inefficient, but together they represented more than 38% of the company’s monthly BigQuery compute bill.
The obvious recommendation would have been to eliminate MERGE entirely.
That recommendation would have been wrong.
MERGE was not the problem.
The implementation was.
To understand why, the engineers rebuilt one pipeline from scratch and compared three different incremental strategies using exactly the same production dataset.
The outcome challenged several widely accepted BigQuery best practices—and explained why many incremental pipelines become slower every quarter despite processing almost the same amount of new data.
The engineering team selected one of the most expensive production pipelines and rebuilt it three different ways. The objective was not to prove that one SQL pattern was universally superior, but to identify the point at which a perfectly reasonable incremental strategy becomes economically unsustainable.
The pipeline loaded approximately 11 million new transactions every night into a fact table containing 8.9 billion historical records. Only 0.13% of the destination table changed during a typical execution.
Three implementation strategies were tested against the same production data.
The first implementation retained the existing MERGE statement. The second replaced it with a partition-based overwrite, rebuilding only the affected daily partitions. The third introduced a staging table that isolated changed business keys before performing a highly selective MERGE against the destination table.
The results surprised even experienced BigQuery engineers.
| Strategy | Data Scanned | Runtime | Monthly Compute Cost |
|---|---|---|---|
| Standard MERGE | 27.4 TB | 43 min | $4,180 |
| Partition Overwrite | 1.1 TB | 8 min | $176 |
| Selective MERGE with Staging | 2.4 TB | 12 min | $364 |
The standard MERGE was not slow because MERGE is inherently inefficient.
It was slow because BigQuery had to compare millions of incoming rows against billions of historical rows to determine which records required modification.
The destination table had become too large for the original implementation strategy.
The investigation then focused on why the problem had escaped code review.
The SQL itself looked clean.
Indexes are not managed in BigQuery as they are in traditional relational databases, so nothing appeared obviously incorrect. Partitioning and clustering were already configured. The pipeline completed successfully every night.
The missing analysis concerned business change patterns.
The engineering team examined twelve months of historical data and discovered that 98.7% of corrections affected only the most recent seven days of transactions.
Records older than thirty days were almost never updated.
Yet every MERGE operation still compared incoming data against the entire historical table.
The company had optimized for theoretical correctness instead of observed business behavior.
The engineers redesigned the pipeline around that evidence.
Recent partitions remained fully mutable.
Older partitions became effectively immutable unless an exceptional correction process was triggered.
Instead of scanning years of history every night, the pipeline compared changes only against the partitions where updates actually occurred.
The improvement was dramatic.
Average nightly runtime fell from 2 hours and 47 minutes to 58 minutes.
Total bytes processed by incremental pipelines decreased by 72%.
The Finance team once again received reconciliation reports before the start of the business day.
More importantly, the optimization remained effective six months later because it scaled with business behavior rather than table size.
The investigation uncovered another expensive pattern.
Several pipelines executed multiple MERGE statements against the same destination table during a single workflow.
One process updated customer status, another corrected marketing attribution, and a third enriched geographic information.
Each MERGE independently scanned the destination table.
Combining those operations into a single transformation reduced scanned data by nearly 65% without changing business logic.
The engineers calculated the economics of the entire optimization program.
| Metric | Before | After |
| Nightly pipeline duration | 2 h 47 m | 58 m |
| Data scanned each night | 412 TB | 116 TB |
| Monthly BigQuery compute | $121,000 | $82,900 |
| Estimated annual savings | — | $457,000 |
Interestingly, rewriting SQL accounted for only part of the savings.
The larger improvement came from changing how engineers designed incremental processing.
Instead of asking, “How do we update this table?”, they started asking, “How often does this data actually change?”
That question fundamentally changed architecture decisions.
Some pipelines continued using MERGE because business requirements genuinely demanded row-level updates.
Others switched to partition replacement because historical corrections were rare.
A few adopted hybrid strategies, combining staging tables with selective updates only where business evidence justified the additional complexity.
The lesson extended well beyond BigQuery.
Incremental processing is not a technical pattern.
It is a business pattern expressed in SQL.
When engineers understand how data changes, pipelines remain fast for years.
When they assume that every record might change every day, compute costs grow faster than the business itself.
What Most Teams Do Wrong
A common implementation looks like this:
- Create one large fact table.
- Append new data every day.
- Use a generic MERGE for all updates.
- Allow historical corrections indefinitely.
- Watch runtime and cloud costs increase every quarter.
A more sustainable approach is:
- Measure how data actually changes.
- Define a realistic correction window.
- Partition by business update patterns, not habit.
- Merge only where updates genuinely occur.
- Periodically review whether the original incremental strategy still matches current data volumes.
Evidence Collected
INFORMATION_SCHEMA.JOBS_BY_PROJECT- BigQuery execution plans
- Cloud Billing Export
- Partition statistics
- Dataform execution history
- Cloud Monitoring runtime metrics
- Historical correction frequency analysis
Executive Recommendations
- Review every MERGE statement once destination tables exceed one billion rows.
- Measure correction frequency before selecting an incremental strategy.
- Prefer partition replacement when historical updates are exceptionally rare.
- Consolidate multiple MERGE operations targeting the same table.
- Evaluate incremental pipelines annually—what was efficient at 500 million rows may become prohibitively expensive at 10 billion.
The Question Every CTO Should Ask
“Are our incremental pipelines designed around how the business actually changes data—or around assumptions we made years ago when our datasets were ten times smaller?”