Settle the meaning and the grain before the field list
A mapping spreadsheet that starts with column names has already skipped the analysis. The
question to answer first is what one row means in each system, because a difference there
invalidates every field-level mapping underneath it. In the classic version of this problem, the
legacy CUSTOMER table has one row per billing arrangement, so a company with three depots is
three customers with three credit limits and three addresses. The new system models a legal
entity with many accounts beneath it. Nothing in the field names tells you that, and both sides
will happily agree that customer maps to customer.
So establish three things per entity, in writing, with the business rather than with a DBA. What
is one row — a person, a legal entity, an arrangement, an event? What makes two rows the same
thing, which is to say what is the natural key as the business understands it? And who owns the
definition when two departments disagree, because sales and credit control will not mean the same
thing by "active customer" and one of them has to be authoritative for this build.
A grain difference is not a mapping problem, it is a rules problem. Collapsing three legacy rows
into one entity with three accounts means deciding which of the three addresses becomes the
entity address, what happens to three different credit limits, and which of three
inconsistently-spelled names is the registered one. Those are business decisions with money
attached, and the analyst's job is to surface them as decisions rather than let a developer pick
MAX(id) and move on.
Profile the data before believing the documentation
The data dictionary describes the intent of the system at the time it was built. Fifteen years of
use is recorded only in the data itself, so run the profile early: distinct values and their
frequency per column, null rates, minimum and maximum, and the actual cardinality between the
tables you think are related. It takes a day and it reliably changes the design.
Three findings recur often enough to be predicted. Fields get overloaded — a NOTES column
carrying "DO NOT DISPATCH" for 900 accounts is a business rule with no other home, and if you
migrate it as free text the rule silently stops being enforced. Sentinel values stand in for
missing data, so a date of birth of 1 January 1900 on four thousand records is not four thousand
centenarians. And reference fields drift, so a status code list of six documented values turns
out to contain eleven, three of them lower-case duplicates.
Each of those needs a named decision, and the profile is what lets you ask for it with a volume
attached. "What should happen to the 900 accounts flagged in free text" is a question a business
owner can answer in a meeting. "The notes field is messy" is not.
What the mapping document has to contain
One row per target field, and the target side leads — you are filling a shape, not emptying a
box. Anything unmapped on the target side is a gap; anything unmapped on the source side is a
decision to leave data behind, which is also a decision somebody has to own.
| Target field | Source | Rule | If absent | Owner |
|---|
| entity.registered_name | CUSTOMER.NAME of the lowest account number in the group | Trim, collapse double spaces, keep case as held | Reject the group to the exception report | Credit control |
| entity.credit_limit | Sum of CUSTOMER.CRED_LIM across the group | Cap at 250,000 pending re-approval | Set 0 and flag for review | Credit control |
| account.opened_date | CUSTOMER.OPEN_DT | Values of 1900-01-01 treated as unknown | Use earliest transaction date, else null | Finance |
| account.dispatch_hold | Derived from NOTES containing DO NOT DISPATCH | Set true, and record the note text on the account | False | Operations |
The If absent column is the one that gets skipped and the one that decides how go-live feels.
Every field needs an answer among four options: derive it, default it, leave it null, or reject
the record to an exception queue that a human works through. Left unstated, the answer becomes
whatever the load script's author chose at two in the morning.
Reconciliation is part of the mapping, not a test afterwards
Design the counts before the load runs, and design them so a business person can verify them
without SQL. Record counts by entity and by status, sums of the money columns, and a
deliberately chosen sample of twenty awkward accounts that the business team looks at by hand on
day one. The awkward sample finds what the totals hide, because a mapping can balance perfectly
and still have put every group's address on the wrong account.
The hard part of a data migration is almost never the fields that map. It is the records where
the two models disagree about what one row means, and every one of those is a business decision
waiting for an owner.
© 2026 Preptima. Originally published at preptima.com (opens in a new tab).