Partial Failure in a Batch
Concepts covered: Dead Letter Queues
A file of 10,000 rows with 37 bad ones
At 02:00 on Sunday the payouts job loads a file of driver payouts into the 2026-10-24 partition of fact_driver_payouts. The file has 10,000 rows, and 37 of them have an amount_raw that won't cast to a decimal. A batch has to decide what the partition holds as a whole, and there are 3 answers.
| Strategy | Partition this morning | The failures | Rerun after the fix |
|---|---|---|---|
| All-or-nothing | Empty; the swap never ran | Run blocked, owner paged | All 10,000 rows |
| Skip-and-quarantine | 9,963 rows | 37 rows in `quarantine_driver_payouts` | The 37 rows |
| Partial commit, 10 chunks of 1,000 | 8,000 rows from 8 chunks | Chunks 3 and 8 marked failed in a run ledger | 2,000 rows in 2 chunks |
All-or-nothing: a partial answer is a wrong answer
fact_driver_payouts feeds the bank transfer file. A partition with 9,963 rows looks healthy: it has a total, and 37 payouts are missing with nothing on any dashboard to say so. When a consumer acts on a partial output as if it were complete, the batch must land whole or not at all. The job loads into staging_driver_payouts first and swaps the partition in only after a check passes.
SELECT
COUNT(*) AS total_rows,
COUNT_IF(TRY_CAST(amount_raw AS DECIMAL(12, 2)) IS NULL) AS bad_rows
FROM staging_driver_payouts
WHERE batch_date = DATE '2026-10-24'The task running this query fails when bad_rows is above 0, so the swap task downstream of it never runs and every earlier partition stays as it was. Here the rerun is cheap, a 4-minute load, so discarding 9,963 good rows costs almost nothing.
Skip-and-quarantine: a gap you can name
Suppose instead the file fed only a driver-earnings dashboard that the operations team reviews weekly. A gap of 0.37% is acceptable there, as long as it is recorded and later filled. The job writes failing rows to a quarantine table with a reason, then loads the rest. A threshold still guards against a broken file: when more than 1% of rows fail, the file itself is suspect and the run stops.
INSERT INTO quarantine_driver_payouts
SELECT
payout_id,
driver_id,
amount_raw,
'amount_raw is not a decimal' AS reason,
batch_date
FROM staging_driver_payouts
WHERE batch_date = DATE '2026-10-24'
AND TRY_CAST(amount_raw AS DECIMAL(12, 2)) IS NULLThe load into fact_driver_payouts uses the opposite filter, IS NOT NULL. Both statements are preceded by a delete of the 2026-10-24 rows they wrote earlier, so a rerun replaces its output instead of appending to it. When the 37 rows are corrected, they are merged into the partition on payout_id, the business key, so a second fix attempt cannot double them.
Partial commit: a run too costly to repeat
Now change a single fact: before loading, every row is scored by a fraud service, and each chunk of 1,000 rows takes 25 minutes, so the run takes over 4 hours. The job commits each chunk in its own transaction and records it in a ledger. A chunk fails as a unit, whether a row inside it is bad or a write times out, and the job carries on with the next chunk.
| chunk_id | rows | status | finished_at |
|---|---|---|---|
| 1 | 1 to 1,000 | committed | 02:25 |
| 2 | 1,001 to 2,000 | committed | 02:50 |
| 3 | 2,001 to 3,000 | failed | 03:15 |
| 4 to 7 | 3,001 to 7,000 | committed | 04:55 |
| 8 | 7,001 to 8,000 | failed | 05:20 |
| 9 to 10 | 8,001 to 10,000 | committed | 06:10 |
Partial commit needs 2 things the other strategies do not: the ledger, and writes that replace by chunk_id, so a chunk that half-wrote before failing can run again without doubling rows. The resumed run reads the ledger and processes only chunks 3 and 8, taking 50 minutes instead of 250. The price is consistency: until both chunks land, the partition holds 8,000 of 10,000 rows, and consumers must either accept that or wait for the ledger to show all 10 chunks committed.
A chunked job writes each chunk with a plain INSERT, then marks it committed in the ledger. Chunk 6's insert succeeds, then the job crashes before the ledger update. What does the resumed run do?
- Runs chunk 6 again and appends its 1,000 rows a second time
- Skips chunk 6, because its insert already succeeded
- Rolls chunk 6 back, then runs it once more
- Stops at chunk 6 with a duplicate-key error
The decision as a grid
| Consumers of the output | Rerun is cheap | Rerun is expensive |
|---|---|---|
| Act on totals: payments, ledgers, tax filings | All-or-nothing | Partial commit into staging; publish once every chunk commits |
| Tolerate a recorded gap: dashboards, exploration | Skip-and-quarantine | Partial commit with a quarantine for bad rows |
A nightly job builds daily_revenue_by_city, which finance uses to file city sales-tax remittances. The run takes 6 minutes. Last night 12 of 400,000 rows had a null city. Which strategy fits?
- Skip-and-quarantine, since 12 rows is far below any threshold
- Partial commit, keeping clean chunks and rerunning failed ones
- Load every row and file the 12 under a city named unknown
- All-or-nothing, blocking the publish until the 12 rows are fixed
About this lesson
This section is part of the Failure Handling: Intermediate lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.