The failure scenario

A fictional daily load contains events A for $100 and B for $50. The first attempt writes A, then fails before B. A blind append retry writes A and B again, leaving $250 instead of $150. The scheduler successfully retried, but the destination is now wrong.

Choose an identity with meaning

Use a stable source event ID when available. If an order can have several legitimate events, order_id alone is not an event key. A composite key may be appropriate, but document its assumptions. A timestamp can collide or change precision; it is not automatically a reliable identity.

One safe processing pattern

1. Read a bounded source window and record its boundaries.
2. Write to a staging area with a run identifier.
3. Validate keys, row counts and business totals.
4. Merge or replace the target population under a documented key rule.
5. Publish the completion marker only after the write succeeds.
6. Retain enough run metadata to diagnose and replay a failure.

The exact atomicity guarantees depend on the storage system. A file replacement, database transaction and distributed table merge do not have identical failure modes. In an interview, describe the guarantee your design needs and verify that the chosen system provides it.

Practise rerun behaviour in Python

events = [('A', 10000), ('B', 5000)]
ledger = {}
ledger['A'] = 10000  # first attempt stops here
for event_id, amount_cents in events:
    ledger[event_id] = amount_cents
assert len(ledger) == 2
assert sum(ledger.values()) == 15000
for event_id, amount_cents in events:  # same input again
    ledger[event_id] = amount_cents
assert sum(ledger.values()) == 15000

This in-memory illustration replaces values by event key, so rerunning the same input keeps the total at $150. It is not a production database implementation and does not solve concurrency or durable recovery. It also assumes a repeated event ID has the same meaning. Conflicting versions require an explicit ordering or rejection rule.

Handle late events separately

A high-water mark based only on event time can miss late arrivals. Depending on source guarantees, consider an overlap window with deduplication, ingestion-time tracking or a periodic reconciliation. Define how far back a correction can arrive and how older partitions are rebuilt.

Interview follow-up

Explain what happens if the process fails after writing the destination but before saving the checkpoint. Then explain two simultaneous retries. A strong answer identifies the required transaction boundary, unique constraint or concurrency control rather than claiming that “exactly once” happens because the job has retries enabled.