Exactly-once from Kafka into ClickHouse
Kafka's exactly-once semantics cover reading from and writing to Kafka. The moment the sink is another system, the guarantee becomes whatever that system can do with a retried write. ClickHouse can do quite a lot, as long as the consumer is built for it.
Make every batch repeatable
After a crash, the consumer must rebuild exactly the batch it tried before, and ClickHouse must recognise it. Both halves need care.
For the first half, cut batches by offsets, not by time. A batch is "partition 3, offsets 1,200,000 to 1,249,999". Commit the offset to Kafka only after the insert succeeds. After a restart the consumer resumes from the committed offset and produces the same batch.
Let ClickHouse drop the retry
Replicated MergeTree tables deduplicate inserts by default: each inserted block is hashed, and a block whose hash was seen recently is ignored. Hash-based dedup breaks as soon as the batch contents differ slightly, so give it an explicit token derived from the offsets:
INSERT INTO events
SETTINGS insert_deduplication_token = 'events-p3-1200000-1249999'
FORMAT RowBinary
For a non-replicated MergeTree, deduplication is off unless the table sets a window:
ALTER TABLE events
MODIFY SETTING non_replicated_deduplication_window = 1000;
The window is a count of recent inserts, not a duration. If the consumer can fall far behind and retry an old batch, make it larger.
When rows themselves repeat
Token dedup handles retried batches. It does nothing for a producer that sends the same event twice in different batches. For that, the table needs a natural key and ReplacingMergeTree:
ENGINE = ReplacingMergeTree(ingested_at)
ORDER BY (account_id, event_id)
Duplicates disappear on merge, which happens eventually rather than immediately. Queries that must be exact use FINAL or aggregate with argMax; dashboards usually tolerate the brief overlap.
What we didn't do
The Kafka table engine is convenient, but its delivery is at-least-once and batch boundaries aren't under your control, so tokens can't be made deterministic. For tables where an occasional duplicate is acceptable it is fine. For billing data we run our own consumer.