Why route rejected rows to a quarantine table instead of dropping them or failing the load?
answer
- three choices, not two
- green job, shrinking row count
- the rejected row is still evidence
- keep it so you can replay it
- unwatched reject table equals dropping
basics
~20 sA quarantine table keeps each rejected row plus the reason it failed, so good data still lands while bad data stays visible and replayable. Dropping destroys the evidence silently; failing the whole load punishes every valid row for a few defects.
solid answer
~50 sEvery ingestion job must decide what to do with a row it cannot accept: discard it, stop everything, or set it aside. Discarding is the dangerous default — the job exits green, the target quietly holds fewer rows than the source, and nobody notices for weeks. Halting is honest but blunt: one malformed row in two million holds up the other 1,999,999, and tomorrow's batch stacks up behind today's. Quarantine is the middle path — clean rows go to the target, rejected rows go to a separate table or dead-letter topic tagged with the failure reason and a pointer back to the source, and a reject-rate metric makes the failure loud. Because the record is preserved, the failure is reversible: fix the parser or the mapping and replay it. Quarantine only works if someone watches it; an unmonitored reject table is just a slower way of dropping rows.
code
sql · 13 lines-- good rows land; rejects go aside with a reason, not to /dev/null
INSERT INTO orders_target (order_id, customer_id, amount)
SELECT s.order_id, s.customer_id, s.amount
FROM staging_orders s
WHERE s.order_id IS NOT NULL AND s.amount >= 0;
INSERT INTO orders_quarantine (raw_record, reject_reason, source_file, ingested_at)
SELECT to_json(s),
CASE WHEN s.order_id IS NULL THEN 'null_order_id' ELSE 'negative_amount' END,
s.source_file,
current_timestamp
FROM staging_orders s
WHERE s.order_id IS NULL OR s.amount < 0;go deeper
Be ready to name the three outcomes for a bad row — drop, halt, quarantine — and say why a silent drop is the one that hurts most. Knowing that the reject needs a reason stored with it is most of the answer at this level.
Explain the mechanics: the split write, what the reject store holds, and the reject-rate metric that makes the failure visible. Interviewers expect you to add pass-through-and-flag as a fourth option and say when it fits.
Show you have operated one. Talk about triage ownership, the oldest-untriaged-record alert, and the failure mode where quarantine quietly becomes a landfill. Be able to say which defects you would still fail the load on.
Own the policy: which sources get quarantine versus fail-fast, who is accountable for triaging rejects, and how completeness is reported to consumers so a partially-loaded batch is never mistaken for a whole one.
## The choice every ingestion job faces When a pipeline reads a record it cannot accept — a date that will not parse, a required key that is null, a foreign key pointing nowhere, a JSON document failing its schema — it has exactly three options. Discard the record and carry on. Abort the entire load. Or put the record somewhere separate, along with the reason it was refused, and carry on with the rest. That third option is what quarantine means: a reject table, a reject file, or a dead-letter topic holding records the pipeline refused, annotated with why. ## Why silent dropping is the worst default Dropping is attractive because it is one line of code — a `WHERE` clause, a `filter`, a `try/except` that swallows. The problem is not that rows are lost; it is that nothing says so. The job exits zero, the dashboard is green, the target's row count is simply smaller than the source and nobody is comparing the two. Months later an analyst notices revenue is off and there is no record of which rows went missing or when it started. Silent dropping converts a data-quality problem into a trust problem, and trust problems take far longer to repair than parsers. A drop is defensible only when it is deliberate, documented and **counted** — discarding heartbeat events you never intended to store, say — and even then the count belongs in a metric. ## Why halting the load is also usually wrong Fail-fast is the honest opposite: load nothing if anything is wrong. It has a real place, but as a blanket policy it makes the pipeline brittle in exactly the way real sources are messy. One bad row blocks every good one, the downstream models do not build, someone is paged at 03:00 for a single mistyped postcode, and the backlog grows. Fail-fast also breeds a bad habit: the fastest way to unblock a pipeline at 03:00 is to delete the offending row from staging — which is silent dropping performed by a tired human. ## What quarantine actually buys **Continuity.** Good records land on schedule. The blast radius of a defect is the defect, not the batch. **Evidence.** The rejected record is preserved as it arrived, with the rule that refused it and a pointer back to its origin. That is the difference between "some rows are missing" and "1,432 rows from the 2026-08-20 orders file failed the non-negative-amount rule". **Reversibility.** Because the record survives, the failure is not terminal. Once the parser, the mapping or the contract is fixed, the quarantined records replay through the same pipeline and the target becomes complete. Dropped rows can only be recovered by re-extracting from the source, which is expensive for large batches and often impossible for streams whose retention has passed. ## A lifecycle, not just a table A quarantine store is worth having only if records leave it. The pattern that works is a small state machine: a record arrives as `new`; someone triages it and either fixes the pipeline and marks it for replay, or marks it legitimately unloadable (a test record, a duplicate, a genuinely invalid submission) and closes it. Two metrics keep this honest — the reject rate per run, which catches sudden breakage, and the age of the oldest open quarantined record, which catches the slow rot where the table grows forever and nobody looks. Without those, quarantine degenerates into dropping with extra storage costs. ## When drop or halt is right after all Halt when the defect makes the rest of the batch untrustworthy rather than merely incomplete: the file has an unrecognisable schema, the row count disagrees with the manifest, control totals do not reconcile, or the reject rate jumps far above its normal band — which nearly always means the source changed rather than that the data got worse. Halt too when partial data is worse than late data: a financial close, a regulatory extract, a reconciliation feed. Drop only what you decided in advance you do not want, and count it. ## The fourth option interviewers probe There is a fourth shape worth naming: **pass-through-and-flag**, where the suspect record is loaded into the target carrying a validity flag or a quality score. Consumers who need completeness can see it; consumers who need cleanliness filter it out. That is the right choice when excluding a row skews an aggregate more than including a dubious one does — a revenue fact whose customer key is unknown usually belongs in the table pointing at an "unknown" member, not in a reject table where the day's total silently shrinks.
- What turns a quarantine table back into silent dropping?Nobody reading it. If no metric reports the reject rate per run and no alert fires on the age of the oldest untriaged record, rows accumulate unseen and the outcome is identical to dropping — you just pay for the storage. Quarantine is a lifecycle with an owner and an exit path, not a destination.
- When is loading the suspect row with a validity flag better than quarantining it?When excluding it distorts an aggregate more than including it does. A revenue fact with an unresolvable customer key usually belongs in the table pointed at an unknown-member row, flagged, so the day's total stays correct and consumers who need clean joins can filter. Quarantine suits rows that are unusable at all, not merely imperfect.
- Does quarantining rows mean the load can be reported as successful?Only if the run's reject count and rate are part of the reported outcome. A run that quarantined a normal handful is a success; a run that quarantined a tenth of the batch is a failure that happened to write some rows. Report rows read, rows loaded and rows rejected together, never just the last two.
It is the post office's undeliverable shelf: the rest of the round still gets delivered, and the one letter with a bad address waits somewhere findable until it is corrected — rather than being binned or the whole round cancelled.
saying these in an interview costs you the question
- Filtering bad rows out in the query and calling that handling
- Assuming a green job means every source row landed
- Treating quarantine as a bin nobody ever needs to read
- Halting the whole load for any single malformed row
- Believing dropped rows can always be re-extracted from the source later