A data migration is complete only when the target data reconciles to an approved source boundary and every material difference is explained. A green import message proves that a loader finished; it does not prove record completeness, correct values, preserved relationships or usable business history.
Design control totals before building transformations. Reconcile at extraction, transformation, load and cutover. Keep rejected and intentionally excluded records visible, owned and approved. The checklist below treats migration as an evidence chain rather than a sequence of files.
Define the migration boundary and authority
List the source systems, datasets, dates and business objects in scope. For each object, name the authoritative source, data owner and target purpose. State what will not migrate—such as expired history, duplicate drafts or records retained only in an archive—and how users will access it afterward.
Freeze definitions before counting. "Active customer", "open order" and "attachment" must mean the same thing to the source query, reconciliation and target acceptance.
Profile before designing transformations
Measure record counts, nulls, formats, value distributions, duplicates, invalid references, date ranges, attachment types and volumes. Sample records that look ordinary and records at each extreme. Compare the observed data with policy and user expectations.
The UK Government Data Quality Framework treats quality as fit for purpose and encourages understanding dimensions, root causes and ownership. Use profiling to decide whether a problem should be corrected at source, transformed under an approved rule, accepted as an exception or excluded.
Create a field and relationship map
| Mapping element | Required decision |
|---|---|
| Source | table/file, field, type, meaning and owner |
| Target | object, field, type, allowed values and required status |
| Rule | copy, convert, derive, split, combine, default or exclude |
| Relationship | parent, child, lookup, many-to-many and sequence |
| Exception | condition, queue, owner, deadline and allowed disposition |
| Test | control total, sample or business scenario proving the result |
Do not hide a business decision inside code. A default value can change reporting or compliance meaning; the data owner must approve it.
Design control totals before the first test load
Use several kinds of control because counts alone miss corruption:
- records by object, status, date band and business unit;
- financial or quantity sums where meaningful;
- minimum, maximum and distribution checks;
- unique identifiers and duplicate counts;
- parent-child and lookup relationship counts;
- attachments by type and total bytes;
- hash or checksum tests for files where appropriate;
- required-field and valid-value percentages.
Set tolerances only when the business meaning supports them. A zero tolerance may apply to open balances; an approved exclusion list may explain archived records. A generic "99% migrated" target can conceal the one percent that matters most.
Keep a transformation ledger
Version every rule with its rationale, approver, implementation and test. Record source and target handling for time zones, character encoding, decimals, identifiers, deleted records and historical status. Make non-reversible changes visible.
When a rule changes, identify which test loads and evidence are invalidated. Do not quietly edit transformation code and compare it with an old baseline.
Reconcile at four checkpoints
- Extraction: the staged source matches the approved query and snapshot.
- Transformation: source minus approved exclusions plus documented derivations equals the transformed set.
- Load: accepted target plus rejects equals the transformed input; relationships and totals remain correct.
- Cutover: changes since the rehearsal are captured, loaded and reconciled before business release.
At each checkpoint, store the query or method, time, result, exception list and sign-off. NIST SP 800-53 includes controls concerned with information integrity; the practical migration implication is to detect unauthorised or unintended change rather than trusting movement alone.
Make exceptions a managed dataset
Every rejected, unmatched or ambiguous record needs an identifier, reason, severity, owner and disposition: fix source, transform, create target manually, archive, exclude with approval or defer under a controlled plan. Preserve the link to the original record and rule.
Prioritise by business impact, not by easiest volume. Five orphaned high-value contracts can matter more than thousands of old contact records.
Test business use, not only database equality
Ask representative users to find, open and act on migrated records. Run scenarios involving search, permissions, history, reporting, corrections and downstream integration. Verify that dates, units, names and relationships make sense in the target interface.
Database-level equality cannot prove that a user sees the right customer history or that a report includes the correct status. Combine technical reconciliation with business acceptance.
Control migration access and evidence
Migration extracts often concentrate sensitive data outside the normal application controls. Inventory each copy, location, encryption method, access group, transfer route and destruction date. Separate duties where a single operator could alter both data and reconciliation evidence without review.
Protect logs and control totals from silent change. Record who generated and approved them. Remove temporary extracts and credentials after acceptance, then obtain evidence of disposition. The migration workspace should not become an unofficial long-term archive.
Rehearse cutover with timings and fallback
Run at least one full-volume rehearsal using production-like constraints. Measure extraction, transformation, load, reconciliation and exception resolution. Identify the last safe point to abandon cutover and the evidence required to continue.
The rollback plan should state whether the source remains authoritative, how target writes are prevented or reversed, how users return to the old process and how data created during the window is handled. Test the communications and decision route as well as the scripts.
Approve release through explicit gates
| Gate | Minimum evidence | Owner |
|---|---|---|
| Data ready | profile complete; mapping and exclusions approved | data owners |
| Technical ready | repeatable load; controls and monitoring pass | migration lead |
| Business ready | critical scenarios and reports accepted | process owners |
| Cutover ready | timing, delta, fallback and communications rehearsed | release owner |
| Close | final reconciliation, exceptions and archive disposition approved | accountable sponsor |
Use a final pre-cutover challenge
Ask a reviewer who did not build the migration to reproduce one control total, trace one transformed field, inspect one rejected record, follow one parent-child relationship and explain the rollback decision time. Sample a high-risk object and an ordinary one.
If evidence depends on one person's memory or a manual spreadsheet with unclear formulas, the cutover is not ready. The challenge is not a ceremonial audit; it tests whether the migration can be understood and controlled under pressure.
Retain the evidence after go-live
Store source snapshot identifiers, mappings, rule versions, control totals, exceptions, approvals and final reconciliation under retention and access rules. Monitor early operation for issues that migration tests could not reveal, but distinguish them from later user or integration changes.
Migration confidence comes from reconstructability. Another reviewer should be able to explain what moved, what changed, what did not move and why every material difference was accepted.