You are mapping data from a legacy system into a replacement and the two define a customer differently. How do you work that out?

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.