Designing Idempotent Import Jobs: A Practical Pattern for Safe Re-runs

Most teams eventually build an “import job” that pulls data from somewhere else: a partner feed, an internal export, a SaaS report, or a CSV someone emails every week. It usually works until it doesn’t.

A network timeout, a malformed row, a partial deploy, or a database restart can cut the job in half. Then the only practical question is: “Can I safely run it again?”

If the honest answer is “maybe, but it might duplicate records,” you are one incident away from manual cleanup. The goal of idempotent import jobs is simple: re-running the same import should produce the same final state, without double counting, data corruption, or mystery side effects.

Why imports fail (and why retries are normal)

Imports are where your system meets messy reality. Even if your code is correct, the environment is not always cooperative. Common failure points include:

• Transient failures: API rate limits, timeouts, DNS issues, short outages.
• Partial processing: the job crashes after writing some records but before marking the run complete.
• Bad inputs: missing required fields, changed schemas, weird characters, duplicate rows.
• Concurrency: two runs overlap due to a schedule hiccup or a manual rerun.
• Downstream side effects: import triggers emails, webhooks, or other jobs.

Retries are not a sign your system is unreliable. They are a standard tool for building reliability. The trick is making retries safe, predictable, and boring.

What “idempotent” means for import jobs

In this context, idempotent means: if you run the same import twice (same inputs, same intended operation), the second run does not change the final result. It might do work, but it should not create duplicates or drift.

There are two levels to think about:

1. Run-level idempotency: re-running the same file or API snapshot does not create extra records.
2. Item-level idempotency: re-processing a specific row/event updates the same target record in the same way.

Good import design typically focuses on item-level idempotency first, then adds run tracking so you can reason about progress, retries, and partial failures.

Choosing a good idempotency key

The core move is to pick an idempotency key that identifies “the thing being imported” in a stable way. Then your import writes become upserts (update if exists, otherwise insert) keyed by that identifier.

What makes a good key

• Stable: it should not change when a field like “name” changes.
• Unique: it should map to exactly one target record.
• Available on every row: missing keys force you into fragile fallbacks.
• Defined by the data source: best case is an upstream ID (customer_id, invoice_id, sku).

Common key choices

If the source provides a real ID, use it. If it does not, consider:

• Composite key: concatenate multiple fields that together identify the record (for example vendor_id + external_order_number).
• Content hash: hash normalized fields. Useful for “append-only facts,” but risky if fields change formatting.
• Run-scoped key: only when the record is intended to be unique within a specific run (for example line_number within a file), and you also store the run identifier.

A warning: avoid keys based on human-editable text (names, titles) unless you have no other choice. Those fields are prone to whitespace changes, corrections, and duplicates.

A minimal state model: runs, items, and checkpoints

Idempotency is easiest when you can answer two questions quickly:

1. What input did we process, and how far did we get?
2. For each item, did we already apply it to the target system?

You can get most of the benefit with a small amount of state stored in your database.

Run log: what happened, when, and with what input

Create a run record per import attempt. Give it a stable fingerprint of the input: a file checksum, a vendor “report ID,” or an API cursor range. Then store status and metrics. Conceptually:

{
  "import_run": {
    "run_id": "uuid",
    "source": "vendor_orders_csv",
    "input_fingerprint": "sha256:...",
    "status": "running|failed|completed",
    "started_at": "...",
    "finished_at": "...",
    "processed_count": 0,
    "error_count": 0
  },
  "import_item": {
    "item_key": "vendor:order:12345",
    "run_id": "uuid",
    "status": "applied|skipped|error",
    "last_error": "..."
  }
}

You do not need to store every payload forever. Often, storing item keys and a small error message is enough to support retries and debugging.

Row-level checkpoints: resume without guessing

If your import reads a large file or paginated API, add a checkpoint that lets you restart near where you left off. Options include:

• File offset or line number: effective for CSV, but be careful if input changes between runs.
• API cursor: store the last successfully processed cursor token.
• Timestamp watermark: import “all updates since X,” but only if the source provides reliable ordering.

Even with checkpoints, keep item-level idempotency. Checkpoints help performance and operator convenience, but idempotency is what makes reruns safe.

Real-world example: a nightly vendor CSV

