Why Did the Totals Grow? Audit Power Query Merge Cardinality

A BigQuery writer times out after sending three rows. The process never records an acknowledgement. On restart, should it create a fresh stream, reuse offset 0, advance to 3, or re-read the source and rebuild the batch? Only one of those choices preserves the identity of the write you were already attempting.

This guide is for data engineers, streaming-platform owners, ingestion-service developers, and reliability reviewers using the gRPC BigQuery Storage Write API. The main path is an application-created committed stream with an explicit row offset. The lab target is deliberately narrow: a disposable, append-only BigQuery table that is compatible with Storage Write API ingestion and has no identity columns. It is not tabledata.insertAll, not the Storage Write REST interface, and not a claim that Google coordinates your upstream checkpoint, local journal, and downstream effects in one transaction.

Google documents exactly-once write semantics within an application-created stream when offsets are supplied; the default stream is at-least-once. It also documents asynchronous AppendRows, ordered responses on a connection, and offset errors that can result from client mistakes. Those are service boundaries, not a complete recovery protocol.

The operating model below is therefore explicit about what is documented and what is proposed. The proposed invariant is: destination + stream + row offset + exact batch identity + source range must survive a crash together. Everything else, including fencing, fingerprints, checkpoint ordering, and acceptance gates, is application-owned.

Choose the Exact Boundary of the Ingestion Guarantee

“Exactly once” is only useful after naming the object that is exactly once. For the Storage Write API, the documented control is a row offset on an application-created stream. If the supplied offset matches the next append position, the write proceeds; if the offset has already been written, the service reports ALREADY_EXISTS; if the offset is beyond the current end, it reports OUT_OF_RANGE. The default stream does not accept user-supplied offsets.

That contract does not say that two source records with the same business key are one event. It does not say that offset 0 on stream A is the same operation as offset 0 on stream B. And it does not make a source acknowledgement atomic with the BigQuery append.

For this guide, the delivered unit is a durable mapping from a reproducible source range to one exact serialized batch and one BigQuery write position. “Exactly once” will mean the same intended batch is not appended twice at the same explicit position in the same application-created stream. Google describes committed streams as providing exactly-once delivery within a stream through record offsets.

A replay onto a new stream is a new service identity. A second upstream record that happens to represent the same purchase, sensor reading, or user action is also a distinct write unless your application applies a business-deduplication rule. Keep those boundaries visible in design reviews.

Select the Stream Type Before Designing Recovery

The recovery protocol depends on stream type. Google’s overview distinguishes the default stream from application-created streams, and application-created streams from one another by visibility and commit behavior. The default stream is immediately queryable and at-least-once. A committed application-created stream makes successful appends visible immediately and supports explicit offsets. A pending stream buffers rows until finalization and an atomic batch commit.

This article uses application-created committed streams because the operational question is whether a continuously running producer can preserve write identity across uncertain acknowledgements. That choice is not a statement that default-stream ingestion is wrong. Google’s best-practices guide explicitly says the default stream may be preferable when upstream delivery is already at-least-once or duplicates can be detected after ingestion.

The wider architecture still matters, including backpressure, source retention, table design, and monitoring, but those concerns are broader than this protocol. Refonte Learning’s article on cloud-native pipeline design is useful context for that surrounding system; it does not replace the Storage Write API’s stream and offset contract.

A committed stream does not require a batch-commit step. Finalization is optional for committed streams and closes the stream to further appends. Pending streams follow a different CreateWriteStream → AppendRows → FinalizeWriteStream → BatchCommitWriteStreams workflow. Treat those as different recovery protocols, not interchangeable final steps.

Persist the Source-to-Write Identity Before Sending

The journal is the writer’s memory after the process is gone. It must be durable enough that a restart can answer, without guessing: which source records were intended, what exact rows were serialized, where were they intended to land, and what evidence exists about the outcome? This journal is a proposed application control; Google does not create or coordinate it for you.

A compact schema can look like this:

Write the PREPARED record before calling AppendRows. The stable fields form the BigQuery batch identity for recovery. Observations may change; the intended batch may not. This is also where data contracts and schema ownership provide complementary context: a schema contract can define what fields are valid and who owns change, but it does not implement a durable append journal.

