Start with a record, not a line number
A resumable CSV import needs a stable input, a durable record identity and a checkpoint committed with the work it describes. A count of lines read from a file cannot provide that contract. Quoted CSV fields can contain newlines, and a process can fail after writing a business row but before saving its progress.
Build the small importer below to see the boundary. It stages four CSV records, accepts three and rejects one invalid quantity. An injected exception rolls back a partially executed chunk. After reopening the SQLite database, the importer resumes from the last committed checkpoint and finishes with three result rows. Repeating the finished import adds nothing.
The example uses Python's standard library and a temporary local database. It demonstrates transaction behavior and an orderly reopen. It does not implement an upload server, tenant authentication, distributed workers or a power-loss test.
Freeze the input and validate its shape
- Download the importer source and save it as import_core.py.
- Run it with Python 3 in a directory where it can write its results. The script creates its fixture internally and uses an isolated temporary database.
- Inspect import-results.json, written beside the script. No existing customer database is opened.
The fixture has the exact header label,quantity. Its second record contains a quoted newline in the label. The parser returns that value as one record. Use the Python CSV reader with the declared dialect and newline handling, then validate the schema separately: strict CSV parsing does not turn a missing field or an invalid business value into your desired rejection policy.
This implementation checks the header exactly, rejects extra or missing fields as row errors, requires a nonempty label and requires a positive integer quantity. An unterminated quoted field rejects the whole file before any import is staged. Validly parsed records with bad values remain in staging with an error, so the final result can account for every record.
Input identity is (tenant, import ID, SHA-256 of raw bytes). Reusing the same tenant and import ID with different bytes is rejected. The in-memory byte string is both hashed and parsed, avoiding a gap in which a mutable file path could point to changed content. A production staging contract also needs to freeze encoding, parser version, mapping and validation rules.
Stage once, then review what will happen
Staging assigns record numbers after parsing, starting at one. The key (tenant, import ID, record number) identifies a record in this batch. It does not identify a customer or product across multiple imports. Uploading the same file under a different import ID remains a new batch under this example's policy.
Keep preview and application separate. A useful preview reports total parsed records, accepted records, rejected records and the reasons for rejection. It should also state whether applying the batch will insert, update or skip existing business objects. The demonstration writes accepted rows into an import-specific result table. It intentionally avoids choosing a customer-matching policy for your application.
The source stages the whole small file in one transaction. For large uploads, use bounded parsing and staging batches with a separate readiness marker. Do not let an application worker consume a half-staged file. Once staging is complete and validated, mark the version ready and resume business application from the staged records, without seeking into the original CSV stream.
Make the checkpoint part of the write
Each chunk begins a SQLite write transaction, reads the committed checkpoint and selects the next staged records in order. Accepted records are inserted into the result table. Rejected records are skipped but still count as examined records. The checkpoint advances to the last examined record in the same transaction as the inserts.
The central invariant is small enough to review directly:
begin transaction
read committed checkpoint
select the next staged records
write accepted results
advance checkpoint through all examined records
commitIf an exception interrupts the chunk, both its inserted rows and checkpoint movement roll back. The next run sees the earlier checkpoint and retries that chunk. SQLite's transaction documentation describes its single-writer behavior and explicit transaction modes. The source uses BEGIN IMMEDIATE before selecting work so two writers cannot independently read the same checkpoint and both proceed as owners. Busy handling remains an integration responsibility.
A unique result key supplies an additional invariant.
A uniqueness error is not the checkpoint protocol. The implementation expects normal resumption to select only records beyond the committed cursor. If applying a record triggers an external API call, that effect cannot roll back with SQLite. Persist an intent and use an appropriate outbox handoff.
Interrupt the second chunk and inspect the result
The fixture runs with a chunk size of two. After the first chunk, records one and two have been applied and the checkpoint is two. The second chunk encounters the rejected third record, inserts the fourth, then raises an injected exception before checkpoint commit. The transaction rolls back that insert.
| Checkpoint in the demonstration | Committed cursor | Applied result rows | Staged rejected records |
|---|---|---|---|
| First chunk committed | 2 | 2 | 1 |
| Second chunk rolled back | 2 | 2 | 1 |
| Database reopened and import resumed | 4 | 3 | 1 |
| Finished import repeated | 4 | 3 | 1 |
Check the saved output against this table. The script also asserts that changed input bytes are rejected, malformed quoting creates no new import, and another tenant cannot address this import through the core function. Tenant IDs in the demo are trusted fixtures. A real API must derive and authorize tenant context rather than accept it as proof of permission.
These checks exercise failure inside a transaction, not a crash after the client loses a successful commit response. Reopening after a completed chunk still illustrates why the committed cursor is authoritative. Infrastructure crash durability, concurrent worker scheduling and real target-table constraints need their own tests in the selected deployment environment.
Connect the core to an import job
Expose the importer through a durable asynchronous job contract. Separate upload completion, validation readiness, application progress and final outcome. A job can finish with rejected records if the product permits partial acceptance. Label that outcome clearly and provide a stable error report. Corrected bytes require a new input version or a new import ID.
Before applying real customer data, define cancellation at a chunk boundary, import authorization, duplicate business-key behavior and the retention of staged data. Bound file size, field length and staging resource use. Preserve the same mapping rules across a resume, even when the application has deployed a newer parser.
The successful handoff is inspectable: the operator can see which input version ran, how many records were examined, which records were rejected and where to resume. That contract remains useful when the local demonstration becomes a queue-backed SaaS import service.
Sources
Documentation checked .