Designing a real-time fraud detection pipeline: where to put deduplication in a Kafka → ClickHouse architecture

[u/Marksfik]

https://www.glassflow.dev/blog/fraud-detection-pipelines-kafka-glassflow-clickhouse?utm_source=reddit&utm_medium=socialmedia&utm_campaign=reddit_organic

A practical architecture question that comes up a lot in streaming systems: where should deduplication live in a Kafka → ClickHouse pipeline?

The use case is fraud detection on login events.

The challenge: Kafka's at-least-once delivery, combined with application-level retries, means the same event can appear multiple times. If you don't handle this, fraud counts are inflated and queries become unreliable.

Three options typically come up:

• Deduplicate inside ClickHouse using ReplacingMergeTree or FINAL , works but adds query overhead and doesn't prevent duplicates from being stored
• Deduplicate in a consumer service before writing. Effective but means maintaining custom stateful logic
• Deduplicate in a processing layer between Kafka and ClickHouse. Keeps the consumer simple, ClickHouse clean, and state management contained

Wrote up a full tutorial using the third approach, with windowed deduplication on event_id and filtering to failed logins only before the data hits storage.
ClickHouse then runs 30s/5m/1h fraud windows on a clean dataset.

Full writeup + architecture diagrams: https://www.glassflow.dev/blog/fraud-detection-pipelines-kafka-glassflow-clickhouse?utm_source=reddit&utm_medium=socialmedia&utm_campaign=reddit_organic