Do not put raw sensitive payloads in ordinary application logs. Store either an immutable protected object reference or enough source coordinates to reproduce the exact ordered rows under an explicit source-retention contract. A fingerprint should be computed over a defined canonical representation, such as versioned protobuf bytes in source order, rather than over whatever JSON rendering a debugger happens to emit.

Proposed pre-send checklist

• Journal row is durably committed before network send.

• Stream name and offset are already allocated.

• Exact ordered row set is immutable or reproducible.

• Fingerprint algorithm and serialization version are recorded.

• Owner epoch is current.

• No source checkpoint has advanced beyond this unconfirmed batch.

Count Rows, Not Requests or Bytes

Offsets count rows. Suppose synthetic batch A contains three rows and starts at offset 0. Synthetic batch B contains two rows, so its intended offset is 3. If both are accepted in sequence, the next append position is 5. Two API requests do not imply offset 2; payload bytes do not imply any offset at all. Google’s committed-stream sample advances the next offset by the number of rows in each batch.

Bind the Offset to the Exact Intended Batch

An offset is not a batch hash. Google’s error semantics report whether a stream position is already occupied; the documentation does not state that ALREADY_EXISTS compares your retry payload with the previously accepted payload byte-for-byte. The stronger rule, “treat ALREADY_EXISTS as success only if the journal proves this is the same intended batch,” is therefore a proposed producer invariant, not a BigQuery equality guarantee. The best-practices guide itself warns that offset errors can result from incorrect client offsets.

After a timeout, never re-read a mutable source, produce a different ordered row set, and attach the old offset. Either reproduce the exact batch or stop and reconcile.

Establish One Recoverable Owner for Each Stream

Google documents a transport boundary: an application-created stream can have only one active connection, while the default stream can have multiple connections. That is useful, but it is not the same thing as an application lease that decides which process may mutate your journal, acknowledge the source, or recover an old batch.

Use an explicit ownership protocol around each created stream. One workable proposed design is a durable lease row with a monotonically increasing owner_epoch. A process acquires the lease with compare-and-swap semantics, persists the new epoch, and includes that epoch in every journal state transition. Any update from an older epoch is rejected. The lease store must itself provide the atomic conditional update you rely on; a timestamp in memory is not fencing.

Local policy example: one active application owner per stream, lease renewed before a fixed locally chosen expiry, and every mutation fenced by owner_epoch. The numeric lease duration is environment-specific; this article does not prescribe one.

On takeover, the new process must read durable state before creating network state. If the journal says stream S has batch A at offset 0 with an uncertain outcome, recovery authority is over that identity. Creating stream S2 merely because the old process died changes the idempotence namespace. Replaying the same source rows at offset 0 on S2 can create another valid append.

The same principle applies when a gRPC connection breaks. Google says connections can be closed and clients may reconnect; the Java and Go libraries can reconnect automatically. A new connection to the same created stream is not the same as a new stream.

Your failover test should therefore include a stale process waking after ownership moved. Expected result: it cannot update the journal or advance the source checkpoint, even if it still has local objects capable of attempting I/O.

Model Append Outcomes Instead of Catching Every Error the Same Way

A reliable writer does not collapse every exception into “retry.” It distinguishes what is known about acceptance. AppendRows is asynchronous, and responses on a bidirectional connection are ordered with the requests sent on that connection. Google recommends nonblocking appends for throughput. For a recovery-first reference implementation, however, start with one in-flight batch per created stream so that an unresolved response cannot be confused with several later allocations. That serialization is a proposed auditability tradeoff, not Google’s highest-throughput recommendation.

The RPC reference says that when an AppendRowsResponse.error is present, the rows for that request were not accepted, and it separately documents row errors for corrupted rows. A transport failure with no conclusive response is different: absence of an acknowledgement does not prove absence of server acceptance. That distinction is an inference from the response contract and is the reason SENT_UNKNOWN exists.

Also distinguish “the API call returned from a local method” from “the backend append is acknowledged.” Google’s Java sample gets an ApiFuture<AppendRowsResponse> and handles completion in a callback; its lower-level examples separately write requests and consume responses. Verify the exact client-library contract you deploy before choosing the moment that moves your journal to CONFIRMED.

Recover an Uncertain Acknowledgement

