Most migration pain is mapping pain. The engineering of moving bytes from one database to another is well understood. What breaks migrations is the semantic layer above it: deciding exactly how each source concept becomes a target concept, in what order, and what to do when the source contains things the target has no room for. A source-to-target mapping that survives contact with reality is explicit about all of this before the first row moves, rather than discovering it during the cutover window at two in the morning.
A good mapping is a contract. For every target field it names where the value comes from, how it is transformed, and what happens to the cases that do not fit. Written down, that contract can be reviewed, tested, and rehearsed. Left implicit in migration scripts, it becomes a set of assumptions nobody validated until they failed.
Semantic mappings, not column copies
The unit of a durable migration is the semantic mapping, which describes how a target field derives from source data. Some mappings are direct copies. Many are not. A target field might combine two source columns, apply a unit conversion, or select among sources based on a condition. Treating every field as a potential transformation, and documenting the actual rule, prevents the quiet corruption that happens when a copy was really supposed to be a computation.
The mapping also has to state its assumptions about the source. If a mapping assumes a source column is never null, that assumption is part of the contract and can be checked against the real data before the run. Migrations fail when their mappings silently assume a cleaner source than the one that actually exists.
Translating enums and lifecycle states
Enumerations and lifecycle states are where mappings earn their keep, because the source and target rarely share the same vocabulary. The old system might have five order states and the new one seven, with a many-to-one collapse in one place and a one-to-many split in another. Every source value needs an explicit destination, and every target value needs to be reachable. The dangerous cases are the ones with no clean answer: a source state the target cannot represent, or a target state that requires information the source never captured.
- Enumerate every source value and assign each an explicit target, with no silent default.
- Decide collapses and splits deliberately, since a many-to-one loses distinction and a one-to-many needs a rule to choose.
- Flag source values with no valid target as blockers rather than mapping them to a nearest guess.
- Confirm every target state is reachable, so the new model is not carrying states the migration can never produce.
Restructuring: one-to-many and many-to-one
Schemas rarely map row for row. A source table often splits into several target tables, or several source tables merge into one. A one-to-many restructuring, such as splitting a wide customer record into a customer and separate address and contact tables, requires deciding how to distribute and key the pieces. A many-to-one restructuring, such as merging regional tables into a single unified table, requires reconciling overlapping identifiers and deduplicating entities that existed separately in the source. Both change the grain of the data, and grain changes are where counts and keys most easily go wrong.
The mapping has to make the new grain explicit and preserve identity across the restructuring, so that relationships still resolve after the shape changes. This is also where derived fields tend to appear, because a merged or split model often needs values that neither source table stored directly.
Derived fields and dependency-aware load order
Derived fields are target values computed from source inputs rather than copied: a status inferred from several columns, a total summed from line items, a normalised category. Each derived field needs its rule written down and, ideally, an independent way to check it after the load. Derivations also create dependencies, because a field derived from another table cannot be computed until that table is present.
Those dependencies dictate load order. Parents must load before children so that references resolve, lookups before the rows that point at them, and base tables before the derived aggregates that read them. A dependency-aware load order, computed from the relationships in the mapping rather than guessed, is what prevents the cascade of foreign-key and null failures that turns a migration run into a night of manual reordering.
A migration plan is only as good as its worst unhandled case. The mapping’s job is to make every hard case explicit before the run, not to discover it during one.
Blockers and rehearsal
An honest mapping surfaces blockers: the cases it genuinely cannot resolve, such as a source value with no valid target, a required target field with no available source, or a duplicate identity that the merge cannot safely reconcile. Naming these as blockers, and refusing to paper over them with a nearest-guess default, is what keeps a migration from silently corrupting the very records that were hardest to move. A blocker is a decision escalated to a human, which is exactly where the ambiguous cases belong.
Finally, the mapping should be rehearsed before it is trusted. A rehearsal runs the full mapping against real or representative data in a safe environment, then reconciles the result semantically, checking state distributions, derived values, and balances rather than only row counts. Rehearsal is where the assumptions in the contract meet the messiness of the actual source, and it is far cheaper to discover a bad enum mapping in rehearsal than in production.
In controlled, synthetic validation, building the mapping as an explicit contract with enumerated state translations, dependency-derived load order, and named blockers, then rehearsing it with semantic reconciliation, caught restructuring and derivation errors before any real cutover. The general lesson holds for any migration: the mapping is the migration. Everything else is plumbing.