How Should Large CSV Imports Handle Failures After Partial Progress?
Asked by
MellowCedar42
I'm building an import feature that may process millions of CSV records. The work runs in the background in chunks, and users are notified only when the import finishes. Data-validation errors are one case, but I'm more concerned about infrastructure failures such as the database becoming unavailable after 100,000 records have already been committed.
Since a single transaction for the entire file probably isn't practical, how should the import process handle partial progress, retries, and non-data-related failures? What should the user see if the job cannot complete, and what safeguards should I add so a failed import does not leave production data in an inconsistent state?
Answers
A giant CSV may be worth questioning from a workflow perspective, but it can still be supported. Split the file into smaller chunks, process them with bounded parallelism, and keep an audit record for the file, chunk, row counts, retries, and errors. For failures that cannot be recovered automatically, leave the job marked as failed and tell the user what happened and whether they should correct the file, retry the import, or contact an administrator.
Reply to this answer
If the business requirement is all-or-nothing visibility, load everything into temporary or staging tables first. Only after every chunk succeeds should you perform the final publish step, such as swapping tables, marking an import version active, or inserting the staged records into production in a controlled transaction. If partial results are acceptable, commit per chunk instead, but make that behavior explicit to the user and provide a way to inspect or clean up an incomplete import.
Reply to this answer
Make the import idempotent so restarting the job does not duplicate records. Use a stable key from the source data, or an import ID plus row identifier, and enforce the appropriate database constraint. Then a failed run can safely retry the last chunk or rerun the entire file. For each batch, commit successful work and keep enough metadata to know exactly what was processed.
Reply to this answer
Treat the import as a durable background job with explicit states such as pending, in progress, completed, and failed. Record the status and error for every chunk, retry temporary problems like database outages with exponential backoff, and stop on non-recoverable failures. The final job status should be based on what is actually present in the database, not merely on whether the worker reached the end without throwing an error. Show the user a clear summary: completed, partially completed, or failed, along with whether a retry is in progress.
Reply to this answer
Use a staging table rather than writing directly into production tables. Load the chunks there, track each chunk’s status, and validate the complete import before moving the data into its final destination. If a chunk fails, you can retry or discard just that staging data without exposing a half-finished import to users. A single transaction covering millions of rows is usually too expensive, so keep the transactions limited to manageable batches.
Reply to this answer
Add your answer
Answers are reviewed before publication.
Related Discussions