Data migration changes where information is held or served. Data transformation changes its representation, structure, or interpretation. A system replacement often requires both, but they need separate acceptance questions: did the intended population arrive, and does the resulting information still mean what the business intends?
For a program leader moving historical service records into a new platform, the key decision is which changes are necessary for relocation and which introduce new business meaning. Combining them into one undifferentiated “migration” workstream makes it easy to approve a technically successful load that has reclassified history incorrectly.
The distinction is practical rather than absolute. Converting a file format or mapping a field can be part of migration. A semantic change, such as splitting one legacy status into several new statuses, may require facts the source never recorded. The project must expose that uncertainty instead of treating it as a technical mapping problem.
Separate transport, representation, and meaning
Transport concerns moving an identified population from source to destination with appropriate security and completeness. Representation concerns encoding, data types, field structure, and relationships. Meaning concerns what the values assert about the business.
A date can be transported intact while being interpreted under the wrong time zone. A status can be represented in a valid target enumeration while asserting the wrong lifecycle state. A customer reference can point to an existing target record while identifying a different business party.
For each mapping, state which kind of change it performs and what evidence supports it. Simple renaming may need a schema test. A unit conversion needs a valid conversion rule and applicable scope. A historical reclassification needs an approved interpretation and sufficient source evidence.
Avoid calling all cleanup “transformation” as if that makes it harmless. Removing duplicates, filling missing values, or changing categories can affect downstream decisions. Some corrections are necessary, but their basis and consequences should remain visible.
A hypothetical service-status mapping
Suppose a hypothetical legacy system contains one thousand work orders: seven hundred marked Closed and three hundred marked Open. The new platform distinguishes Completed, Cancelled, and Open. In the legacy process, Closed was used for both completed and canceled work.
A simple mapping of Closed to Completed would load all one thousand records and preserve the apparent open-versus-closed count. It would also claim that seven hundred jobs were completed, which the legacy status alone cannot establish.
Further review finds evidence that five hundred of the closed records represent completed service and 150 represent cancellations. Fifty remain ambiguous. A defensible transformation therefore has five hundred Completed, 150 Cancelled, three hundred Open, and fifty unresolved records requiring a defined treatment.
The accepted target population may contain 950 records while fifty remain in an accountable exception store. That does not mean the fifty can disappear from the reconciliation. The project must explain the full one-thousand-record population and agree whether the unresolved cases block cutover, remain available through a legacy view, or receive another explicitly approved representation.
The unknown records should not be labeled Completed simply to achieve a clean load. Nor should they be labeled Cancelled because that seems conservative. The appropriate treatment depends on their intended use and the available facts. A target platform’s limited status list does not create missing historical knowledge.
All counts and status rules in this example are hypothetical. The important lesson is that equal source and target row counts can coexist with a materially wrong interpretation, while a controlled exception population can make an incomplete transformation honest and manageable.
Define a transformation contract for each consequential rule
A transformation contract is a working design record describing the source population, input fields, rule, output meaning, exclusions, assumptions, and responsible approver. It should also identify how an exception is detected and how a later correction will be applied.
For the status example, record the evidence that supports Completed or Cancelled, the precedence of conflicting evidence, and the treatment of missing information. Preserve the original status and source identifier so the target classification can be explained and challenged.
Version the rules. A later improvement to the mapping should not silently alter historical outputs without identifying which records changed and why. Where users need reproducibility, retain the source snapshot and rule version necessary to recreate the result.
W3C’s PROV-O model represents relationships among data entities, activities, and responsible agents. It provides a useful language for provenance, although adopting the ontology is optional and does not prove that a transformation is correct. W3C PROV-O, Starting Point Terms
Keep approved business rules distinct from implementation code. The rule should be understandable to the responsible domain owner; the code should be testable against it. Neither a spreadsheet mapping nor a script should become the only place where the intended meaning is known.
Test structure and semantics separately
Structural tests establish that required fields, types, keys, and relationships meet the target contract. They can detect invalid dates, missing references, duplicate identifiers, or truncated values. They cannot establish every business interpretation.
Semantic tests use independently specified cases. A canceled work order should remain distinguishable from completed service. A historical record should retain the appropriate effective context. A source value outside the known mapping should enter an exception route rather than default to a convenient category.
Test cardinality changes explicitly. Splitting one source record into several target records, combining several records, or flattening a hierarchy changes the meaning of row counts. Reconciliation should compare the relevant business objects and relationships, not demand identical counts where the approved transformation intentionally changes them.
Check joins and filters. A lookup that fails can drop records from an inner join; a one-to-many relationship can multiply them. A total can remain plausible even when particular records are omitted and others duplicated. Preserve record-level lineage and reconcile meaningful groups as well as aggregate counts.
For precision-changing transformations, define rounding, truncation, and loss of detail. Converting a timestamp to a date or a detailed classification to a broad category discards information. If the loss is intentional, document its purpose and preserve the original where required. If it is unintended, it is a defect rather than a formatting choice.
Reconcile the complete population of outcomes
A migration reconciliation should account for accepted records, rejected records, intentionally excluded records, and unresolved exceptions. The categories need clear definitions and must not overlap. A rejected load should remain traceable to its source record and reason.
For the hypothetical thousand work orders, a reconciliation showing only 950 accepted rows is incomplete. It must identify the fifty ambiguous cases and their approved disposition. If some records are intentionally excluded because they are outside scope, that is a different category from a failed transformation.
Use totals that match the data’s purpose. Counts by source status, target status, business period, and owning entity can reveal problems that a single grand total misses. Where amounts or quantities matter, compare them on a consistent basis and explain approved differences.
GAO’s data-reliability guidance considers accuracy, completeness, and applicability for the intended audit use. Its purpose-specific approach is a useful reference for choosing reconciliation evidence, while the migration’s acceptance policy remains the organization’s responsibility. GAO-20-283G, Assessing Data Reliability
Do not use the old system as an unquestioned oracle. Agreement with a known source error may be undesirable. When a correction is intentional, keep the original, the approved correction, and the reason so the difference is explainable rather than hidden.
Decide when to transform
Transforming before the move can let the existing business owners inspect corrections in familiar systems, but it may be constrained by legacy capabilities or create duplicate work. Transforming during the move can centralize the rules, but it concentrates uncertainty near cutover. Transforming after a faithful landing can preserve source evidence, but it requires access controls and clear separation between raw and approved data.
Choose the sequence according to reversibility, source stability, and the consequence of wrong interpretation. A faithful raw copy can support reconstruction, but it should not be presented as a ready-to-use business dataset merely because it is available in the new platform.
Separate unavoidable target adaptation from broader redesign. If the program changes identifiers, hierarchies, statuses, and historical classifications simultaneously, testing becomes harder because many causes can explain a difference. Stage changes where practical so their effects can be isolated.
There are limits to staging. The target may require a new structure at launch, or the source may be retired on a fixed date. In those cases, strengthen the rule evidence, exception handling, and reconciliation rather than pretending the additional risk is absent.
Make cutover depend on meaning as well as load success
Define which unresolved transformations block the intended use and who can accept a bounded limitation. A service team may need access to ambiguous historical records even if those records cannot participate in a particular performance measure. The decision should identify both allowed and prohibited uses.
Plan correction after launch. Preserve a route from a newly discovered source fact to an approved target update, with version history and downstream reconciliation. Avoid direct repair that loses the link to the original transformation and is later overwritten by a rerun.
Start with the three mappings most likely to change business meaning. For each, write the rule, construct difficult test cases, and reconcile every outcome category. A migration is ready when the organization can explain both where the records went and what the transformed records now assert.
