A data job that runs for six hours will, sooner or later, be interrupted. The laptop sleeps, the VPN drops, a site returns an error page at row 4,812, or someone presses Ctrl+C by accident. If the job has to start again from row one, every interruption costs hours and often creates duplicates.

Most of my long-running client jobs use one of two simple patterns to avoid that. Neither needs a framework. This article shows both, taken from real projects, and what I would tighten today.

Pattern 1: a “done” column in the spreadsheet itself

A client gave me an Excel list of a few thousand companies and asked me to fill in website, phone, contact person and annual revenue from two public business directories. The input was already a spreadsheet, and the output had to be the same spreadsheet with more columns filled in.

The simplest way to make that resumable is to let the spreadsheet remember its own progress. One column (here, AB) is empty until a row is finished. The loop skips any row that already has a value there, and the workbook is saved after every row:

wb = openpyxl.load_workbook(excel_file_name)
sheet = wb["Sheet1"]

for i in range(2, sheet.max_row + 1):
    if sheet[f"AB{i}"].value is not None:      # finished on an earlier run
        continue

    query = str(sheet[f"B{i}"].value)
    zip_code = str(sheet[f"K{i}"].value)

    result = lookup_with_retries(query, zip_code)   # two sources, up to 3 tries

    write_result(sheet, i, result)                   # columns P..AA
    sheet[f"AB{i}"] = "Yes"
    wb.save(excel_file_name)                         # progress survives a crash

Restarting the script after a crash simply continues from the first row without a “Yes”. There is no separate state file to lose or get out of sync.

Two other details made the output trustworthy. Each lookup only accepted a directory result whose postcode matched the row’s ZIP code, so two companies with the same name in different towns were not confused. And the lookup tried both directories, up to three times with a pause, and only gave up when every source came back empty.

What I would tighten today

  • Save atomically. If the process dies in the middle of wb.save(), the file can be corrupted. Save to file.tmp and then rename it over the original; a rename is atomic on the same disk.
  • Distinguish “not found” from “failed”. Marking a row “Yes” when both sources returned nothing hides the difference between “this company has no listing” and “the site was down”. A status column with found, not_found and error lets you re-run only the errors.
  • Catch specific exceptions. A bare except: also swallows Ctrl+C and real bugs. Catch network and parsing errors explicitly and let anything else stop the run loudly.
  • Save in small batches for very large sheets. Saving an Excel file after every row is fine for thousands of rows; for hundreds of thousands, save every 20 to 50 rows and accept re-doing at most one batch.

Pattern 2: a database collection as a work queue

The second pattern came from a migration job. A construction company wanted every project document, photo and video moved out of a construction-management platform into their own SharePoint (later also Google Drive), keeping the folder structure. That meant tens of thousands of files, slow downloads, and an upload API that occasionally failed.

The job was split into two stages connected by a MongoDB collection. Stage one walks the platform’s folders and downloads each file. Before downloading, it checks whether the file’s path is already recorded; after downloading, it records the file with is_uploaded: False. The file path is the document’s _id, so the database never records the same file twice (the destination is a separate question, below):

def download_file(url, path, job_id, media_type, title):
    local_path = path.replace(BASE_DIR, "")
    if files.find_one({"_id": local_path}):
        return                                   # already downloaded on a previous run

    stream_to_disk(url, path)
    files.update_one(
        {"_id": local_path},
        {"$set": {"file_path": local_path, "job_id": job_id,
                  "file_type": MEDIA_TYPES.get(media_type, ""),
                  "file_title": title, "is_uploaded": False}},
        upsert=True,
    )

Stage two only reads from the queue. It takes every file that is not yet uploaded, recreates the folder path on the destination, uploads the file and flips the flag:

for entry in files.find({"is_uploaded": False}):
    try:
        folder = ensure_folder_path(entry["file_path"])   # create missing folders
        sharepoint.upload_file(entry["file_path"], folder)
        files.update_one({"_id": entry["_id"]}, {"$set": {"is_uploaded": True}})
    except UploadError as e:
        log.warning("upload failed, will retry next run: %s (%s)", entry["_id"], e)

Because the two stages only communicate through the collection, each one can be stopped, restarted or run on a different machine. A failed upload simply stays False and is picked up on the next run. Adding Google Drive later meant writing one more stage-two uploader, not touching the downloader at all.

The crash window, and the honest guarantee

Look closely at stage two: it uploads the file, and then sets is_uploaded: True. If the process dies in between (the upload succeeded but the flag was never written), the next run sees False and uploads the same file again. The local _id stops the database recording a file twice, but it does not stop a second copy landing on SharePoint. This pattern is at-least-once, not exactly-once, and it is worth being precise about that rather than claiming a guarantee the code does not give.

Whether that matters depends on the destination. If a repeated upload overwrites the same path, a duplicate just wastes bandwidth. If it creates report (1).pdf next to report.pdf, you have a real problem. The fixes, in order of how much I trust them:

  • Make the destination idempotent. Upload to a deterministic path and have the API overwrite, or check “does this exact file already exist there?” before uploading. Then a repeat is harmless, and exactly-once stops mattering.
  • Reconcile instead of trusting the flag. At the end, list the destination and compare it to the collection; re-upload only what is genuinely missing, and flag anything unexpected.
  • Record intent before acting. Set upload_started_at before the upload and is_uploaded after. A file that has a start time but no completion is the exact set to check by hand or reconcile, not blindly retry.

Why a database instead of a list in memory?

  • Progress survives crashes, restarts and redeploys.
  • You can answer “how far along are we?” with one query, while the job is running.
  • Several workers can share the queue. For that, claim items atomically with find_one_and_update({"is_uploaded": False, "claimed": None}, {"$set": {"claimed": worker_id, "claimed_at": now}}) so two workers never take the same file. Give the claim an expiry, too: if a worker crashes mid-upload, its claim must time out and return the item to the queue, or that file is stuck forever.

Choosing between them

Item Done column Database queue
Best for Enriching an existing spreadsheet Multi-stage jobs, large volumes, several workers
Extra infrastructure None A database (SQLite is enough for one machine)
Client can see progress Yes, by opening the file With a query or a small dashboard
Main risk Corrupting the file mid-save A duplicate upload if a crash lands between the upload and the flag (make the destination idempotent)

Both patterns follow the same rule: write down that a unit of work is finished, in durable storage, immediately after finishing it, and make “already done” the first thing the job checks.

If you want to see the behaviour rather than read about it, the resilient job runner in the Lab simulates exactly this: kill the process halfway and watch it resume from its checkpoint. Its writes are keyed by record ID, so a resumed run overwrites rather than duplicates. It is the same idempotent-destination idea as above, which is what makes “no duplicates” actually true.

Try it: kill a job and resume it

The runner below models both patterns from this article: a checkpoint after every record, and writes keyed by record ID. Turn up the failure rate, kill the process halfway, then resume.

Simulation. No real requests are made. The runner models how my production jobs behave: timeouts, rate limits, permanent errors, crashes and resuming from a checkpoint. Outcomes are random, so every run is different.

Job · export 240 records

idle
0done
0retries
0failed
0.0selapsed

Conditions


done working retrying failed

Log

checkpoint: none