What a primary ID is, and why every reconciliation depends on it
Before you can compare two systems, you have to agree on what counts as the same row. That agreement is the primary ID — the field, or combination of fields, that identifies one record in both systems. Get it right and reconciliation is just arithmetic. Get it wrong and every number downstream is quietly suspect. This is the step people skip, and it's the one that bites hardest.
What is a primary ID in reconciliation?
A primary ID is the value you use to match a record in one system against the corresponding record in another. In a sales reconciliation it might be the order number; in inventory it might be SKU plus location; in a bank reconciliation it might be a transaction reference. The only requirement is that it means the same thing on both sides and points to exactly one record on each.
This is different from a database primary key. A database primary key is unique within one system. A reconciliation primary ID has to be unique and shared across two systems that were never designed to agree.
What makes a good primary ID?
- Unique on both sides. One value points to at most one record in each system. If
order_idappears twice in the export, it is not yet a usable key. - Stable over time. The value does not change after the record is created. Status fields and computed totals make poor keys.
- Present in both systems. A perfect key that only one side records cannot match anything.
- Same format on both sides, or normalizable to it.
1001,#1001, andORD-1001are the same order to a human and three different keys to a computer.
When one field is not enough: composite keys
Many retail records are only unique in combination. A single SKU is not unique across warehouses; a SKU is unique per location. A line item is unique per order plus line number. When no single field identifies a row, you build a composite key by concatenating fields in a fixed order, for example sku | location_id.
What breaks when the primary ID is wrong
| Symptom | Likely cause |
|---|---|
| Everything shows as a mismatch | Key format differs between sides (leading zeros, prefixes, casing) |
| Zero matches on a clean dataset | Wrong field chosen, or key missing on one side |
| Duplicate matches, inflated counts | Key is not unique; one row matches many |
| Random rows unmatched | Whitespace, encoding, or type differences (text 1001 vs number 1001) |
How to validate a primary ID before you trust it
- Count rows and count distinct key values on each side. If they differ, the key is not unique there.
- Check the overlap: how many keys appear on both sides versus only one.
- Normalize formats — trim whitespace, strip prefixes, fix casing and leading zeros — then re-check.
- Only then run the comparison.
The discipline is simple: prove the key is unique and shared before you compare a single value. Reconciliation done on a bad key produces confident, wrong answers. Choosing it well is step one of the manual reconciliation method.
Frequently asked questions
What is the difference between a primary key and a primary ID?
A database primary key guarantees uniqueness inside one system. A reconciliation primary ID is the value you use to match records across two systems. A primary key can serve as a primary ID only if both systems store the same value.
Can a primary ID be more than one field?
Yes. When no single field is unique, you combine fields into a composite key — for example SKU plus location, or order number plus line number — joined in a fixed order with a consistent separator.
Why does my reconciliation show everything as a mismatch?
Most often the key formats differ between systems: leading zeros, prefixes like ORD-, casing, or a text value on one side and a number on the other. Normalize both sides to the same format and re-run before assuming the data itself is wrong.