Six count checks pass, but only two targets reconcile
Every target in this experiment has six rows, matching the source. Only two satisfy the declared key and value comparison. The remaining targets contain a changed amount, a replaced identifier, a duplicate key or an invalid numeric representation. A row-count check accepts all six because it cannot see those differences.
The executed reconciliation script compares fixed JSON snapshots. It uses a stable integer ID, exact integer cents and a status field whose whitespace and letter case may be normalized under an explicit contract. It does not connect to a database, observe a CDC stream or certify a production cutover.
The complete fixtures include the six source rows and all six target variants. That small input makes each discrepancy inspectable. The purpose is to define what a comparison proves before a migration team turns a green count check into a release decision.
State the comparison contract before comparing values
A reconciliation needs a stable way to identify corresponding records. In this fixture, ID 3 must match ID 3 regardless of row order. If the target generates different identifiers, the migration needs a verified mapping or another stable migration key. Sorting two unrelated lists and comparing their positions does not establish record identity.
The field contract matters just as much. Amounts are integer cents and must remain integers. The string "100" is rejected even though a permissive conversion could produce the number 100. Status values may be trimmed and lowercased because the declared contract treats those differences as equivalent. That permission does not extend to arbitrary fields.
A real transformation may legitimately split records, change identifiers or reshape a schema. Compare the source's intended transformed representation against the target, with the transformation itself reviewed. If the same defective transformation produces both the migrated data and the expected comparison output, agreement can conceal the shared defect. Include independently specified examples of the business meaning.
The fixture's normalized-status case passes because that equivalence is intentional. The numeric-string case fails because its representation violates the chosen type contract. Neither outcome proves a universal rule about whitespace or numeric strings. It demonstrates why normalization must be part of the specification rather than an improvised way to make differences disappear.
Separate the failure classes instead of reporting one percentage
The script indexes records by key and records duplicates before comparing the surviving keyed values. It then reports missing source keys, unexpected target keys, changed values and invalid records. The duplicate list remains a failure even if a later row overwrites an earlier value in the temporary map.
| Target fixture | Row count matches | Keyed outcome | Specific difference |
|---|---|---|---|
| Exact copy | Yes | Reconciled | None |
| Changed amount | Yes | Failed | ID 3 has 301 cents instead of 300 |
| Replaced key | Yes | Failed | ID 6 missing, ID 7 unexpected |
| Duplicate key | Yes | Failed | ID 5 repeated, ID 6 missing |
| Normalized status | Yes | Reconciled | Permitted trim and lowercase only |
| Numeric string | Yes | Failed | ID 1 violates the integer amount contract |
The results file exposes all of those lists. An invalid row may also appear as missing from the validated index. Those categories are diagnostic evidence, not disjoint populations to add into a percentage. Count unique affected records separately if a dashboard needs that measure.
Do not discard duplicate evidence just because a dictionary or upsert operation can choose one winner. A migration may have lost a record and duplicated another while preserving the total count. In this example, replacing the sixth row with a second copy of ID 5 makes exactly that failure visible.
A hash can accelerate comparison when its inputs and collision assumptions are understood, but a matching aggregate hash is not a substitute for defining identity, types and snapshot boundaries. This fixture uses direct comparison so its proof does not depend on a hash implementation or an unexplained checksum contract.
Compare states that represent the same migration point
Fixed snapshots avoid a problem that a live migration cannot ignore: the source may change while validation runs. A difference can indicate an actual migration defect, replication lag or two observations taken at different logical points. Equal counts at one instant do not resolve that ambiguity.
AWS DMS data-validation documentation describes row-level source-to-target comparison and validation states, including constraints around primary keys and ongoing changes. Inspect unresolved or suspended validation rather than treating the absence of a listed mismatch as success. The documentation also notes resource costs; validation is work the migration must plan for.
Define the snapshot or change-stream boundary being compared. For a cutover, identify which source commits must already be represented, how in-flight changes are drained or accounted for and whether target-side writes can interfere with the comparison. Keep replication-progress evidence separate from the actual record comparison.
When evaluating a modernization engagement, Pharos Production's legacy modernization with database migration and validation is relevant to this boundary. Its service page discusses database migration, dual-write validation and parallel operation of old and new systems. Use that scope to request a concrete reconciliation contract and cutover evidence, including unresolved differences, before accepting the migration work. The page does not establish that any provider ran this article's fixture.
Make cutover evidence explain the unresolved records
A migration review should be able to name the expected population, its stable keys, approved transformations and comparison point. For each discrepancy, assign a disposition backed by evidence: corrected data, an accepted transformation difference or an unresolved blocker. An unexplained exception list is not the same as a reconciled target.
Run the downloadable script with python3 experiment.py to verify the local results. Then adapt the failure classes into tests for the actual schema and transformation. Include duplicate keys and invalid representations even when the database normally enforces constraints; the export, staging or transformation path may encounter those cases before final insertion.
The expand-and-contract migration article addresses application compatibility during schema change. Reconciliation answers a separate question: whether the intended records and values arrived at the chosen target boundary. Both forms of evidence can be necessary, but one cannot stand in for the other.
In this fixture, six matching counts coexist with four failed targets. The useful release evidence is the comparison contract and explained differences, not the count equality by itself.
Sources
Documentation checked .
Dreamtsoft Editorial
The duplicate-key case belongs in a migration review because a map can retain one value while concealing that another source record never arrived.
Dreamtsoft Editorial
For a target with new identifiers, which mapping establishes record identity? Equal positions in sorted exports leave that unresolved. The comparison needs to show that both rows represent the same business record.
Dreamtsoft Editorial
Trimming status text is allowed by this fixture's contract. Applying the same normalization to every field would introduce a different contract, which needs its own review.
Dreamtsoft Editorial
The discrepancy categories can overlap. I would inspect the affected record identities first. One record may appear in more than one diagnostic list. Adding the counts could therefore misstate the affected population. A failure percentage needs a defined set of distinct records.