Here's a scenario every data team has lived through: a nightly job fails partway, gets automatically retried, finishes fine, and the next morning someone notices the numbers don't add up. Not because the retry failed. Because it worked.
This is one of the interview questions we teach inside our How to Interview Engineers Without Being an Engineer course, because it does something most technical interview questions don't: it lets a completely non-technical interviewer tell a strong data engineer from a weak one, just by listening for a handful of specific words and ideas. No SQL required. No whiteboard. Just one story, and a rubric.
The Scenario
"A nightly ETL job loads yesterday's transactions into the warehouse. Last night, it failed two-thirds of the way through because of a network blip. It was automatically retried an hour later, and finished successfully. This morning, some transactions are showing up twice. How would you design this pipeline so a failed-and-retried run can never create duplicates, no matter when it fails? Explain your answer to someone who doesn't code."
Why This Question Works
What separates a mid-level engineer from a senior one is usually how they think about failure, not whether they've memorized a tool. Junior engineers design for the happy path. Senior engineers assume things will fail and build pipelines that stay correct no matter how many times they're re-run.
The Logic, in Plain English
The trap: a weak fix re-runs from the top, treating every record as brand new, so the retry re-inserts the same 650 transactions a second time. The fix is called idempotency: running the job twice with the same input should produce the exact same result as running it once, by checking each transaction's unique ID and updating the existing row instead of adding a new one.
Our favorite way to explain it: think of a mail carrier re-delivering a route after lunch. Deliver by house address, and redelivering the whole route twice never gives anyone two copies of the same letter. Grab "the next envelope in the pile" instead, and a repeated route means duplicates. Same letters. The only thing that changes is whether the system checks where something belongs before dropping it off.
What a Weak Answer Sounds Like
- "We'd just make sure the job doesn't fail." (Avoids the question.)
- "We'd get an alert and manually delete the duplicates." (Cleanup, not prevention.)
- "We'd only run it once a day." (Doesn't address partial failure at all.)
The Scorecard (1–5)
| Score | Label | What It Looks Like |
|---|---|---|
| 1 | No grasp of the problem | Denies or sidesteps the issue; no sign retries or partial failures are normal. |
| 2 | Reactive, not preventative | Fixes duplicates after they happen (alert plus manual cleanup); nothing prevents them. |
| 3 | Passable | Right instinct, "check if it's already there first," but can't explain the mechanism. No unique ID, vague on how the check actually works. |
| 4 | Solid | Clearly explains idempotency and upserting by a unique ID, in plain terms. |
| 5 | Outstanding | Everything in a 4, plus separates "make one record safe to reprocess" from "make sure a retry reprocesses the right batch" (well-defined batches by date, not "wherever it stopped"), and can teach the idea in a sentence or two without losing a non-technical listener. |
Sample Answers, Scored
Five ways a candidate might answer the prompt above, each scored against the rubric.
"We'd just make sure the job doesn't fail in the first place."
"We'd get paged, run a quick script to remove the duplicate rows, and let the team know it's fixed."
"Before inserting a record, we'd check if it's already in the table and skip it if so."
"Each transaction has a unique ID. Instead of inserting blindly, we'd upsert: if the ID already exists, we update that row instead of adding a new one. Run the job once or ten times, the warehouse ends up the same either way."
"Two things need to be true. First, each transaction needs a unique ID, so a single record can be safely upserted instead of duplicated. Second, the job needs to process a well-defined batch, like 'yesterday's date,' not 'wherever the last run stopped,' so a retry always redoes the same batch instead of skipping or repeating an arbitrary chunk. Put together: safe to reprocess a record, and safe to reprocess a batch."
Try It in Your Next Screen
This is one of dozens of prompts we teach inside How to Interview Engineers Without Being an Engineer, each with the same plain-English explanation and 1–5 scorecard. Or book an interviewer to have us run the whole process for you.