Assume batch A is durably prepared for stream S, offset 0, three rows, fingerprint H(A). The request is sent, then the process loses the response and crashes. Recovery does not allocate offset 3, regenerate A, or open S2. It reuses S, offset 0, and the exact bytes or reproducible batch represented by H(A).

If the original append never reached acceptance, the retry can succeed at offset 0. If the original append was accepted, retrying the same explicit offset is idempotent and the service can report ALREADY_EXISTS. Google documents offset-specified writes as safe to retry after network errors or server unresponsiveness.

Do not advance the source checkpoint merely because a send operation completed locally. The checkpoint follows conclusive target evidence under the ordering defined later.

Interpret Offset Errors with the Journal in Hand

Google documents ALREADY_EXISTS when the supplied offset has already been written and OUT_OF_RANGE when the supplied offset is beyond the current end of the stream. Its best-practices page also warns that both can arise from wrong client offsets.

Therefore:

Never “hunt” for an accepted offset by incrementing or decrementing until BigQuery stops objecting. That can turn an accounting defect into silent data loss or duplication.

Work Through a Synthetic Crash-Recovery Ledger

The following lab is synthetic and not executed. Use a disposable, compatible append-only table with no identity columns, and inject failures only in an approved test environment. The expected results follow the documented offset semantics; they are not presented as observed runtime output.

Assume one source partition P7. Batch A covers source positions 1000–1002 and contains three rows. Batch B covers 1003–1004 and contains two. A created committed stream S is assigned to the partition.

The critical point at T4 is not the error code alone. The journal proves that the retry is for the same source range, same destination, same created stream, same row offset, same row count, same serialization version, and same fingerprint. Only then does ALREADY_EXISTS become evidence consistent with “our intended batch is already at this position.” Google’s own best-practices statement that ALREADY_EXISTS may be ignored sits beside its warning that wrong offsets also produce offset errors; the journal supplies the missing producer-side proof.

Now test the deliberately unsafe alternative. At T4, suppose the restart creates stream S2, rebuilds A from source, and writes it at offset 0. The Storage Write API sees a valid first write on a different stream. Exactly-once semantics are within a stream, so S2:0 does not inherit the occupied state of S:0. Both committed streams are visible after successful appends. This can yield duplicate destination rows even though each stream individually obeyed its offset contract.

Synthetic acceptance checklist

• Lost response after server acceptance leaves A recoverable without a new stream.

• Restart reads the journal before allocating any offset.

• ALREADY_EXISTS is accepted only after exact-batch proof.

• B remains at offset 3; it is not moved because A’s response was lost.

• Source progress moves to 1005 only after both intended batches are confirmed.

• Creating S2 for unresolved A is classified as a design change requiring reconciliation, not an ordinary retry.

Keep Source Checkpoints Separate from Target Acceptance

The BigQuery append and the upstream source acknowledgement are separate durability domains. Nothing in the Storage Write API documentation coordinates an external queue acknowledgement, Kafka-style source offset, application journal commit, and BigQuery append as one distributed transaction. The safe design is therefore a recovery ordering, not a claim of atomicity across systems. Google’s documentation defines when rows are appended and visible; your source defines when replay remains possible.

A proposed ordering for each source range is:

This is a classic “there may be a duplicate attempt across a crash, so make the target attempt idempotent” pattern. The journal bridges the uncertainty, but it does not make step D and step E atomic. If the process crashes after D but before E, the source may replay the range; the writer recognizes the existing journal identity and does not allocate a new stream position. If it crashes after E but before F, recovery must be able to inspect the source checkpoint and repair local status without replaying data.

The destination schema for this lab can include source coordinates and batch_id as ordinary columns for reconciliation. That is different from generated-key strategy. Refonte Learning’s discussion of BigQuery identity-column ingestion boundaries covers that separate concern; this lab intentionally stays on a compatible non-identity destination.

Test Crashes Around the Source Checkpoint

Run failure injection at the boundaries, not only during the network call.

Local policy example: require one approved test for every checkpoint boundary and every supported owner-transfer path before production scale-up. The count is a local acceptance rule, not a Google product requirement.

Define a Separate Business-Duplicate Policy

Two different source records can describe the same business event. Example: positions 1002 and 1019 may both carry order_id=O42 because an upstream producer duplicated the event. Writing each record once at distinct BigQuery offsets is fully consistent with the stream guarantee.

