Streaming CDC to BigQuery: Storage Write API, Ingestion Modes, and Cost Patterns

BigQuery’s billing model for streaming ingestion catches teams off-guard. The legacy streaming API charges per byte, with no free tier: a high-churn CDC table writing thousands of events per second accumulates cost quickly, and the bill arrives before anyone has thought carefully about the ingestion architecture. Then there’s the secondary surprise: rows streamed into BigQuery don’t update in place. Every CDC event appends a new row, and “current state” has to be computed on top of that event log.

This guide covers how CDC events actually land in BigQuery, which ingestion path to use, what it costs, and how to maintain a clean table for downstream queries.

BigQuery Ingestion Options for CDC Pipelines

Four paths exist for getting data into BigQuery. For streaming CDC, two of them matter.

The legacy streaming API (tabledata.insertAll) was the original real-time ingestion path. It still works but is officially deprecated for new workloads. Pricing is $0.01 per 200MB ingested, with no free tier. For a CDC pipeline generating 100GB per day, that’s $5/day before any query costs, though significantly higher than the alternative.

The Storage Write API is the current recommended path. It has two modes relevant to CDC:

Default stream writes rows with at-least-once semantics. It’s the simplest to implement and doesn’t require pre-allocated write streams. For CDC use cases where the downstream deduplication query handles duplicates anyway, the default stream is a reasonable choice.

Committed stream writes rows with exactly-once semantics via a two-phase protocol: the client appends rows to an uncommitted buffer, then finalizes (commits) a batch. This prevents duplicate rows from connection retries, which matters when the connector might resend events after a network interruption.

Pricing for the Storage Write API: the first 10GB per month is free; beyond that it’s $0.025/GB. The legacy streaming API charges $0.05/GB ($0.01 per 200MB) with no free tier. A pipeline ingesting 100GB per day spends roughly $75/month on the Storage Write API versus $150/month on the legacy API. Both are reasonable at that scale, but the gap widens quickly on high-churn tables running into the terabyte-per-day range.

New pipelines should use the Storage Write API. Streamkap’s BigQuery connector uses the Storage Write API and the committed stream by default.

How CDC Events Land in BigQuery: The Append Pattern

BigQuery doesn’t support row-level upserts the way a relational database does. When CDC events arrive, they’re appended as new rows. Each row carries metadata columns that identify the operation type and source timestamp:

• _streamkap_op: the CDC operation (c for create, u for update, d for delete)
• _streamkap_source_ts_ms: the source database timestamp of the change

An INSERT at the source database creates one new row in BigQuery. An UPDATE creates another row with the updated values and _streamkap_op = 'u'. A DELETE creates a row with _streamkap_op = 'd' (and null values for the changed columns, depending on the connector configuration).

The table grows over time. To query current row state, you need a view or query that selects the most recent event per primary key and excludes deleted rows:

SELECT * EXCEPT (_streamkap_op, _streamkap_source_ts_ms, row_num)
FROM (
  SELECT *, ROW_NUMBER() OVER (
    PARTITION BY id
    ORDER BY _streamkap_source_ts_ms DESC
  ) AS row_num
  FROM `project.dataset.orders`
) WHERE row_num = 1 AND _streamkap_op != 'd'

This works for ad-hoc queries but adds scan cost for large tables. The alternative is a scheduled cleanup query that maintains a deduplicated table.

Scheduled Cleanup for Current-State Tables

BigQuery restricts DML (UPDATE, DELETE) on rows still in the streaming write buffer. Rows can remain in the buffer for up to 90 minutes after ingestion. Any cleanup query that tries to delete those rows will fail with a buffer-access error.

The safe pattern is to run cleanup on rows older than the buffer window:

DELETE FROM `project.dataset.orders`
WHERE id IN (
  SELECT id FROM (
    SELECT id, MAX(_streamkap_source_ts_ms) AS latest_ts
    FROM `project.dataset.orders`
    WHERE _streamkap_source_ts_ms < UNIX_MILLIS(TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 90 MINUTE))
    GROUP BY id
  ) latest
  JOIN `project.dataset.orders` existing
    ON existing.id = latest.id
    AND existing._streamkap_source_ts_ms < latest.latest_ts
);

Schedule this as a BigQuery scheduled query or a Cloud Scheduler job. Daily is often sufficient for reference tables with low churn. For high-update tables where storage cost from accumulated events is a concern, hourly or more frequent cleanup makes sense.

The Streamkap BigQuery best practices guide covers the cleanup query pattern and scheduling recommendations for different table volumes.

Cost Model: Storage Write API vs Legacy Streaming

