BigQuery Storage Write API vs Streaming Insert for CDC Pipelines

Choosing between BigQuery's ingestion APIs looks like a pricing exercise, but for CDC pipelines it is a correctness decision first and a cost decision second. Every row moving through a change data capture pipeline is an insert, an update, or a delete, and writing the wrong row twice, or missing a delete, corrupts the table on the other end. That single fact changes what "the right API" means. An event-logging pipeline can tolerate a duplicate row without much consequence. A CDC pipeline that applies the same update twice, or drops a delete during a retry, produces a table that looks fine and is wrong.

Some of this decision is already made. The legacy tabledata.insertAll API is deprecated as of 2026; the current guidance is to use the Storage Write API with gRPC. What remains open, and what actually determines whether a CDC pipeline holds up in production, is which Storage Write API mode a team picks: default stream, committed stream, pending stream, or buffered stream. Each makes a different promise about delivery guarantee, visibility timing, and UPSERT support, and those differences matter most under exactly the conditions CDC pipelines are built to survive. The sections that follow work through those differences in enough detail to make the right call before a pipeline ships, not after the first retry storm exposes a design choice nobody tested.

What the legacy Streaming Insert API actually did and why it falls short for CDC

The legacy tabledata.insertAll method sent rows over HTTP as JSON into a streaming buffer attached to the table. Rows sat in that buffer and were unavailable for certain DML operations, which is a serious problem for CDC pipelines that need to apply UPSERTs or merges right after ingestion rather than minutes later.

Its delivery guarantee was at-least-once, with deduplication offered on a best-effort basis. Duplicates could and did get through, and that best-effort layer was not reliable enough to stop a CDC pipeline from double-counting an update or double-applying a delete. For a system where every row is supposed to represent the current state of a record, that is a significant risk. It is a silent source of wrong answers.

Cost compounded the problem. Pricing was per row, so a table charge applied at a minimum even when the payload carried only a tiny change, such as a single updated column. High-churn CDC tables, where most changes touch one or two fields, are exactly the workload this pricing model penalized hardest.

How the Storage Write API Is Architecturally Different From Its Predecessor

The Storage Write API does not route data through a buffer at all. It writes directly to BigQuery's storage layer over gRPC instead of HTTP, converting incoming data to columnar format immediately, so there is no intermediary step standing between a write and the ability to run DML against it. That alone removes the structural delay that made the legacy API a poor fit for CDC. The API is also unified across workloads: the same interface supports real-time streaming and atomic batch commits, so a team does not need separate pipelines for streaming inserts and bulk backfills, since both use the same code path with different stream types.

The transport layer changed too. Protobuf serialization replaces JSON, and the resulting payloads are meaningfully smaller, a difference that adds up fast for high-churn CDC workloads pushing large volumes of small row changes.

Pricing follows the architecture. Instead of billing per row, pricing bills per unit of data volume with a generous monthly free tier, a structural advantage for CDC traffic where row counts run high but each payload is small. One constraint survives the redesign, though: the 10 MB per-request limit still applies, and sources capable of producing large documents, MongoDB among them, can hit that ceiling and require splitting or truncation logic built into the pipeline.

The architectural shift here is real and it solves the buffer problem, but it does not, by itself, solve the delivery-guarantee problem. That decision lives one level down, in which of the API's stream modes a team picks, and that choice is where a CDC pipeline's reliability story actually gets written.

The three stream modes and the guarantee each one actually provides

The three stream modes are not interchangeable settings for the same job. Each makes a distinct promise about when written data becomes visible for query and whether duplicate writes can occur, and picking the wrong one for a CDC workload is a correctness bug, not a performance trade-off to optimize later.

| Mode | Delivery guarantee | Data visibility | Primary CDC use case | |---|---|---|---| | Default stream | At-least-once; duplicates possible on retry | Immediate on acknowledgment | Workloads where a downstream layer, such as a MERGE job keyed on primary key, absorbs duplicates | | Committed stream | Exactly-once, enforced by stream-level offsets | Immediate on acknowledgment | Direct writes to a target table with no downstream merge layer | | Pending stream | Atomic batch, invisible until explicit commit | Only after the stream is finalized | Initial backfills and snapshot loads, not ongoing streaming |

The default stream is the simplest to implement and the closest equivalent, in spirit, to the old tabledata.insertAll. It is a reasonable choice only where something downstream is already built to absorb duplicates, which is an unusual position for a CDC pipeline writing straight into a production table.

The committed stream is where exactly-once delivery actually lives. Each AppendRows call carries the offset the API expects next, and any attempt to write to an offset that has already been committed gets rejected rather than silently applied. If a request fails and the client retries using the same offset, the API deduplicates the write mechanically, not as a matter of probability or best effort. An ALREADY_EXISTS response means a duplicate attempt, and an OUT_OF_RANGE response means a gap, giving the pipeline deterministic signals to act on. Visibility is immediate here too, so choosing exactly-once costs nothing in latency compared to the default stream. For any CDC pipeline writing directly into a target table without a merge layer sitting in between, this is the mode that matches the job.

The pending stream behaves differently again. Data stays invisible until the whole stream is finalized and committed as a single atomic batch, which suits an initial backfill or a snapshot load far better than it suits the continuous trickle of an ongoing CDC stream.

Why exactly-once delivery is not optional for CDC pipelines during a retry storm

A retry storm is the condition where upstream failures cause a burst of change events to get redelivered in a short span, and it is the moment where the gap between at-least-once and exactly-once delivery stops being theoretical. Applying a duplicate update twice against a target table does not throw an error. It produces a row that looks correct and is wrong, with nothing in the system flagging the mistake.

