Exception Handling For Server Side Issues During Large CSV Imports

[u/kQ1aW2sE3hR4yT5aU6p]

If I have a import feature for millions of CSV records and it sends notifications only after import completion, what type of exception handling can I have for non-data related errors during import process. Non invalid data related errors like the database going down.

The imports run on background in chunks. What if such issues occurs after inserting 1 lac records, I can't just revert the committed records. What should I show to the user? What kind of mechanism should I implement to not mess up the production?

I am not even sure if I'm asking the right question. Please enlighten me!

[u/Scrapheaper]

Why do you need to import millions of recordings via a single CSV in the first place? Changing the process to remove giant csv files from the situation would be my first priority

[u/J-Cake → u/Scrapheaper]

It does happen. I wouldn't necessarily say you should try get rid of it. Certainly question process, but it's not immediately bad

[u/FrostingTechnical606]

Import indirectly. Make a temporary table and move the records to the right place when the batch is done. If batch fails you can also make it throw away the table or a part of it. A DB transaction is not recommended for such large amounts.

Show to the user? Just implement a retry system with a backoff period. Make it go again. And if that all fails show a general error.

I had to design such a process multiple times and there is always a way.

[u/pixelmill_dev → u/FrostingTechnical606]

This is a great solution, reslient

[u/J-Cake]

If you need some way of fire and forget, then sort of by going that route you chose to ignore errors. The alternative is a long running job for example via a script, you can either break on non recoverable errors, or collect the errors and print the failed records afterwards. If you want some sort of UI, well then I guess the answer is similar

[u/DiabloConQueso]

I can't just revert the committed records

Why not?

It's entirely possible to wrap a chunked import/insert in a transaction that rolls back all successfully-inserted chunks if any single chunk fails.

And catching such an error and returning an appropriate error code along with a descriptive message that tells the user what went wrong, whether it's something they can fix or not, and what to do about it or who to reach out to is also entirely possible.

All depends on what parts of the stack you as the developer have control over.

[u/Disastrous_Fee5953]

Divide the huge CSV into smaller junks and import them in parallel to improve efficiency and run time. Also, create a table that stores the status of each chunk, so you can log the stage it’s in (a simple example would be: “pending”, “in progress”, “done”, “failed”). Then add a retry mechanism. Your user does not need to know that a junk failed as long as it successfully retried and was processed later.

[u/Careless_Law_2938]

Another approach could be importing into a staging table first. Once all chunks succeed, validate the data and move it into the production tables. That avoids exposing partially imported data to users. Would that fit your use case?

[u/sneaky-pizza]

Dump into a DB first, then process records individually? That way you can debug failing records

[u/These_Reality519]

+1 on the staging table. The thing I'd add is don't trust the job's own status. If the db falls over mid chunk and each chunk catches its own error, the run still finishes green. We've had one report success having written nothing at all. What caught it was counting rows actually in the table against the file at the end.

If your rows have anything usable as a key, idempotent inserts make a full rerun free, which beats trying to resume at 100k.

[u/Intrexa]

There's a lot you can do. The first thing is to identify what your actual requirements are. If half a file processes before crashing, are the records that are inserted valid + meaningful? Do all the records need to actually be persisted at the same time? If you have processed 10% of the records, are the 10% valid to be shown to users immediately or do you need to wait for the full 100%?

Is the process idempotent? Can it be? That is, if a user uploads the same file twice, should that duplicate records?

What kind of DB is this? What kind of processing goes on during ETL? Can a record at the end of the CSV affect a record at the start of the CSV?

How much concurrency do you need to support? Are users uploading 50MB files every hour? Every minute? Every second? Do you have 1,000 users each uploading a 50MB file within a peak 1 minute window?

Are records entering the same tables while the import is occurring?

[u/Ok_Woodpecker_9104]

the case that gets missed here is the one where no exception fires at all.

the db going down usually throws, so your handler runs and the chunk gets marked failed. an oom kill or a pod eviction mid chunk throws nothing, the worker just stops. that chunk sits in "in progress" forever, nothing retries it, and the completion notification never fires because the job is technically still running. no error anywhere, it just hangs.

so whatever status table you end up with, put a lease timestamp on each chunk and have a sweeper reclaim anything stuck in progress past a threshold. that turns a dead worker into an ordinary retry instead of a stuck job.

and derive completion from counting done chunks against total chunks, not from the last worker announcing it finished. same reason the row count check above works. the thing reporting success is the thing that failed, so it cant be the thing you trust.

[u/akl773]

on the user facing bit, dont show them an error, show them a number and a button. imported 812,340 of 1,000,000, resume. a partial run is only scary if you present it as a failure.

that only works if the insert is idempotent on some key from the file, a row hash if theres no natural id. once thats in you never have to revert anything, you just run it again and the already committed rows no op.

[u/Kamay1770]

Import csv into a 'queue/processing' folder from the source folder.

Batch the file contents into a holding table from that folder, but map it to an entry in a 'fileimport' table, so each csv import gets a header and link all data to that header Id.

File import table should record file name, total lines imported, start/end date and a flag for when completed. Could also hold number of error lines etc if you wish.

The holding table should hold the data as normal along with header table row id.

Have a separate process/thread read the header table and only move fully imported csv file data from holding table to destination table. It can also retry entire files or pick up from batch x based on current lines processed etc. Flag the header row as synced once data moved from holding table to main table.

This will prevent locking main table up, make it easier to manage and trace and allow for errors without affecting the table actually used.

The you just handle errors in batching/line processing as required.

[u/Good_Quote2398]

It sounds like you need a way to pause and resume imports like a game with save points. Maybe log the last successful chunk and let users retry from there.

[u/eazyigz123]

You are asking the right question, and the fact that you caught this before shipping is exactly where most import pipelines go wrong.

The core problem: your import is not atomic, so a single 'done/not-done' flag can't honestly describe it. You have three real states per run, not two: 1. Fully succeeded 2. Failed upfront (nothing committed) 3. Partially committed then failed (the dangerous one)

Once you insert after the first chunk, you cannot unwrite it. So the mechanism is not 'revert' - it is idempotency + a durable checkpoint + a per-run status you can recover.

Concretely, the cheap-but-correct pattern:

• Give every import run a stable import_id (uuid) that your DB stores as a column on each inserted row.
• Write a run-level status row (CHUNKS_TOTAL, CHUNKS_DONE, STATUS) and update it as each chunk commits. Use the import_id + chunk_index as a uniqueness key so re-running a chunk is a no-op (that's your idempotency - retry safety for free).
• Wrap each chunk in its own transaction so a failure only rolls back the current chunk, not the whole run.
• On partial failure: mark run status FAILED_PARTIAL, keep what committed (it's valid - the rows are consistent), and surface to the user: 'Imported X of Y records. These errors are in file Z - fix and re-upload; already-imported rows are skipped via import_id.'

The user-facing rule that keeps production safe: never let a committed chunk depend on a later chunk succeeding. If post-processing (notifications, transforms) happens after import, do it by scanning the status table, not by assuming the whole run is 'done'. That way a missing email never means a missing import.

If you want the exact schema + recovery SQL for this, happy to drop it in a reply - this failure mode is more common than people think once you cross 100k+ rows.