An import you cannot reverse is an import you should not run

Sooner or later somebody brings a spreadsheet of history into a live system. A register kept for years, a ledger from a previous tool, a list somebody maintained by hand.

It is one of the most useful things you can do — the history becomes searchable, connected and usable. It is also one of the few operations that can quietly corrupt a working system in a way nobody notices for months, and the difference between the two outcomes is decided entirely before the write.

The two properties that make it safe#

Idempotent. Running it twice writes nothing the second time. This is what lets you run it, check it, and run it again after a fix without producing duplicates.

Reversible. Every row it wrote can be identified and only those rows. This is what lets you undo it if the check afterwards finds something wrong.

Both come from the same mechanism: tag every imported row with a marker that identifies the import and the source row. Ours was a short prefix plus the source document's own reference. That single field makes the second run write nothing, and makes the reversal an exact selection rather than a query somebody constructs afterwards from dates and hope.

Add it before the write or you will not add it at all, because after the write the rows are indistinguishable from everything else.

Reconcile before you write#

The rule we hold to hardest here: a parse that cannot reproduce the total it claims is not a parse anybody should import.

When we brought eight months of a concrete register into our own system, the verification was that the parsed rows had to reproduce three figures exactly — the row count, the total volume, and the total value — against the figures the register itself claimed.

They did not, at first. Two rows were missing: one with a blank serial number and one whose serial had been typed as a letter. Together they accounted for the discrepancy. Both were hunted down before a single row was written.

That check is worth more than any amount of spot-inspection afterwards, for a simple reason: a spot check confirms that the rows you looked at are right. A total confirms that the rows you did not look at are there.

If your source has no total to reconcile against, compute one from the source before parsing, by a different method, and reconcile to that. The point is two independent routes to the same number.

Decide the overlap deliberately#

The most dangerous part of any historical import is where the old records overlap the period the live system was already recording.

We had exactly this. Fifty-two rows in the register covered dates the live system had already been capturing through a different route. The register held one set of figures for those days; the system held another, smaller set.

They describe the same concrete, counted differently. Importing on top would have double-counted on a live ledger.

So those rows were not imported, and the decision was stated rather than guessed — it was escalated as a judgement for the owner rather than resolved by whichever rule happened to be easiest to implement.

The general shape: an overlap is not a technical problem with a correct answer. It is a question about which record is authoritative for that period, and that question belongs to a person. What the import must do is make the overlap visible before it runs, rather than silently applying a preference.

Preserve what the source knew#

An import is an opportunity that comes once, and the most common regret is having dropped fields because the target had nowhere to put them.

Our register carried things the live system had never held: who received each delivery, the document number, the rate including tax, the workability reading, and the seven- and twenty-eight-day test results where they had been taken. Those were exactly the fields that turned the imported rows from a volume total into evidence — for the reasons set out in the concrete cube test, a result with nothing to attach it to is a number with no owner.

If the target has no column, add one. Discarding a field at import time is permanent in practice, because nobody re-runs a historical import to recover a column they decided was unimportant.

Set the state honestly#

Imported rows land in some state, and the default is usually wrong.

Ours were marked as received, because the register is the proof of receipt — every row names the officer who took delivery. Left in the default state they would have shown on the way, did it land? for ever, on hundreds of rows, about concrete poured months earlier.

That would not have been a cosmetic problem. A screen full of permanently unanswerable questions is a screen people stop reading, and it would have taken the genuine current items down with it. A check that can never be satisfied is worse than no check — the same trap described in money before material, where a warning that was always on had become wallpaper.

Back up the specific table, and say so#

Before the write, back up what the write touches. Not the whole database as a matter of ceremony — the specific table, so the restore is a real option rather than a theoretical one.

And record, in the change log, that it was done. Somebody looking at this a year from now needs to know the backup exists and where.

The order that works#

1. Parse, and reconcile to a total the source itself claims.
2. Resolve the rows that will not parse, individually, by hand.
3. Identify the overlap with existing data and put the decision to a person.
4. Add the marker field to every row that will be written.
5. Back up the target table.
6. Write, in a transaction.
7. Run it again and confirm it writes nothing.
8. Verify the totals in the live system against the source.

Steps one, three and seven are the ones people skip, and they are the three that decide whether the import is recoverable.

Migrations deserve the same discipline#

Everything above applies to schema migrations as well, with one addition: migrations should be additive and idempotent, and proven so by running them repeatedly against a throwaway copy before they go anywhere near production.

Ours are applied to production ahead of the code that needs them, in that order, always. Code that expects a column which does not exist yet fails immediately and visibly. A column that exists before the code needs it is harmless.

And where a migration cannot be made automatic, it is stated as a manual step in the release notes rather than assumed. An unstated manual step is a production incident with a delay fuse.

The short version#

Tag every row you write so a second run does nothing and a reversal is exact. Reconcile to a total the source itself claims, before writing anything.

Put the overlap in front of a person instead of resolving it by rule. Keep the fields the source knew and your system did not. Set the imported state to what is actually true, not to the default.

And back up the specific table you are touching, so that the way out is something you have rather than something you would have to invent.