Deep diveImplementation
Data migration validation: proving completeness and correctness to the business and auditors
A migration is finished when someone accountable can show, with evidence, that the right records arrived with the right values, still linked to each other and usable by the business. This deep dive defines what correct means, then covers source profiling, versioned mapping specifications, four levels of reconciliation, handling rejects, and what belongs in the evidence pack for sign-off and audit.
On this page
- What a correct migration means: four properties and the terms behind them
- Profile the source data before writing any mapping
- What a mapping specification should record for each target field
- The four levels of migration reconciliation
- Full comparison or statistical sampling at Level 3
- Rejects and exceptions: fix at source, transform or exclude
- When auditors and regulators will look at migration evidence
- Contents of the migration evidence pack
- Hypothetical example: migrating a customer ledger
- Questions and answers
- Sources
What a correct migration means: four properties and the terms behind them
- Completeness
- Every record in scope arrived, and nothing out of scope did. Proved with counts and control totals by entity and segment.
- Accuracy
- Each migrated value equals the source value after the documented transformation. Proved by field-level comparison.
- Integrity
- Relationships survive: every invoice still points to its customer, every line to its header, every balance to its transactions.
- Usability
- The business can work with the data in the new system: processes run, reports agree and users recognize their records.
- Control total
- The sum of a numeric field, such as outstanding balance or quantity on hand, compared between source and target.
- Hash total
- A checksum over a set of values, often over fields that are not meaningful to add up, such as account numbers, used to detect any change.
Profile the source data before writing any mapping
Profiling means measuring what the source data actually contains: how often each field is empty, the distinct values and formats in use, codes missing from reference tables, duplicates, orphaned child records and dates outside sensible ranges. Run it at the start of discovery, not after mappings are written, because what it finds changes the mappings and creates cleansing decisions that only business owners can make.
Profile again before every migration rehearsal. Legacy data keeps changing while the project runs, and a rule that handled every case in spring can meet a new code value by autumn.
What a mapping specification should record for each target field
Keep the specification under version control beside the migration code, so every reconciliation report can name the version it tested.
The four levels of migration reconciliation
Each level catches errors the one before it cannot. Run all four in every rehearsal and in the final migration.
| Level | What is compared | Catches | Misses |
|---|---|---|---|
| Level 1: record counts | Records per entity, broken down by segment such as status, region or company code. | Dropped or duplicated records and wrong filters. | Wrong values in records that did arrive. |
| Level 2: control and hash totals | Sums of amounts and quantities, and checksums of key fields, per segment. | Truncated amounts, sign errors and altered identifiers. | Offsetting errors and wrong non-numeric fields. |
| Level 3: field-level comparison | Every field of every record, or a statistical sample, against the expected value. | Transformation and mapping errors. | Process-level problems with individually correct data. |
| Level 4: business-rule and process tests | Business rules and real processes run on migrated data, such as ageing reports or a billing run. | Broken relationships and data the new system cannot use. | Little, once the first three levels have passed. |
Full comparison or statistical sampling at Level 3
Where the comparison can be automated, compare every record: recompute the expected target value from the source using the mapping rules and diff it against what was loaded. Automation makes full comparison cheaper than arguing about sample sizes.
Sampling is for checks that need human judgment, such as reading migrated contracts or confirming that a customer record looks right on screen. Stratify the sample by segment, value and age so high-value and unusual records are always included, document the method, and agree it with the people who will rely on the result, including internal audit where relevant.
Rejects and exceptions: fix at source, transform or exclude
Every record that fails a load or a check needs a recorded disposition and an owner.
- If
The error is in the source and the source is still live.
ThenFix it at source, owned by the business, so the next rehearsal picks it up.
Corrections made only in migration code are lost if the source is reloaded.
- If
The error follows a systematic pattern, such as a legacy code with two meanings.
ThenAdd a transformation rule to the mapping specification, with the owner's sign-off.
- If
The record is obsolete and not needed for operations.
ThenExclude it, archive it under the retention schedule and record the exclusion.
- If
The record cannot be resolved before cutover but must exist.
ThenLoad it to a quarantine or suspense status with an owner and a deadline.
- If
The reject affects financial balances.
ThenDo not accept it without finance sign-off on the effect on the ledger.
When auditors and regulators will look at migration evidence
Sarbanes-Oxley Act, Section 404
United StatesApplies whenA public company migrates data in systems that support financial reporting2.
- Management assesses internal control over financial reporting, so migration controls and reconciliations become audit evidence.
21 CFR Part 11 (electronic records; electronic signatures)
United States (FDA)Applies whenLife-sciences firms migrate records required under FDA predicate rules3.
- Validated systems that can detect invalid or altered records, and accurate, complete copies of records.
BCBS 239, Principles for effective risk data aggregation and risk reporting
Global banking supervisionApplies whenBanks in scope migrate risk data or the systems that aggregate it4.
- Risk data that is accurate, complete and reconciled with sources, including accounting data.
GDPR (Regulation (EU) 2016/679)
European UnionApplies whenMigrated data includes personal data of people in the EU5.
- Keep personal data accurate, and do not migrate personal data you no longer have a reason to store.
Contents of the migration evidence pack
Hypothetical example: migrating a customer ledger
Questions and answers
Is data migration validation the same as data migration testing?
They overlap. Migration testing checks that scripts and tools work, usually during development. Validation proves that a specific migration run, ideally every rehearsal and the final run, produced complete and correct data, and it produces evidence for sign-off. Teams need both, but only validation results go into the evidence pack.
Should data be cleansed before or during migration?
Fix errors at source whenever the source is still in use, because those corrections help the business today and survive reloads. Use transformation rules for systematic patterns that cannot be fixed at source. Avoid manual corrections in staging tables between runs; they are hard to repeat and hard to evidence.
How do you reconcile when the target data model differs from the source?
Reconcile at the level where both models agree on meaning. If one source customer becomes an account and two contacts, count customers to accounts and compare control totals on balances, not row counts per table. The mapping specification defines these equivalences, which is why it must be explicit.
Who should sign off a data migration?
The business owner of each entity, not the project team, because they are accepting that the data is fit to run their processes. Finance signs for anything affecting balances. In ColdAI's implementation approach, discovery identifies risks and dependencies early1, which is also the moment to name these owners.
Sources
- Implementation: approach and offerings — ColdAI
- Sarbanes-Oxley Act of 2002 (Public Law 107-204) — U.S. Government Publishing Office · checked 10 October 2026
- 21 CFR Part 11: Electronic Records; Electronic Signatures — Electronic Code of Federal Regulations · checked 10 October 2026
- Principles for effective risk data aggregation and risk reporting (BCBS 239) — Basel Committee on Banking Supervision · checked 10 October 2026
- Regulation (EU) 2016/679 (General Data Protection Regulation) — EUR-Lex · checked 10 October 2026