AngoraTek FR
← Back to Blog
Data Engineering 7 min read

A Crash at 3 a.m. Should Never Cost You a Full Re-run

At 3:04 in the morning the pager fires: the trips job, six hours into a seven-hour run, has died on an out-of-memory error. The fix takes eleven minutes. The recovery takes seven hours, because the only plan anyone wrote down says "delete the output and start over", and the 9:00 a.m. report arrives empty while the re-run is still grinding through city three.

Our claim is easy to argue with: a pipeline that needs a seven-hour replay after one bad hour is not unlucky, it is unfinished. Resumability is a property of the pipeline's shape, decided when the first write is written and nearly impossible to retrofit at the moment you actually need it. This post follows one worked example, the nightly job that turns a day of raw GPS pings into a trips table, and it ends where the interesting decision lives: in the write, not in the retry.

The whole-job replay deserves one honest hearing

Start with the defense of the obvious answer, because it wins often enough that refusing it is also a smell. A transform that runs in twenty minutes, reads a fixed input, writes a fixed output, and touches nobody's inbox can simply fail and simply be run again. Delete, re-run, done. Checkpoints are not free either: an idempotent design has to be maintained and occasionally explained, and a team that built checkpointing into a twenty-minute job bought complexity for a problem it did not have.

The trips job fails that test on a single number: seven hours per replay. The cost compounds quietly during development, long before any real 3 a.m. Every bug fix requires a replay, so the team learns to batch fixes into "big runs" and a one-line change ships to production as an untested bundle, because testing it alone costs a working day. The replay tax does not only slow the recovery after a crash. It slows every correction that comes before one, and that is the argument for changing the design, and it arrives weeks before the pager does.

Resuming at file boundaries only moves the lie

The first fix everyone reaches for is skipping files already processed. A staging directory holds one file per ingestion hour, and the job records each completed file in a manifest, so a restart reads the manifest and starts after the last entry. It feels like resumability, and it dissolves on contact with the details.

A "complete" file is a claim, made by a marker written somewhere. The crash can land between the write of the file and the write of the marker, and the restart then skips an hour that is only half-written downstream. The marker can also lie in the other direction: a file marked complete on disk was truncated mid-write, and the manifest says nothing about its tail. And if two humans restart the job within the same hour, both read the same manifest, both start at the same offset, and the downstream now holds two interleaved copies of the same evening.

A better marker only tightens the same claim. What the design needs is fewer claims: if every write is safe to apply twice, the manifest becomes an optimization, a way to skip work, instead of the thing whose truth the run depends on. That inversion is the pivot, and everything we kept is downstream of it.

What won: the sink keeps the progress log

Two decisions carry the whole design, and both live at the write.

Decide identity at ingestion, from the input alone. A trip's key is derived from data: the device identifier and the start timestamp, or the identifier the vehicle's vendor already assigned. It must not depend on the run: not the auto-incremented counter, not the order in which records arrived. A key that changes between two runs is not a key; it is two different facts wearing one name.

Make the write idempotent, keyed on that identity. Our sink is a Postgres table, so the write is an upsert. Postgres has shipped ON CONFLICT since 9.5 arrived in 2016 and MERGE since version 15 in 2022; the example below uses the latter. The same contract exists across the engine choices we make for other workloads, from Spark's MERGE on Delta and Iceberg tables to the incremental merge strategy documented for dbt models, and the coordinate handling underneath such schemas is its own earlier argument.

MERGE INTO trips AS target
USING staged_trips AS source
  ON target.trip_id = source.trip_id
WHEN MATCHED THEN UPDATE
  SET started_at = source.started_at,
      distance_m = source.distance_m,
      vehicle_class = source.vehicle_class
WHEN NOT MATCHED THEN INSERT
  (trip_id, started_at, distance_m, vehicle_class)
  VALUES (source.trip_id, source.started_at, source.distance_m, source.vehicle_class);

Once every write is repeatable, progress is derivable instead of asserted. The hour the job may skip is the highest ingestion hour fully present in the sink, and one query against the output answers it, whatever the manifest claims. At 3:04 the restart does a single SELECT max(), skips the six confirmed hours, and finishes the seventh. The manifest still exists, but only to save work, never to be trusted alone.

This is also why identity precedes idempotency in the order of the work. A MERGE keyed on a run-derived identifier leaves duplicate rows behind on every replay: each run honestly believed it was meeting a brand new fact.

A checkpoint stored outside the sink is a rumor

Where the checkpoint lives matters as much as how the writes behave. A checkpoint held in a job server's memory dies with the job server, and a Spark Structured Streaming checkpoint directory or a Kafka consumer offset only helps if it is committed with the output it describes. Kafka's transactional producer has existed for exactly this since version 0.11, released in 2017: offsets and the sink write land in one atomic transaction, so a restart cannot advance the offset past work it never wrote.

Anything that poisons the write poisons the resume. Wall-clock functions sit first: a row stamped now() differs between the crash and the replay, so the replay quietly rewrites rows it just wrote instead of meeting them unchanged. Random keys and unsorted source iteration are the same defect in different clothes. The rule is blunt: derive everything from the input, and make the replay a second reading of the same world rather than a new one.

The checkpoint table itself gets the durability of the output, because it is the output's table of contents. We keep a confirmed_batches row written in the same transaction as the batch it describes, which keeps one promise worth more than any dashboard: confirmed work is never lost and never double-applied.

What a restart design cannot fix

Side effects are outside the contract. A confirmation email, a webhook to a partner, a ticket opened in an operations queue: rows can be re-written, and an email cannot be un-sent. Our workaround moves anything with that shape into its own small job, which reads the confirmed output and fires effects once per fact, behind an idempotency key the receiving system can check. The big job resumes; the effects job replays in seconds.

Non-determinism in the world, not just in the code, is the second limit. A run that joins live data from two sources at runtime resolves differently after a restart, because one source was updated between the crash and the resume. Landing raw inputs first and deriving everything from the landing zone costs a copy, and it buys a replay that re-derives from the same bytes it read the first time. We pay it for anything long-running; for short pipelines we skip the landing zone and live with the smaller risk.

And idempotency is not exactly-once, however a vendor dresses it up. It is exactly-once-on-read: a replay will write the same fact twice, and readers who cannot tolerate seeing one fact twice need dedup logic or a transactional source. Naming that distinction in a design doc has settled more arguments than any framework comparison we have sat through.

Pick the replay budget per pipeline before the first transform is written: a job recoverable in half an hour is honestly and cheaply a re-run, and a job recoverable in seven hours is an idempotent write with a sink-side progress log, whether or not anyone put those words in the checklist. If delete-and-replay is still the recovery plan, price every hotfix as a full replay and let that number argue for you.

The cost of a crash is set at write time, not at retry time.

AngoraTek builds resumable data pipelines for small teams. If your recovery plan is still the delete key, bring us the pipeline and we will find the first place a checkpoint belongs.

Thanks for reading this far. If this sounds like the problem you're wrestling with, tell us about it. The team reads every message.

Start a conversation