How Should Large CSV Imports Handle Failures After Partial Progress?

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.

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.

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.

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.

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.