Guides
Keeping BigQuery costs low
How BigQuery bills CDC MERGE jobs and how to minimise cost: insert-only merges, clustering, partitioning, refresh interval and change-history retention.
Your data lives in your own Google Cloud project, so Google bills BigQuery to you directly. With on-demand pricing, the main cost of CDC is the queries that apply changes, not storage or ingestion.
How BigQuery bills a refresh
A MERGE with an UPDATE or DELETE clause is billed for the entire target table, or for the target partitions it touches if the table is partitioned. A MERGE with only an INSERT clause is billed only for the rows it reads. There is also a 10 MB minimum per table referenced.
What Quayen does automatically
- Insert-only refreshes. When a batch has no updates or deletes (common for logs, events and new orders), the refresh skips the full-table scan.
- Clustered change history. History is clustered by log position, so each refresh reads only new changes.
- Compressed history storage. The change-history dataset uses physical (compressed) storage billing and a 2-day time-travel window.
- Only changed tables. Tables with no new changes are skipped entirely.
What you can tune
- Refresh interval. Refresh cost scales with how often tables refresh. Every 15 minutes costs roughly a third of every 5 minutes for busy tables. Change it any time in the pipeline's settings.
- Partition large tables by a column that is set once, like
created_at, so refreshes and your own queries touch fewer partitions. - Retention. Keep change history only as long as you need it (3 days to forever).
Using clustering to reduce BigQuery cost
Clustering sorts a table's storage by up to four columns. When a query filters or joins on those columns, BigQuery skips the blocks that can't match, so on-demand queries are billed for fewer bytes. Clustering is free to set up, and BigQuery re-clusters tables in the background at no charge as new rows arrive.
- Cluster by what you filter on. Customer ID, account ID or status columns that appear in most
WHEREclauses. Put the column you filter on most first. - Combine with partitioning. Partition by date to cut whole days out of a scan, then cluster within each partition.
- Expect a higher estimate than the bill. The dry-run estimate before a query doesn't account for clustering, so the bytes actually billed are often lower.
Quayen clusters each replicated table by its primary key, which keeps merges and lookups by ID cheap. You can change the clustering columns of a table at any time with bq update --clustering_fields; new data is clustered by the new columns.
Check what you're spending
SELECT DATE(creation_time) AS day, COUNT(*) AS refreshes,
ROUND(SUM(total_bytes_billed) / POW(1024, 3), 1) AS gib_billed
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
AND statement_type = 'MERGE'
GROUP BY day ORDER BY day;Replace region-us with your dataset's location, for example region-asia-south1.
To estimate refresh costs before you start, try the BigQuery MERGE cost calculator.