Imagine a small e-commerce business receives a nightly CSV from a fulfillment vendor with columns: order_number, status, tracking, shipped_at. Your app needs to update each order’s shipping status.

Here is how an idempotent design looks in practice:

• Idempotency key: vendor_id + order_number. Store it as external_order_key on your internal order record.
• Upsert behavior: find the internal order by external_order_key. If it exists, update shipping fields. If not, create a “placeholder order” only if your business rules allow it, otherwise record an item error.
• Run fingerprint: compute a checksum of the CSV contents, or use a filename pattern if that is reliably unique per day.
• Side effects: do not send “your order shipped” emails directly in the import loop. Instead, write the updated state, then trigger a separate notification process that is itself idempotent (for example it records “notification sent at” per order).

Now the vendor re-sends the same CSV because their system had a hiccup. You run the import again. Your upsert logic finds the same orders and writes the same values, so nothing duplicates. If the first run crashed halfway, the second run fills in the remaining orders and re-writes the first half harmlessly.

Copyable checklist: make your import safe to re-run

• Define the unit of work: what is a single “item” (row, event, object)?
• Pick an idempotency key: stable, unique, always present. Prefer upstream IDs.
• Use upserts: insert-or-update keyed by the idempotency key (or a unique constraint that enforces it).
• Separate side effects: avoid sending emails/webhooks during the write loop, or make side effects idempotent too.
• Record runs: store status, timestamps, input fingerprint, basic counters.
• Record item outcomes: at least store “applied vs error” keyed by item key, so retries can skip or re-attempt intentionally.
• Make failures visible: include a human-readable error summary and where to look next.
• Handle concurrency: prevent overlapping runs for the same source, or ensure they cannot conflict.
• Plan for deletes: decide how “missing in source” should be treated (no-op, deactivate, or keep until explicit deletion event).

Common mistakes (and how to avoid them)

• Using “natural keys” that are not stable: matching customers by name + ZIP code feels convenient until someone changes their name or two people share it. Prefer source IDs.
• Appending instead of upserting: inserting a new row on every run is the fastest path to duplicates. Enforce uniqueness with a constraint on the idempotency key.
• Assuming the input is immutable: a “daily CSV” might be regenerated with corrections. Use a fingerprint and treat “same filename” as untrusted unless proven otherwise.
• Mixing processing and notification: “update order” plus “send email” inside the same loop creates double-email risks on retry. Decouple or dedupe with a per-order notification record.
• Skipping run metadata: without a run log, operators have no idea whether “it’s safe to rerun.” The cost is low, the payoff is high.

When NOT to use this pattern

Idempotent imports are a strong default, but there are cases where you should pick a different approach or add constraints:

• When you must preserve every historical version: if you are building an audit log or an event store, you might want append-only writes. In that case, idempotency shifts to “dedupe identical events by event_id” rather than “upsert current state.”
• When the source has no stable identifier and no safe composite key: you may need to negotiate a better feed, or treat the import as a one-time migration with manual reconciliation rather than an automated job.
• When updates are not commutative: if applying items in different orders produces different outcomes, you need stronger ordering guarantees (for example a sequence number) before retries are safe.

If you fall into one of these cases, you can still borrow parts of the pattern: run tracking, input fingerprinting, and side-effect separation remain valuable.

Conclusion

Reliable imports are not about preventing failure. They are about making failure recoverable. By choosing a stable idempotency key, writing with upserts, tracking runs, and separating side effects, you turn “rerun it and hope” into a controlled operational routine.

The best sign you got it right is simple: re-running an import becomes a normal tool, not a risky last resort.

FAQ

Is idempotency only for APIs, or does it apply to CSV imports too?

It applies to both. CSV imports often need idempotency even more because they are frequently re-sent, corrected, or partially processed.

Do I need to store every imported row to be idempotent?

No. Many systems only store the item key and the outcome (applied or error). The key is being able to uniquely identify the target record and safely upsert it.

What if the source changes an ID or reuses IDs?

That is a data contract problem. If IDs are not stable, treat them as unsafe keys and look for a different stable identifier (or push back on the provider). If neither exists, you may need manual review steps.

How do I prevent two import runs from stepping on each other?

Use a simple lock per source (for example “only one running run per source”), or ensure your writes are safe under concurrency via unique constraints and upserts. Run logs help you detect and resolve overlaps.