How Should I Handle Failures During a Large Background CSV Import?

Make each batch independently retryable and idempotent. Store a durable import record with the file identifier, batch ranges, attempt count, and current status. If the database goes down, mark the batch as interrupted and retry it later. If a batch repeatedly fails, show the import as partially failed or failed and explain whether the user should correct data, retry the job, or contact an administrator.

Import into a staging table first rather than writing directly to production tables. Track each batch as pending, running, completed, or failed, and retry transient failures with exponential backoff. Once every batch has succeeded, validate the staged data and move or merge it into the production tables. That way, users don’t see a partially completed import.

A simpler alternative is to split the file into smaller jobs and process them independently, possibly in parallel. Keep the original file and enough metadata to resume or rerun individual pieces. The user-facing status can be something like queued, processing, completed, completed with failures, or failed, with a summary of successful and unsuccessful records.

You can use transactions per chunk, but a transaction covering millions of rows is usually expensive and difficult to operate. If the requirement is all-or-nothing visibility, staging is safer. If partial completion is acceptable, commit successful chunks and report exactly how many were processed and which ones failed.

Don’t rely only on the worker’s final status. A process can report success after losing its database connection or writing fewer rows than expected. Reconcile the result afterward by comparing expected and inserted counts, using a unique import or record key. Idempotent inserts also make a full rerun safe instead of creating duplicates.