The import produced 2,698 rows where I expected 2,800, and the difference was the whole point

The catalogue and pricing rules for a fabrication business were seeded from a single master workbook. The import ran without an error. It produced 2,698 rule mappings.

The source contained 1,400 mappings, each of which should expand into two — one for internal cost, one for the customer-facing price. That is 2,800. The import was 102 short and had reported nothing wrong.

A clean run is not evidence. It tells you no exception was raised, which is a statement about the importer’s error handling and not about the data. The only thing that tells you an import worked is a count-by-count reconciliation against the source, with every difference explained by a named cause.

What the reconciliation looked like

For each kind of thing, two numbers: what the source artefacts said should exist, and what a direct count in the live data returned.

Priced materials, 358 expected and 358 present. Labour and process items, 47 and 47. Suppliers, 39 and 39. Pricing rules, 349 plain plus 13 composite, and exactly 362 present, split the same way. Composite definitions, 13 and 13. Underneath those, 610 calculation steps and 310 material slots — no independent expected figure for those, so they were recorded as consistent rather than as verified, which is an honest distinction worth keeping.

Then the mappings, and the 102-row gap.

Explaining the gap rather than accepting it

The temptation with a small percentage discrepancy is to accept it. Ninety-six per cent of the rows arrived, the system works, move on.

That is how a data defect enters production with a plausible cover story. A shortfall has a cause. Either you can name it or you do not yet know whether the import is correct.

Here the cause was findable and specific. Twelve rule codes were named in the source mappings and never defined anywhere in the source — not in the rules sheet, not in the materials sheet, not in the services sheet. The importer correctly declined to create a mapping pointing at a rule that does not exist. Every mapping that referenced one of those twelve codes was skipped, and the number of such mappings, doubled for the two pricing roles, accounted for the shortfall.

So the gap was not an import defect. It was the import faithfully reporting a defect in the source, by refusing to fabricate the missing half of a reference.

There was a second, smaller number that confirmed it from the other direction. An intermediate artefact listed 369 rule codes ready to seed; the live data had 362. The difference was exactly the seven of the twelve undefined codes that had made it as far as that artefact. The other five never reached it. Two independent differences, both fully accounted for by the same twelve codes, and no unexplained residue in either direction.

That is the state you are trying to reach. Not zero differences — differences whose causes are enumerated and add up exactly.

Both directions, always

The reconciliation also asked the reverse question: are there rule codes in the live data that do not appear in the source at all.

There were none, and that check matters as much as the forward one. A forward-only reconciliation catches things that failed to arrive. It does not catch things that arrived from somewhere else — a partial earlier import that was never cleaned out, a manually created row, a test record. Those are the ones that later produce a price nobody can trace to a source document.

Set difference in both directions, or the reconciliation is half done.

The audit that ran before the import

The reason the gap was as small as twelve codes is that the source had already been mechanically audited, and this is the part I would repeat on any project of this shape.

Before seeding, every rule’s calculation chain was parsed and checked for internal consistency. It turned up 121 problems — steps referring to an intermediate working value that no earlier step had produced. A repair layer then produced proposed corrected expressions for around 120 rules, each with a guess at the parent rule the step belonged to, so a human could confirm rather than invent.

Only after that did an import-readiness pass run over every rule, and it marked none as unfit to seed. That was the go signal.

The order is the point. Audit the source, repair with confirmation, prove readiness, then import, then reconcile. Importing first and finding the problems afterwards means every problem you find is now mixed with the question of whether your importer caused it.

What to do with the gaps you cannot fix

Twelve rule codes could not be resolved by anyone but the business, because the only definition of them lived in an estimator’s head.

They were written down as source-data gaps, in a file, by code, with the note that they were referenced but never defined. They were not filled in with a plausible-looking calculation to make the numbers tie out.

That restraint is not fussiness. An invented pricing rule produces a wrong number silently for as long as it exists, and it looks exactly like a correct one. A missing pricing rule produces a visible failure the first time someone quotes that item, and the failure names itself. Between a loud absence and a quiet fabrication, take the loud absence every time.

The rule

An import is finished when you can produce a table with three columns — entity, expected from source, actual in the system — where every row either matches or has a written cause, and where the causes account for the differences exactly.

Run the set difference in both directions. Audit the source before you load it, not after. And never close a gap by writing data the source did not contain, however small the gap looks next to the total.