At-Least-Once and Its Duplicates

Concepts covered: Idempotency

At-least-once is the default in almost every messaging system. Kafka, SQS, Pub/Sub, RabbitMQ all default to at-least-once. The reason: it is the only semantic that guarantees no data loss without the complexity of transactions. The tradeoff is duplicates, which are handled downstream.

The production default: at-least-once delivery (no data loss) plus an idempotent sink that upserts by key, so a redelivered duplicate overwrites rather than double-counts. Cheaper and simpler than true exactly-once.

producer (source)
kafka (queue, Kafka, at-least-once)
consumer (transform)
idempotent_sink (storage, upsert by key)

producer -> kafka
kafka -> consumer
consumer -> idempotent_sink

How Duplicates Happen

Consumer reads message, processes it, writes output.
Consumer attempts to commit the offset.
Consumer crashes before the commit succeeds.
Consumer restarts, reads from the last committed offset.
The same message is processed again. Duplicate output.

Downstream Dedup Strategies

Strategy	Mechanism	Best For
MERGE/Upsert	INSERT ON CONFLICT UPDATE by event_id	Entity-centric data (customers, orders)
DELETE-INSERT	Delete partition, rewrite all records	Fact table daily loads
Dedup at query time	ROW_NUMBER() OVER (PARTITION BY event_id ORDER BY ts DESC) WHERE rn = 1	Analytical queries, ad hoc analysis
Bloom filter	Probabilistic check before write	High-throughput streams where exact dedup is too expensive

The senior insight: at-least-once delivery + idempotent consumer = effectively-once processing. This is cheaper and simpler than exactly-once semantics. Most production pipelines use this pattern. The consumer's write operation (MERGE, DELETE-INSERT) naturally deduplicates.

No Hire

"Duplicates shouldn't happen"
"We'd use exactly-once for everything"
No awareness that at-least-once is the default

Strong Hire

"At-least-once with idempotent MERGE gives us effectively-once at lower cost"
"Duplicates are expected. The write pattern handles them."
"I'd only pay the exactly-once cost for financial data where a duplicate is catastrophic"

About this lesson

This section is part of the Delivery Semantics: Advanced lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.