A BigQuery streaming pipeline is producing duplicate rows. How do you diagnose and eliminate them?

## Establish the shape of the duplication Before changing anything, characterise it: ```sql SELECT event_id, COUNT(*) AS n, MIN(ingest_ts), MAX(ingest_ts) FROM raw.events WHERE ingest_ts > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY) GROUP BY event_id HAVING n > 1 ORDER BY n DESC; ``` Three signatures, three different causes: - **Byte-identical rows, timestamps seconds apart** — client retried an append it could not confirm. This is at-least-once ingestion behaving as documented. - **Identical rows, timestamps hours or days apart** — a batch or backfill was replayed, or two pipelines write the same source. - **Same key, different payload** — the producer genuinely emitted the event twice, or it is a legitimate update and your "duplicate" is actually a new version. That is a modelling question, not an ingestion bug. ## Why streaming duplicates in the first place Both streaming paths default to at-least-once: - The Storage Write API's **default stream** (`_default`) accepts no offsets. It is shared by many writers, which is what makes it fast and simple, and is precisely why it cannot tell a retry from a new write. - The legacy **`tabledata.insertAll`** method offers `insertId`-based deduplication that Google documents as **best effort** and only within a limited window. Designing for correctness on top of it is a mistake; it is also the path being superseded by the Storage Write API. Any network path can lose an acknowledgement. A client that does not retry loses data; a client that retries without an idempotency token duplicates. There is no third option unless the server can recognise the retry — which is what offsets do. ## The upstream fix: committed streams with offsets Create a stream per writer with `CreateWriteStream(type=COMMITTED)` and send each `AppendRows` request with the offset at which its rows begin. BigQuery tracks the stream's current offset; an append whose offset has already been written is rejected rather than applied, so the client can retry freely. Handling that rejection as success is the whole exactly-once protocol. What it costs you: one stream per writer with a lifecycle (create, append, finalize), offset state that must survive a client crash, and lower throughput per stream than the shared default stream. Exactly-once is a design commitment, not a flag. If your writes are batch-shaped, **pending** streams give a different form of the same protection: nothing is visible until `BatchCommitWriteStreams`, so a failed run publishes nothing and the rerun is clean. ## The downstream fix: deduplicate on read or on merge Even with exactly-once writes, producers replay and backfills overlap, so mature pipelines keep a deduplication layer: ```sql CREATE OR REPLACE VIEW curated.events AS SELECT * EXCEPT(rn) FROM ( SELECT *, ROW_NUMBER() OVER ( PARTITION BY event_id ORDER BY ingest_ts DESC) AS rn FROM raw.events ) WHERE rn = 1; ``` The view is always correct but re-scans and re-sorts on every read, which is expensive on a large table. The usual production shape is therefore a periodic `MERGE` from the raw append-only table into a curated table keyed by `event_id`, with the view kept only for the most recent, not-yet-merged window. Restrict both by partition — the raw table should be partitioned on ingest time so the dedup window scans one partition rather than history. ## Prerequisites you must insist on Deduplication needs a **stable business key** produced at the source — an event id, or a deterministic hash of the immutable fields. A key generated at ingestion time is useless: the retry gets a different one. This is frequently the real finding of the investigation, and pushing the key upstream is the durable fix. ## Choosing between the two fixes Exactly-once writes remove duplicates at the cost of pipeline complexity and per-stream throughput. Downstream deduplication tolerates duplicates from every source — retries, replays, double-running jobs — at the cost of query-time or merge-time compute. Most teams end up with both: the default stream for throughput, and a merge into a keyed curated table because they need protection from replays regardless of the write path.