Long-running data jobs should be designed to stop

If a data job only works when it runs start to finish without interruption, everything it depends on has to stay healthy the whole time. The database connection can't drop, credentials can't expire, nobody can redeploy, and nobody can find a bad source file halfway through. For a job that runs for hours, I wouldn't count on any of that. I'd assume it's going to get interrupted and decide up front how it stops and how it picks up again.

The first decision is the smallest unit the job can accept or reject, which might be one source file, one account, a page of records or a bounded ID range. "We processed 43 per cent" doesn't tell you which units actually committed. The unit needs a stable identity, so a page number that shifts when records get added is a poor restart key, while a source record ID, file checksum or explicit range holds up. I'd put one transaction around each unit rather than stretching one over the whole import, since long transactions hold locks and make recovery expensive.

A restart is going to revisit something, so decide what that means before it happens. A unique source key can stop a duplicate insert, an upsert on that key handles updates, and a multi-table import might need a small state machine (received, validated, written, reconciled). I wouldn't just skip every row that already exists, because an earlier attempt might have written only part of a record, or the source might contain a legitimate correction. A source version, content hash or import run ID lets you tell those apart. Side effects outside the database, like sending a message or calling another system, need their own idempotency key or an outbox step, since a rollback can't undo them.

An in-memory counter is fine for a progress bar and gone after a crash, so checkpoint state has to live in durable storage. For each unit I want to know:

• the stable unit ID, the import run and the source version
• when processing started and finished
• the final state (accepted, rejected or awaiting retry) and a short error code linked to fuller detail
• the code or schema version that interpreted the input

The restart query should read that record directly. Retries make sense for a connection timeout or a rate limit, and they just delay the rest of the file when a date is malformed, so classify errors where they happen. A validation failure goes to a rejection queue with the source ID and reason, a transient failure gets bounded retries with increasing delay, and an unknown exception stops that unit and raises an alert.

For a planned stop (a deployment, a wrong source, database maintenance), I'd use a cooperative stop over a hard kill: the worker finishes or rolls back its current transaction, records the checkpoint and exits without claiming new units, with timeouts on network calls so a stuck worker can't block shutdown. So a long import that stops halfway isn't a disaster in itself, provided it stopped at a unit boundary, the checkpoint says what committed and the restart doesn't duplicate work that was already accepted. Before trusting any of that, I'd interrupt a representative sample on purpose during validation, during a database write and after the write but before the completion marker, then restart each case and check the business records, checkpoint table, rejected items and external side effects. The last check is to submit a completed unit again: the data should stay logically unchanged, and the job record should say what it did.