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.

StrategyPartition this morningThe failuresRerun after the fix
All-or-nothingEmpty; the swap never ranRun blocked, owner pagedAll 10,000 rows
Skip-and-quarantine9,963 rows37 rows in `quarantine_driver_payouts`The 37 rows
Partial commit, 10 chunks of 1,0008,000 rows from 8 chunksChunks 3 and 8 marked failed in a run ledger2,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 NULL

The 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_idrowsstatusfinished_at
11 to 1,000committed02:25
21,001 to 2,000committed02:50
32,001 to 3,000failed03:15
4 to 73,001 to 7,000committed04:55
87,001 to 8,000failed05:20
9 to 108,001 to 10,000committed06: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?

The decision as a grid

Consumers of the outputRerun is cheapRerun is expensive
Act on totals: payments, ledgers, tax filingsAll-or-nothingPartial commit into staging; publish once every chunk commits
Tolerate a recorded gap: dashboards, explorationSkip-and-quarantinePartial 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?

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.