At the end of a data migration, someone runs a query on the source, runs the same query on the target, and confirms that both return the same number of rows. Relief follows, the migration is declared successful, and everyone moves on. Row-count parity is a real check, but it is the weakest one available, and treating it as sufficient is how migrations pass their acceptance test and then fail in production. A row count confirms that records arrived. It says nothing about whether they still mean the same thing.
Meaning is exactly what migrations put at risk. Moving data between systems almost always involves transformation: mapping one set of status codes to another, recomputing derived values, splitting or merging tables, changing types and units. Each transformation is an opportunity for the count to stay identical while the content quietly diverges. The rows are all there. Some of them now say the wrong thing.
The failures that row counts cannot see
Consider a few migrations that pass a row-count check and still corrupt the business. An order-status field is mapped from an old enumeration to a new one, and one value is mapped incorrectly, so ten thousand cancelled orders arrive in the target labelled as active. The row count matches perfectly. A balance column is recomputed during the load using a subtly wrong rule, so every account has the right existence but the wrong number. The count matches. A boolean flag inverts because the source used one convention and the target another. The count matches. In each case the migration looks complete and is, in fact, broken.
- State and enum mistranslation, where old codes map to the wrong new codes.
- Derived fields recomputed with a rule that does not match the source semantics.
- Balances and totals that fail to reconcile even though every row is present.
- Sign, unit, or timezone conventions that flip meaning while preserving structure.
- Nulls that become defaults, or defaults that become nulls, changing what a field asserts.
Row counts test presence, not semantics
The reason row counts feel reassuring is that they are easy and they catch the crudest failure, which is losing or duplicating records. That failure mode is worth catching. But presence is a low bar. A migration can preserve presence perfectly while destroying the semantics that made the data useful, and semantics are what the business actually depends on. Nobody runs a company on the number of rows in a table. They run it on what those rows say about orders, balances, and customer states.
Row counts prove the data arrived. They cannot prove it still means what it meant. Meaning has to be reconciled on its own terms.
Semantic reconciliation is the real check
The check that actually protects a migration is semantic reconciliation: comparing the meaning of the source and the target, field family by field family, according to the transformation that was supposed to happen. For a state field, that means confirming the distribution of states matches after applying the intended mapping, not that the raw values match. For a derived field, it means recomputing the value from source inputs under the migration’s rule and confirming the target agrees. For balances, it means reconciling totals and per-entity amounts so that money in equals money out.
Semantic reconciliation is harder than counting rows because it requires knowing what each field means and how it was supposed to be transformed. That knowledge is the point. A migration is a set of promises about how source concepts become target concepts, and reconciliation is the act of testing each promise against the delivered data rather than assuming the code kept it.
Reconciling states, enums, and derived fields
For enumerations and lifecycle states, the strong check is a distribution comparison across the intended mapping. If the source had a known count of cancelled orders, the target should have that same count under whatever value now represents cancelled, and every source value should account for its share of the target. A mismatch in that distribution is a mistranslation, and it is invisible to a row count. For derived fields, the strong check is independent recomputation: derive the value again from source inputs and compare, so that a wrong formula is caught by disagreement rather than trusted by assumption.
Balances deserve special attention because they compound. A per-row balance error might look small in isolation, but reconciling the aggregate exposes systematic drift that individual spot checks miss. Reconciling both the totals and a sample of per-entity balances catches both the broad rule error and the isolated bad record.
Making assurance the acceptance test
The practical shift is to make semantic reconciliation, not row-count parity, the gate a migration must pass. That means defining the checks in terms of the transformation contract, running them against source and target, and treating any semantic divergence as a blocker rather than a curiosity. Row counts stay in the suite as a cheap first filter, but they are the beginning of assurance, not the end of it.
In controlled, synthetic validation where migrations were deliberately seeded with mistranslated enums and miscomputed derived fields while preserving exact row counts, count-based checks reported success on every corrupted load, and only semantic reconciliation of states, derived values, and balances surfaced the injected faults. The conclusion is uncomfortable but clear. If your migration sign-off rests on matching row counts, you are testing the one property that failures are most likely to leave intact.