Streaming buffer is your hidden constraint

The old blanket rule that BigQuery can’t mutate recently streamed rows isn’t accurate anymore. The ingestion method decides the constraint.

Rows written through the BigQuery Storage Write API over gRPC can be modified by recent-row UPDATE, DELETE, and MERGE operations, subject to documented limitations. That support doesn’t extend to every streaming path. Legacy tabledata.insertAll and the Storage Write API REST path have different recent-row behavior.

So when a MERGE rejects fresh data, identify how those rows arrived before redesigning anything. “BigQuery streaming” isn’t one behavior anymore.

The broader architectural advice remains: use streaming to record change, then publish analytical state through an explicit reconciliation model. Technical support for immediate DML doesn’t make row-by-row warehouse synchronization a good default.

Choose the ingestion contract deliberately

For new high-throughput streaming workloads, the gRPC Storage Write API is the serious default. It supports efficient long-lived streams, ordered asynchronous writes, committed streams for exactly-once semantics, and current BigQuery features such as streaming CDC.

The REST endpoint can suit ephemeral or constrained producers, but it doesn’t expose the same recent-row DML contract. Legacy streaming inserts remain relevant in existing systems and should be treated according to their own limitations.

Write the ingestion method into the pipeline contract. Operators need to know which guarantees apply to ordering, idempotency, visibility, offsets, schema evolution, and later mutation. Hiding every path behind a helper named write_rows removes precisely the information incidents need.

Raw arrival and curated state are different products

An append-oriented raw table should tell the truth about what arrived: source identity, event or source update time, ingestion time, payload, producer metadata, and any offset or idempotency key.

A curated latest-state table answers a different question. It resolves duplicates, ordering, late data, deletes, and business identity. That state may be produced through periodic MERGE, partition replacement, a Dataflow CDC path, or another bounded reconciliation process.

Separating the two gives the system replayability. If ordering logic changes or a producer retries a batch, raw history remains available and curated state can be rebuilt. Mutating the only copy immediately is simpler until the first time the mutation was wrong.

Reconciliation needs an explicit window or change set

“Reconcile recent data” isn’t specific enough.

Define the maximum observed lateness, retry horizon, and correction behavior. Then choose a lookback window or affected-key set that covers it. Track how often records arrive outside that boundary. If the tail keeps escaping, widen the contract or fix the producer rather than silently accepting stale state.

For event history, partition by the boundary that supports replay and maintenance. For current-state models, use a stable unique key and make deletion semantics explicit. A source row disappearing, arriving with a tombstone, or changing status are different events unless the contract says otherwise.

The reconciliation job should be idempotent. Rerunning the same source window must converge on the same curated result rather than create another interpretation of “latest.”

Don’t use DML support as an OLTP invitation

BigQuery can now handle mutation patterns that older advice ruled out. It’s still an analytical system.

If the application requires per-request transactions, immediate read-after-write state across several entities, row locks, or conflict handling inside a user workflow, keep that state in an operational database. Stream the resulting changes into BigQuery for analysis.

If the requirement is a continuously updated analytical table fed by ordered changes, BigQuery’s Storage Write API and CDC capabilities may fit well. The deciding variable is the consistency and interaction model, not whether a MERGE statement happens to run.

Model failure before chasing freshness

Streaming introduces ambiguous outcomes, retries, backpressure, quota pressure, and duplicate risk. Buffer important records before the BigQuery write, use offsets or committed streams where exactly-once semantics are required, and record enough producer state to resume safely.

Monitor write errors, append latency, connection behavior, throughput, and reconciliation lag. “Rows are queryable quickly” is only one part of the service. The platform must also prove what happened when an append timed out or exceeded capacity.

Publish the state consumers can trust

A dashboard that reads the raw stream may be fresh and semantically unsettled. Decide whether the consumer needs arrival visibility or reconciled business state.

Operational ingestion monitoring can read raw arrival. Financial and management reporting usually needs curated state after ordering, deduplication, and late data have been handled. That may add minutes of latency and remove hours of argument.

Treat the streaming buffer and ingestion method as explicit constraints, not mysterious product behavior. Check the API path first. Then choose append, CDC, reconciliation, and serving semantics from the state the business needs.