The destination is append-only, offers no transaction and enforces no key — how do you still make a replay leave no extra trace?

seniorshould knowfreq 50%

answer

prevention moves to the reader
one act makes the whole result visible
a deterministic identifier on every row
record intent before an unretractable effect
accepting duplicates requires disclosure

basics

~20 s

Three moves remain: publish staged output in one visible act so abandoned attempts never appear, stamp every row with an identifier derived from the input record and collapse copies at read time, or record intent before an effect that cannot be retracted. The fourth honest answer is to accept duplicates and say so.

solid answer

~50 s

With no key and no transaction the destination cannot absorb the repeat, so you move the work somewhere it can happen. First, make visibility atomic even though the write is not: each unit writes to a private location and one final act publishes the whole result, so a failed attempt's partial output is never seen. Second, stamp every row with a deterministic identifier computed from the input record and have every consumer read through a view that keeps one copy per identifier — the duplicate still lands, it simply stops counting. Third, where the effect leaves the system entirely and cannot be retracted, durably record the intent with that identifier before acting and consult the record on restart; that narrows the window, it does not close it. Fourth, and sometimes correct: accept the duplicates, mark the replayed span, and tell the consumers what they may double-count.

code

sql · 18 lines

sql

-- Every row carries record_id, computed from the input record alone.
-- Consumers read this view, never the landed table.
CREATE VIEW orders_deduplicated AS
WITH numbered AS (
  SELECT
    order_id,
    amount_cents,
    record_id,
    written_at,
    ROW_NUMBER() OVER (
      PARTITION BY record_id
      ORDER BY written_at ASC
    ) AS copy_no
  FROM orders_landed
)
SELECT order_id, amount_cents, record_id
FROM numbered
WHERE copy_no = 1;

go deeper

for a junior

Recall that a destination which cannot replace by key will store both copies, and that the usual remedies are to publish results in one visible act or to filter duplicates when the data is read.

for a middle

Explain how staging plus a single publish hides a failed attempt's partial output, and how a deterministic row identifier lets a reader keep one copy per record.

for a senior

Show the trade you are making: who pays for reader-side deduplication, how far back it must look, what the tiebreak means when the copies differ, and why an unretractable external effect is a different problem.

for a principal

Ask whether this destination should be absorbing replays at all. Moving the effect behind something that accepts a caller-supplied identifier, or declaring the destination duplicate-tolerant in writing, may both beat engineering around it.

Why this case is the interesting one

The easy answers — a deterministically chosen write key, or committing output and the recorded read position as one unit — both need something from the destination. A great many destinations give you nothing: an append-only log of files, an object store, a plain directory, a third-party endpoint. The question is what remains, and the honest answer is that the duplicate can no longer be prevented at the write, only made not to count.

Move one: make visibility atomic even when the write is not

Staged output promoted in a single step — each unit writes to a private location and a single final act makes the whole result visible at once, so a failed and re-run unit leaves its abandoned partial output invisible. This is the workhorse for file and object destinations, and it covers the most common duplicate: the half-written output of a unit that died.

What varies, and what interviewers listen for:

where publishing is a directory move within one filesystem, the final act is cheap and genuinely atomic;
where the store has no cheap move, publishing by copying many objects is neither cheap nor atomic, so the atomic act must be a single small write — a pointer, a manifest listing the objects that count — and the objects themselves become invisible garbage until something lists them;
it protects against a failed attempt, not against a deliberate second run that publishes again.

Move two: push deduplication onto the reader

Write a deterministic row identifier, computed only from the input record, alongside every row, and have consumers read through a view that keeps one copy per identifier. The mechanics are ordinary: number the copies within each identifier and keep the first.

The costs are real and permanent, and naming them is what separates a senior answer from a textbook one:

every reader pays, forever — including readers that appear years later and never heard of the replay;
the tiebreak must be defined — if two copies differ (a non-deterministic value, a code change between the runs), "keep one" is a decision, not a detail;
the deduplication span has to be bounded — collapsing over all history is expensive, and collapsing over a window silently stops working if a replay reaches further back than the window;
one forgetful consumer undoes it — a job that reads the raw rows instead of the view double-counts, and nothing warns it.

Move three: an effect that cannot be retracted

When the effect leaves the system — money moved, a message sent to a third party, an alert raised — there is nothing to overwrite and nobody to deduplicate for you. The mechanism is a write-ahead record of intent: the job durably records what it is about to do, with the deterministic identifier, before doing it, so a restart can tell whether the effect already happened and finish or skip accordingly.

It narrows the window; it does not close it. The job can act and die before recording that it acted, and the restart then cannot distinguish "acted" from "about to act". The genuine fix is on the other side: if the external system accepts a caller-supplied identifier and refuses a second call carrying the same one, the problem moves to where it can actually be solved. If it does not, you are choosing between a possible duplicate effect and a possible missing one, and that choice belongs to whoever owns the money.

Move four: accept it, and say so

Sometimes the right answer is that this destination tolerates duplicates. Approximate counters, sampled telemetry, a search index that will be rebuilt, a staging area that a later step deduplicates anyway. Then the work is not mechanism but disclosure:

