Streaming inserts: 0.4% double-counted and a batch that returns 200 while dropping rows

insertId is best-effort, not a guarantee. BigQuery retains insert IDs for a limited window (on the order of minutes) and uses them to suppress identical retries within that window, scoped to the table. It does not help when:

• The retry arrives after the window has passed - the dedupe record is gone and the row is inserted again.
• The first attempt actually committed but the response was lost (deadline exceeded / connection reset). The client retries a batch that already landed; rows outside the retention window duplicate.
• Different insert IDs are generated for the same logical event (a retry path that recomputes the ID).

So symptom 1 (0.4% double-count during incidents) is exactly the retry-on-timeout case: the write succeeded, the ack didn't, the client resent. Retention limits make this rare in steady state and common precisely when you are retrying hard - which is when it matters.

Symptom 2 is a different bug and more serious: a 200 does not mean all rows were written. insertAll performs partial success. The response body contains insertErrors, a list of per-row failures with an index and reason (schema mismatch, invalid value, quota). The HTTP status is 200 while individual rows were rejected. Returning success on any 200 discards those errors, so the rows vanish silently. That is a data-loss bug, not a delivery-semantics subtlety.

Note also that retrying on all 5xx indiscriminately will retry requests that partially succeeded, which is how one incident produces both symptoms at once.

Move to the BigQuery Storage Write API with application-created streams.

• The default stream gives at-least-once. It is a drop-in improvement over insertAll (better throughput, no per-row cost model, proper error reporting) but it does not solve duplicates.
• Application-created streams give exactly-once: the server deduplicates on (stream, offset) pairs, so a retry that reuses the same offset is discarded. This is a real guarantee, but it is conditional:
  • Each writer must use its own stream (CreateWriteStream) - do not have 100 pods share one stream.
  • The client library tracks offsets locally and must commit them after the append is acknowledged; on restart the writer must resume from the persisted offset for that stream, or the guarantee lapses for the gap.
  • A stream must be finalized (FinalizeWriteStream) when the writer is done, otherwise it lingers and its data may not become visible/queryable as expected.
  • Persist (stream_name, offset) durably per writer - a Kubernetes pod identity plus a small state store is enough. If you cannot do that reliably, this is the moment to consider Dataflow, which manages streams and offsets for you and provides exactly-once end to end.

Also required regardless of API: handle per-row/append errors explicitly and never treat a request as fully successful unless every append in it succeeded. Distinguish retryable (quota, unavailable, deadline) from non-retryable (schema, invalid value) - the latter must go to a quarantine path, not a retry loop. Use the gRPC connection pool with multiplexing to share connections across writers at 40k rows/s.

Bonus: the Storage Write API also removes the per-row streaming cost model of insertAll, which at 40k rows/s sustained is a material saving.

Do not try to clean up with UPDATE/DELETE. Mutating DML across a large partitioned table is expensive, is rate-limited, and rewrites partitions - at 40k rows/s you will spend more on the cleanup than on ingestion, and a mistake is unrecoverable.

Dedupe at read time, then materialize once:

CREATE OR REPLACE TABLE analytics.impressions_dedup
PARTITION BY event_date
CLUSTER BY campaign_id
AS
SELECT * EXCEPT (rn)
FROM (
  SELECT *, ROW_NUMBER() OVER (
    PARTITION BY impression_id
    ORDER BY ingested_at
  ) AS rn
  FROM analytics.impressions_raw
)
WHERE rn = 1;

• ORDER BY ingested_at (an ingestion timestamp column you should add if it is absent) keeps the earliest copy deterministically. Keep the raw table - it is your audit trail and the evidence that the count was wrong.
• Point the dashboard at the deduplicated table. The switch is a view/table reference change, tested in a staging dataset first so nobody discovers a schema surprise in the revenue number.
• If the raw table is too large to rewrite in one statement, dedupe per event_date partition and overwrite partition by partition.

Going forward:

• Add a scheduled data-quality query recording COUNT(*) - COUNT(DISTINCT impression_id) per day and alert on non-zero. This catches a regression in the ingestion path within hours rather than at month-end close.
• Reconcile against the publisher's own counts as an external oracle - an internal-only check would have missed symptom 2 entirely (dropped rows), since drops reduce counts and duplicates inflate them and the two partially cancel.
• Track the insertErrors rejection rate (now: the Storage Write API error rate) as a first-class SLI; it should be zero, and any sustained non-zero is a page.