Use event IDs, business keys, reconciliation queries, or curated-table logic according to the data contract. Do not advertise BigQuery stream offsets, or generated destination IDs, as universal business-event deduplication.

Handle Schema Rejection Without Reusing a Changed Batch Identity

Schema and validation failures are dangerous because the tempting repair is to “drop the bad row and resend the rest” under the old journal identity. That changes the batch.

The RPC reference is precise for corrupted-row failures: when a request fails for corrupted rows, no rows in that batch are appended, and row-level error information is returned so the caller can remove bad rows and retry. It also says an error response indicates the rows were not accepted.

That supports repair only after a conclusive rejection. It does not justify mutating a batch whose transport outcome is unknown.

Record both schema_version and serialization_version. Google’s best-practices guide says extra fields not present in the current table schema can produce SCHEMA_MISMATCH_EXTRA_FIELD; after a table schema update, the service detects the change after a short delay, and existing connections generally need to reconnect with the new schema, with Java JsonStreamWriter providing additional automatic behavior.

Suppose rejected batch B was two rows at offset 3, and one row is removed. The repaired batch is not B anymore: give it a new batch_id, new fingerprint, and row count 1. If a later batch C had already been prepared for offset 5, its allocation is now suspect because the new next position would be 4. In the serialized reference design there should be no later in-flight batch, which is exactly why schema repair is easier to audit.

Schema-recovery checklist: prove rejection; freeze old journal record; create replacement identity; recompute row count; invalidate affected future allocations; reconnect if required by the deployed client/schema path; only then resume.

Add Concurrency Only After the Serialized Protocol Passes

Google designed AppendRows to be asynchronous. Responses on a bidirectional connection arrive in request order, and the best-practices guide recommends sending without blocking for maximum throughput. A production writer may therefore need several in-flight appends. The mistake is adding concurrency before the recovery model can name every outstanding batch.

Start from the serialized protocol, then add a bounded pipeline in which offsets are durably allocated in order and each in-flight request has a journal record. A later response must never erase an earlier unresolved state merely because callbacks are processed quickly.

Local policy example: begin with one in-flight batch per created stream; increase the bound only after crash drills demonstrate deterministic recovery of the full outstanding window. The chosen bound is a deployment policy, not a universal BigQuery recommendation.

Request sizing needs the same restraint. The current BigQuery quota table lists a 20 MB maximum AppendRows request size, while the RPC reference phrases the requirement as a single request being less than 20 MB; larger requests typically return INVALID_ARGUMENT. Treat 20 MB as a hard ceiling, leave encoding headroom, and verify any lower limit imposed by the client you actually deploy.

That 20 MB figure is a service limit, not a recommended target batch size or throughput promise. Google separately recommends larger requests for efficiency, but actual throughput varies with schema, network, server load, quota, and client implementation. Benchmark representative rows and fault behavior in your environment rather than turning documentation examples into an SLO.

For broader platform context around BigQuery and streaming services, GCP data engineering foundations can orient readers to the ecosystem; current limits and recovery behavior should still come from the primary Google documentation above.

Evaluate Pending Streams as a Different Commit Protocol

Pending streams are useful when the unit you need to expose is an atomic batch across one or more streams, but they add a second recovery problem: commit state. Google documents the sequence as create pending streams, append, finalize each stream, then call BatchCommitWriteStreams; data remains invisible until commit, and the commit is atomic. A failed commit can be retried, while per-stream problems are returned in stream_errors.

This is not indefinite staging. The current guide says that after finalization and before commit, data can remain buffered for up to four hours, and pending streams must be committed within 24 hours. The quotas page also lists pending-stream byte limits.

The current gRPC quotas page allows up to 10,000 streams per table in each batch-commit call. That is a documented limit, not a target fan-out.

The important recovery distinction is action identity. Retrying a commit for the same finalized pending streams is not the same operation as creating replacement streams and replaying their source ranges. The former preserves the write set; the latter creates new append identities and must be reconciled as such.

Collect Evidence That Can Reconcile the Source and Destination

API success counters are necessary but insufficient. The review question is coverage: can you account for every intended source range and explain every unresolved write identity?