record which span of input was replayed, and when;
tell the consumers that can distinguish a real increase from a re-emitted one;
keep a way to quantify the overlap, so a question about Tuesday's numbers has an answer.

What fails a candidate is not choosing this option; it is choosing it silently, so that a number nobody can explain shows up in a report three weeks later.

## Why this case is the interesting one The easy answers — a **deterministically chosen write key**, or committing output and the recorded read position as one unit — both need something from the destination. A great many destinations give you nothing: an append-only log of files, an object store, a plain directory, a third-party endpoint. The question is what remains, and the honest answer is that the duplicate can no longer be prevented at the write, only made not to count. ## Move one: make visibility atomic even when the write is not **Staged output promoted in a single step** — each unit writes to a private location and a single final act makes the whole result visible at once, so a failed and re-run unit leaves its abandoned partial output invisible. This is the workhorse for file and object destinations, and it covers the most common duplicate: the half-written output of a unit that died. What varies, and what interviewers listen for: - where publishing is a directory move within one filesystem, the final act is cheap and genuinely atomic; - where the store has no cheap move, publishing by copying many objects is neither cheap nor atomic, so the atomic act must be a single small write — a pointer, a manifest listing the objects that count — and the objects themselves become invisible garbage until something lists them; - it protects against a failed attempt, not against a deliberate second run that publishes again. ## Move two: push deduplication onto the reader Write a **deterministic row identifier**, computed only from the input record, alongside every row, and have consumers read through a view that keeps one copy per identifier. The mechanics are ordinary: number the copies within each identifier and keep the first. The costs are real and permanent, and naming them is what separates a senior answer from a textbook one: - **every reader pays, forever** — including readers that appear years later and never heard of the replay; - **the tiebreak must be defined** — if two copies differ (a non-deterministic value, a code change between the runs), "keep one" is a decision, not a detail; - **the deduplication span has to be bounded** — collapsing over all history is expensive, and collapsing over a window silently stops working if a replay reaches further back than the window; - **one forgetful consumer undoes it** — a job that reads the raw rows instead of the view double-counts, and nothing warns it. ## Move three: an effect that cannot be retracted When the effect leaves the system — money moved, a message sent to a third party, an alert raised — there is nothing to overwrite and nobody to deduplicate for you. The mechanism is a **write-ahead record of intent**: the job durably records what it is about to do, with the deterministic identifier, before doing it, so a restart can tell whether the effect already happened and finish or skip accordingly. It narrows the window; it does not close it. The job can act and die before recording that it acted, and the restart then cannot distinguish "acted" from "about to act". The genuine fix is on the other side: if the external system accepts a caller-supplied identifier and refuses a second call carrying the same one, the problem moves to where it can actually be solved. If it does not, you are choosing between a possible duplicate effect and a possible missing one, and that choice belongs to whoever owns the money. ## Move four: accept it, and say so Sometimes the right answer is that this destination tolerates duplicates. Approximate counters, sampled telemetry, a search index that will be rebuilt, a staging area that a later step deduplicates anyway. Then the work is not mechanism but disclosure: 1. record which span of input was replayed, and when; 2. tell the consumers that can distinguish a real increase from a re-emitted one; 3. keep a way to quantify the overlap, so a question about Tuesday's numbers has an answer. What fails a candidate is not choosing this option; it is choosing it silently, so that a number nobody can explain shows up in a report three weeks later.

How far back should reader-side deduplication look?
Far enough to cover the largest replay the job can produce, which is bounded by how far a restart rewinds — the recovery point interval for a stateful job, the unit for a finite one. Collapsing over all history is the safe and expensive choice; a bounded span is cheaper and fails silently if a replay ever reaches past it, so the bound has to be justified and monitored, not guessed.
The replayed rows differ from the originals because the logic changed between runs. What breaks?
Deduplication stops being a no-op and becomes a choice of which version wins. Keeping the earliest copy pins the old logic's output; keeping the latest lets the replay correct it, but then any reader that already consumed the earlier copy has the old number. Decide the tiebreak explicitly and make it the same in every consumer.
Can you not simply check whether the row already exists before writing it?
Read-then-write across two systems is not atomic: two attempts can both read absent and both write, and a crash between the check and the write reproduces the original problem. It also costs a read per row. The check only helps when the destination performs it itself as part of the write, which is exactly the capability this destination lacks.

saying these in an interview costs you the question

Reads before writing and calls that check a guarantee
Thinks publishing staged output also prevents a deliberate second run
Assumes deduplication at the reader is free once the view exists
Believes a write-ahead record of intent closes the window entirely
Accepts duplicates without telling any downstream consumer
Assumes publishing many objects is atomic because a directory move is

The destination is append-only, offers no transaction and enforces no key — how do you still make a replay leave no extra trace?

§ answer

basics

solid answer

code

§ go deeper

▾level notes4 levels

▾deep dive~4 min

Why this case is the interesting one

Move one: make visibility atomic even when the write is not

Move two: push deduplication onto the reader

Move three: an effect that cannot be retracted

Move four: accept it, and say so

▾common follow-ups3 questions

▾red flags6 flags

Why this case is the interesting one

Move one: make visibility atomic even when the write is not

Move two: push deduplication onto the reader

Move three: an effect that cannot be retracted

Move four: accept it, and say so

answer

go deeper