Make the sink idempotent so replays never duplicate rows

At a glance

Learning objectives

• Make the sink idempotent rather than trying to make the source exactly-once
• Deduplicate by a business key + event time within the watermark
• Rank change events deterministically by (lsn, ts_ms), never by arrival time

What you need

• A stream writing into Delta/Iceberg or a warehouse table with a stable business key
• [object Object]

Steps

1. Name the failure mode — streaming sinks either duplicate on replay or lose rows on failure. (from pattern-streaming-dedup-upsert )
2. Make the sink idempotent rather than trying to make the source exactly-once. (from pattern-streaming-dedup-upsert )
3. Deduplicate by a business key + event time within the watermark, then MERGE (upsert) on the key — a replay recomputes the same rows and merges to the same state, idempotent by construction. (from pattern-streaming-dedup-upsert )
4. Order by event time, not arrival time. (from pattern-streaming-dedup-upsert )
5. For change streams, rank by `(lsn, ts_ms)` — never by ingestion time; ranking by LSN makes re-running the same micro-batch produce the same table. (from skill-cdc-merge-designer )
6. Deduplicate before merging — keep the latest event per primary key with a window function, then merge once. (from skill-cdc-merge-designer )
7. Plan for checkpoint loss — checkpoint lost means duplicates, and the fix is making the sink idempotent. (from project-kafka-spark-delta )

Check yourself

• Replaying the same micro-batch twice produces an identical table — the merge is idempotent by construction.
• The dedupe key is a business key plus event time within the watermark, not an offset and not an arrival timestamp.

Worked example

stream ─▶ [windowed dedupe by key] ─▶ MERGE (upsert on key) ─▶ table

Events arrive out of order. Rank by `(lsn, ts_ms)` — never by ingestion time.

Re-running the same micro-batch must produce the same table. Ranking by LSN
guarantees this; ranking by arrival time does not.

Sourced from

• Streaming Dedup and Upsert
• CDC Merge Designer
• Kafka → Spark Streaming → Delta

Security scan

Last sweep 2026-10-24: 0 finding(s), 0 marked as examples.

Common gotchas

• Ranking by arrival time breaks idempotency — rank by (lsn, ts_ms) or event time.
• The pattern requires a stable business key and event time; state cost is the price.
• A pure append-only event log with no dedup need does not need this pattern.

Production considerations

• Correct under replay with no reliance on perfect exactly-once — that is the goal, and it is achievable.
• MERGE aggregates on the window key so replays converge.

Diagram

idempotent sink
   |
   |- stream arrives (at-least-once, replays included)
   |
   |- windowed dedupe by business key + event time
   |
   |- MERGE (upsert on key) --> table converges
   |
    --> replay the same batch --> same rows --> same state

Registry stats