Design a data pipeline that is safe to re-run: idempotent writes, late data, and exactly-once effects.

TL;DR: You cannot guarantee exactly-once delivery, so engineer exactly-once effects via idempotency: make every write a deterministic upsert keyed by a stable business/event key (MERGE or insert-overwrite-by-partition), so reprocessing the same data converges to the same result. Handle late data with event-time windows and a watermark that bounds how long you wait, and checkpoint progress so a retry resumes cleanly.

How to approach it. Correct the framing first: distributed systems give at-least-once delivery, so the goal is idempotent processing, not magical exactly-once. Then cover the three mechanisms in order: idempotent writes, partition/window scoping, and watermarking for late events.

A strong answer. The core principle is that a re-run must not change the result. That means idempotent writes.

• Upsert by a stable key. Instead of blind INSERT (which double-counts on retry), MERGE on a deterministic key (event id, or business key plus event-time bucket) so reprocessing updates in place rather than appending duplicates. On a lakehouse, MERGE INTO or INSERT OVERWRITE of a whole partition does this atomically.
• Partition-scoped overwrite. For batch, recompute a full partition (say, one day) and atomically replace it. Re-running the day produces the same partition: no duplicates, no surgical deletes. This is the simplest path to idempotency and the one I default to.
• Deduplicate on a key. If the source can deliver duplicates, dedupe within the batch (ROW_NUMBER over the event key, keep the latest) before the write.

Late and out-of-order data: process on event time, not processing time, and group into event-time windows. A watermark declares "I will not accept events older than T behind the latest seen," which bounds state (you can close and emit a window) while tolerating reasonable lateness. Events past the watermark go to a side output or a correction job rather than silently corrupting a closed window. For a 7-day dedup window, a watermark plus keyed state with TTL keeps memory bounded.

Reliability: checkpoint offsets/progress so a failure resumes from the last committed point, and make the commit of "data written" and "offset advanced" atomic (transactional sink) so you never advance past unwritten data or rewrite committed data.

The defensible stance: design for at-least-once delivery and idempotent effects, and do not assume a system that promises "exactly-once" relieves you of idempotent writes.

Key takeaways

• Chase exactly-once effects, not exactly-once delivery: idempotent writes survive retries, replays, and backfills.
• Partition-overwrite is the cheapest idempotency lever for batch; keyed MERGE when you cannot rewrite a whole partition.
• Event time plus a watermark tied to the observed lateness distribution bounds state and routes stragglers to a correction path.
• Commit data and advance the offset atomically, or a crash between them loses or duplicates data.

What interviewers probe next.

• "Why not rely on exactly-once delivery?" It is fragile and usually holds only within one system; idempotent writes survive retries, replays, and backfills regardless.
• "Backfill without double-counting?" Partition-overwrite or keyed MERGE makes a backfill a no-op-or-correct operation, the whole point of idempotency.
• "Watermark too tight vs too loose?" Too tight drops valid late data; too loose holds state and delays results. Tie it to the observed lateness distribution.
• "Streaming exactly-once in practice?" Transactional sinks plus checkpointed offsets (Spark Structured Streaming, Flink) give effectively-once results when the sink supports idempotent or transactional writes.

Common mistakes.

• Blind INSERT/append, so a retry double-counts.
• Processing on wall-clock time, so late events land in the wrong window or are lost.
• No watermark, so windowed state grows unbounded.
• Advancing the source offset before the write commits, losing data on a crash between the two.