Design an idempotent event pipeline

Event delivery can repeat. A producer times out and resends. A queue redelivers after a consumer crash mid-batch. A deploy replays an hour of traffic. None of these are rare — at-least-once delivery is the honest contract of almost every pipeline, so duplicates are a design input, not an edge case.

Idempotent means processing the same event twice leaves the same result as processing it once. Your dashboards’ credibility depends on it: a retry storm that double-counts revenue is a very visible bug.

Stable identity

Add a stable event ID or another deduplication boundary before retrying the same batch:

CREATE TABLE events (
  event_id   UUID,
  ts         DateTime,
  service    LowCardinality(String),
  event_type LowCardinality(String)
)
ENGINE = MergeTree
ORDER BY (service, ts);

The ID must be minted where the event happens — the producer — not where it’s stored. An ID assigned at insert time makes two copies of the same event look like two different events.

With identity in place you can layer defenses. Resending a batch with the same insert_deduplication_token setting lets ClickHouse drop the repeat delivery. And whatever slips through remains detectable, and cleanable, because duplicates share an event_id:

SELECT event_id, count() AS copies
FROM events
GROUP BY event_id
HAVING copies > 1;

Two timestamps, not one

Keep event time separate from ingestion time. The moment a user clicked and the moment the row reached ClickHouse can differ by seconds or, after an outage, by hours. Store both — ts from the source and an ingested_at DateTime DEFAULT now(). Charts group by event time; the gap between the two is your ingestion lag, and watching its maximum tells you when the pipeline is falling behind.

Reject loudly, not silently

Validate required fields at the producer or staging boundary, retain failed batches for inspection, and avoid silently replacing malformed values with misleading defaults. A parser that turns a broken timestamp into 1970-01-01 doesn’t fix bad data; it hides it inside your charts where nobody can find it. Route rejects to a dead-letter path — a file, a queue topic, a separate table — so they can be inspected and replayed after the producer bug is fixed.

Sketch the pipeline

Sketch producer, queue, ClickHouse, and dead-letter paths. Name who retries, how duplicates are detected, and how ingestion lag is measured.

If any of those three questions has no owner, that’s the hole an incident will find. The pipelines that survive messy weeks are the ones where every arrow in the sketch has an answer for “what happens when this step runs twice?”