Keeping BigQuery costs low

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).

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.