Use metrics whose denominators are explicit.

For destination reconciliation, include ordinary source coordinates or a stable event/batch identifier in the target when policy permits. Then compare the expected source range with query results, rather than merely counting successful requests. Google also exposes Storage Write API monitoring through INFORMATION_SCHEMA.WRITE_API_TIMELINE and Cloud Monitoring, but its overview warns that some console AppendRows dashboards reflect connection-level rather than request-level behavior.

Broader data lake and warehouse governance concerns, including retention, access, lifecycle, and stewardship, matter around this evidence plane. Keep the write journal specifically engineered for recovery: protect payload references, minimize sensitive data, define retention after checkpoint completion, and record enough history to explain owner changes and batch replacement.

Evidence gate: a dashboard saying “99.9% requests succeeded” is not proof that every intended event arrived once. Require source-range coverage, unresolved-write age, and an auditable explanation for every changed stream or batch identity.

Run a Controlled Adoption and Failure-Drill Plan

Adopt the protocol by proving recovery before optimizing throughput. The phases below are a proposed local rollout model, not a Google-prescribed deployment sequence.

Local policy example: scale only when the cohort has zero unexplained uncertain appends at the review point, every injected checkpoint-boundary crash has a documented recovery result, and ownership fencing has rejected a stale writer at least once in the test fixture. These are proposed acceptance gates; choose counts and observation windows appropriate to your system.

Inject at least these classes in non-production first: connection loss before response, process death after backend acceptance, crash after local confirmation but before source checkpoint, stale-owner wake-up, schema rejection, and an intentionally wrong offset. Expected results should be written before the test so the drill distinguishes validation from storytelling.

Do not make “fall back to the default stream” an automatic error path. That silently changes the delivery contract from offset-controlled application-created writes to at-least-once default-stream writes. If an incident requires a mode change, stop, record the boundary, reconcile the affected source range, and obtain the operational approval appropriate to your platform.

Likewise, if an old stream cannot be recovered, do not hide the discontinuity. Create a new stream only as an explicit new epoch, with a documented cutover source position and a reconciliation decision for everything before it.

Build the Data Engineering Foundations for Reliable Ingestion

Storage Write API recovery sits on fundamentals that are broader than one client library: durable pipeline state, streaming and batch ingestion, transformation boundaries, and governance. As of September 17, 2026, Refonte Learning’s live Data Engineering Program lists a three-month period, 12–14 hours per week, and areas including data pipelining, real-time processing, ingesting streaming and batch data, transforming data, and data governance and compliance controls.

Those foundations map naturally to an independent exercise like this one: model a source range, persist a recovery ledger, append to a disposable destination, inject an uncertain acknowledgement, and prove that restart preserves the same stream/offset/batch identity.

The useful learning target is not memorizing an error code. It is being able to state what survived a crash, who owns the next decision, and which evidence makes a retry safe.

Answer the Exactly-Once Questions Precisely

The phrase “BigQuery Storage Write API exactly once” should trigger boundary questions, not end them.

Does the default stream deduplicate events?
Do not describe it that way. Google documents the default stream as at-least-once and disallows explicit offsets on it. If your workload accepts that contract and deduplicates elsewhere, the default stream can be the simpler and more scalable choice.

Can every ALREADY_EXISTS be ignored?
No. Google says it is typically a retry condition and its best-practices guide says it can be ignored, but the same guide warns that offset errors can also result from wrong client offsets. Operationally, ignore it only when your durable journal proves the same destination, created stream, offset, source range, row count, serialization version, and exact intended batch.

Does a new stream preserve old offsets?
No. Offsets are positions within a stream. Replaying the same rows at offset 0 of a newly created stream is a new write identity and can create duplicate destination rows.

Is source acknowledgement atomic with the append?
Not in the protocol described here. The Storage Write API governs the BigQuery write. Your source checkpoint and application journal require their own durability and recovery ordering; this article makes no distributed-transaction claim.

Proceed / hold / reconcile checklist

The practical rule is intentionally strict: preserve write identity before you preserve throughput. A BigQuery stream offset can make a retry idempotent inside its documented boundary. Your job is to keep the source range, stream, offset, exact batch, acknowledgement evidence, and durable checkpoint aligned long enough for that boundary to remain meaningful.