The offset mechanism inside committed streams is built for exactly this scenario. A retry carrying an offset that has already been committed gets rejected with ALREADY_EXISTS instead of being applied a second time, so the pipeline receives a deterministic signal instead of a silent corruption. The Storage Write API enforces this at the protocol level: appending before the current stream offset returns ALREADY_EXISTS, and appending beyond it returns OUT_OF_RANGE.

This guarantee only holds if every layer of the pipeline honors it. Teams running Dataflow as the transformation layer, in the common architecture of Pub/Sub for ingestion, Dataflow for transformation, and BigQuery for storage, face a second reliability decision that compounds the first: Dataflow offers both At-Least-Once and Exactly-Once streaming modes, and the At-Least-Once mode, while faster and cheaper, can still produce duplicates that land in BigQuery even when the write layer underneath is using committed streams. A committed stream guards its own offset. It cannot undo a duplicate that Dataflow already generated and handed off as two distinct, valid-looking writes.

The only posture that closes this gap is aligning both layers: exactly-once mode in Dataflow paired with committed streams in the Storage Write API, so that no single point of failure in the pipeline can introduce a duplicate that slips past the offset guard. Anything less leaves a seam where a retry storm can quietly rewrite the truth of a table.

How BigQuery's Native CDC UPSERT Support Changes the Pipeline

Delivery guarantees answer whether a write lands once. They do not answer what that write is supposed to do once it lands, and that is a separate design question BigQuery's native CDC support addresses directly. The Storage Write API can carry per-row change intent through the _CHANGE_TYPE pseudocolumn, marking a row as an UPSERT or a DELETE, and BigQuery applies that change directly against the destination table. No external MERGE job has to run afterward, though the destination table does need a defined primary key, and composite keys spanning multiple columns are supported.

The alternative pattern, streaming every change event as a plain insert and reconciling the table later with periodic MERGE statements, carries a cost that tends to surprise teams only after the table has grown. BigQuery bills MERGE operations on the volume of data scanned, and even with partitioning and clustering in place, running MERGE frequently against a high-churn table adds up fast. That cost is not a rounding error; it is a structural tax on a pattern that re-derives current state from a full history of changes every time it runs.

Insert-only ingestion is not without its own value. It preserves every version of every row, which supports audit trails and time-travel style analysis that a pure UPSERT table cannot offer, but it requires scheduled compaction to keep table growth and query performance in check.

The decision this leaves teams with is practical rather than ideological. Workloads that need current-state accuracy, such as live dashboards, AI feature stores, or operational reporting, should use the Storage Write API's native UPSERT path running on committed streams. Workloads that need full row history should run insert-only ingestion with scheduled compaction and treat the recurring MERGE cost as a deliberate, accepted trade-off rather than an oversight.

Schema evolution: where CDC pipelines quietly break without a plan

A pipeline that streams correctly on day one can still break weeks later, and the most common cause is a schema change nobody planned for. An ALTER TABLE run on the source database does not propagate to BigQuery on its own. A column added upstream simply will not appear at the destination unless the pipeline is explicitly built to detect the schema change and evolve the destination table to match.

Postgres sources using logical replication carry a specific version of this risk: a column dropped or retyped on the source can break the replication stream without warning, or appear as a type mismatch that is difficult to distinguish from an ordinary data error. Change data capture tools built on the Postgres write-ahead log can detect DDL changes through the same mechanism they use to capture data changes, registering a new schema version when a DBA runs an ALTER TABLE so that subsequent events carry the new field. That detection only helps if the pipeline downstream, and the BigQuery write path specifically, is prepared to accept the new shape of the data rather than reject it.

The Storage Write API adds its own layer of rigor here. It requires a protobuf schema kept in sync with the BigQuery table schema, and a mismatch between the protobuf descriptor and the destination table causes the write to fail outright rather than silently dropping or corrupting data. That is the right failure mode to have, loud rather than quiet, but it only pays off if the pipeline has logic in place to detect the mismatch and apply the schema change, instead of simply crashing and waiting for someone to notice.

Managed CDC platforms, including Streamkap among others, handle schema drift detection and destination table evolution automatically. Teams building a pipeline from raw log-based capture plus custom Storage Write API code take on that responsibility themselves. Either way, schema evolution needs a plan in place before the first production ALTER TABLE.

How the Source Database's CDC Mechanism Affects What Arrives at BigQuery

Everything the Storage Write API guarantees sits downstream of a more basic fact: what reaches BigQuery is bounded by what the source database's own change capture mechanism actually guarantees. No write-layer feature can compensate for a gap or an ambiguity that was already introduced upstream.

PostgreSQL sources work through logical replication, which decodes the write-ahead log into a stream of row-level insert, update, and delete events. Replication slots track precisely how far a connector has read, which functions as a durable offset, and that offset maps naturally onto the stream offset model the Storage Write API already uses for its committed streams. The risk sits on the operations side: an unmanaged replication slot will accumulate write-ahead log data indefinitely if the consumer reading it falls behind or disconnects, and left unchecked that can fill disk and bring down the primary database.

MongoDB sources work differently. Its change streams expose insert, update, replace, and delete events, but MongoDB's flexible document schema means a downstream consumer has to handle real structural variation inside the change event payload itself, not just a fixed set of columns. Schema changes in MongoDB also do not raise a DDL event the way an ALTER TABLE does in Postgres, so a pipeline has to infer structural drift from the data as it arrives, which makes automated schema evolution a harder problem to solve cleanly.

The lesson carries across every source: the Storage Write API and its committed-stream guarantee are only as reliable as the change capture mechanism feeding them, and no amount of care at the BigQuery end repairs a gap or an undetected schema change that happened upstream, before the data ever reached the write API at all.