The cost difference between ingestion paths is real, but ingestion cost is rarely the dominant line item for a mature CDC pipeline. Query cost often exceeds ingestion cost as data accumulates.

For ingestion planning:

Volume (events/day)	Legacy API est.	Storage Write API est.
10 million (small CDC table)	~$0.05–$0.50	Free (< 10GB/mo tier)
100 million (medium table)	~$0.50–$5.00	~$0.25–$2.50
1 billion (high-churn table)	~$5.00–$50.00	~$2.50–$25.00

These are rough estimates; actual byte volume depends on row width and CDC metadata. The Storage Write API is consistently cheaper at every scale.

For query cost, the standard BigQuery on-demand pricing is $5 per TB scanned. A large event-log table with years of accumulated CDC events and frequent full-table scans will cost more in query fees than ingestion. The cleanup pattern above reduces table size, which reduces scan cost. Partitioning the table on _streamkap_source_ts_ms (or on the event’s date) lets BigQuery skip historical partitions when queries only need recent data.

Partitioning and Clustering for CDC Cost Control

Partitioning a BigQuery table on a date or timestamp column lets queries skip entire partitions they don’t need to read. For a CDC event-log table, partitioning on DATE(_STREAMKAP_SOURCE_TS_MS_AS_TIMESTAMP) (or on a derived date column) means a query asking “what are the current orders placed this week?” only scans the relevant partitions rather than the entire table history.

BigQuery supports two partitioning types that suit CDC workloads:

Time-unit partitioning divides the table by day, month or year based on a timestamp column. For most CDC pipelines, daily partitioning on the source event timestamp is the right default. Queries that filter by date range benefit immediately without any schema changes to the consumer.

Ingestion-time partitioning uses the time BigQuery received the row rather than any column in the data. This is simpler to configure but less useful for CDC queries, since the ingestion time differs from the source event time. Use column-based partitioning when you have a reliable source timestamp.

Clustering goes further by sorting rows within each partition on up to four columns. For CDC tables, clustering on the primary key column(s) speeds up point lookups (fetching all events for a specific order ID) and makes the cleanup query significantly cheaper:

CREATE TABLE `project.dataset.orders`
PARTITION BY DATE(TIMESTAMP_MILLIS(_streamkap_source_ts_ms))
CLUSTER BY id
OPTIONS (
  partition_expiration_days = 90
);

The partition_expiration_days setting tells BigQuery to automatically drop partitions older than 90 days. For CDC workloads where historical events beyond a retention window aren’t needed, this keeps storage costs bounded without requiring explicit cleanup queries for old data.

One limitation: partitioning on _streamkap_source_ts_ms requires that column to be non-null. CDC events always carry a source timestamp, but the initial snapshot rows (captured before streaming starts) may have a different timestamp distribution. Verify the snapshot rows have valid timestamps before relying on partition pruning.

Schema Evolution in BigQuery CDC

BigQuery handles schema changes differently depending on what changed.

Adding a nullable column is backward-compatible. BigQuery accepts new fields with NULLABLE mode; existing rows don’t need to be rewritten. The CDC connector adds the new column to its schema, and subsequent events include the new field while historical rows show it as null.

Changing a column’s type is a breaking change in BigQuery. BigQuery doesn’t support in-place type changes (e.g. INT64 to FLOAT64, or STRING to BYTES) on columns with existing data. The workaround is to add a new column, migrate data, and drop the old column; or recreate the table with the new schema and re-snapshot from the source. Coordinate type changes at the source database level and give the CDC connector time to adapt before the old schema writes data that BigQuery will reject.

Removing a column is safe from BigQuery’s schema perspective: BigQuery won’t fail on receiving rows that don’t include a column that still exists in the schema. But the column continues to appear in queries with null values for new rows. To clean it up, recreate the table without the column and re-snapshot.

The practical rule: plan schema changes at the source database level, communicate them to the CDC pipeline operator, and handle BigQuery schema updates as an explicit step, not something to discover when the connector starts throwing schema validation errors.

Connecting Streamkap to BigQuery

Streamkap’s BigQuery connector uses the Storage Write API by default, writes the _streamkap_op and _streamkap_source_ts_ms metadata columns on every event, and includes a generated cleanup query for each destination table. Partitioning and clustering recommendations are part of the connector setup flow.

The Streamkap BigQuery documentation covers service account setup, required IAM permissions, dataset and table configuration, and the cleanup query schedule.

Where to next?

• BigQuery connector overview
• Streamkap BigQuery documentation
• BigQuery best practices guide
• Start a free 30-day trial