How much will CDC merges cost in BigQuery?
Keeping a BigQuery table in sync means running a MERGE on a schedule, and BigQuery bills each one by the data it scans. Estimate what that costs for your tables and refresh interval.
Append-only tables (events, logs) are near 0%. Tables edited all day are near 100%.
Estimated BigQuery cost of applying changes
$5,274 / month
843.9 TiB scanned by 8,640 MERGE jobs a month, at $6.25 per TiB on-demand.
| Refresh every | TiB / month | Cost / month |
|---|---|---|
| 1 min | 4,219.4 | $26,371 |
| 5 min | 843.9 | $5,274 |
| 15 min | 281.3 | $1,758 |
| 30 min | 140.6 | $879 |
| 1 hour | 70.3 | $440 |
Assumes on-demand pricing in the US multi-region and that each MERGE with an update or delete scans every column of the target table or the partitions it touches. The first 1 TiB of queries a month is free per billing account; storage and your own queries are extra. Capacity (editions) pricing bills slots instead of bytes.
Why applying changes costs money in BigQuery
With on-demand pricing, BigQuery bills queries by bytes scanned. A MERGE that updates or deletes rows is billed for the whole target table, or for the partitions it touches if the table is partitioned. A MERGE that only inserts is billed for the rows it reads. Every table a query references is billed for at least 10 MB.
So a 200 GiB table refreshed every 5 minutes can scan tens of TiB a month even if only a few rows change, while an append-only table costs almost nothing.
How to lower it
- Refresh less often. Cost scales with the number of merges; every 15 minutes costs about a third of every 5 minutes.
- Partition large tables by a column that is set once, like
created_at, so each merge touches fewer partitions. See partitioning replicated tables. - Skip unnecessary merges. Use insert-only merges when a batch has no updates or deletes, and skip tables with no new changes.
- Consider capacity pricing (BigQuery editions) for heavy, steady workloads, which bills slots instead of bytes.
Quayen does the insert-only and skip-unchanged optimizations automatically and lets you choose the refresh interval per pipeline. More in keeping BigQuery costs low.
Questions
How much does a BigQuery MERGE cost?
On-demand, $6.25 per TiB scanned in the US multi-region, after the first free TiB each month. A MERGE with UPDATE or DELETE scans the target table or the partitions it touches; an insert-only MERGE scans just the new rows.
Does refreshing every minute cost five times more than every five minutes?
For tables where most batches include updates or deletes, roughly yes, because each MERGE scans the table again. Append-only tables barely change in cost.
Does partitioning reduce MERGE cost?
Yes, when changes land in a few partitions, for example recent dates. A MERGE then scans only the partitions it touches instead of the whole table.
Does the estimate include BigQuery storage?
No. It covers only the queries that apply changes. Storage and your own analysis queries are billed separately.
Your MySQL data in BigQuery today
Free during early access. Set it up yourself in about 15 minutes, or we'll do it with you on a call.