Design an idempotent event pipeline

Events arrive more than once. A producer times out and resends. A queue redelivers after a consumer crashes halfway through a batch. A deploy replays an hour of traffic. None of this is rare. At-least-once delivery is the honest contract of almost every pipeline, so duplicates are an input to your design, not an edge case.

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

Give every event a stable identity

Add a stable event ID before you ever retry a batch:

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

Mint the ID where the event happens, in 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, which defeats the whole point.

With identity in place you can layer defenses. Resend a batch with the same insert_deduplication_token setting and ClickHouse drops the repeat delivery. Whatever slips through stays detectable, and cleanable, because duplicates share an event_id:

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

Zero rows is the answer you want. I run this after every replay.

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 by hours after an outage. Store both: ts from the source, and ingested_at DateTime DEFAULT now().

Charts group by event time. The gap between the two columns is your ingestion lag. Watching its maximum tells you when the pipeline is falling behind, long before anyone notices a flat line on a chart.

Reject loudly, not silently

Validate required fields at the producer or at a staging boundary. Keep failed batches so you can look at them. Never replace a broken value with a misleading default.

A parser that turns a bad timestamp into 1970-01-01 doesn’t fix the data. It hides the problem inside your charts, where nobody will find it. Route rejects to a dead-letter path instead: a file, a queue topic, a separate table. Then you can inspect them and replay them after the producer bug is fixed.

Sketch it before you build it

Try this on your own pipeline. Draw the producer, the queue, ClickHouse, and the dead-letter path. Next to each arrow, write who retries, how duplicates get detected, and how ingestion lag is measured.

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