Data mapping document template: how I map a system migration field by field

Most data migrations don't fail on the big stuff. They fail on a field nobody thought about: a status code with no mapping, a date stored as text in the old system, a customer type that got split into two in the new one. By the time anyone notices, the data is live and the business is cleaning it up by hand.

The document that prevents most of this is boring and unglamorous: the data mapping document. As a business analyst, it's the one artifact I won't skip on any migration or integration project, however small. Here's how I build one, field by field, with a template you can copy.

What a data mapping document actually is

It's a table that answers one question for every field that moves: where does this come from, where does it go, and what happens to it on the way?

That sounds simple. The value is in being forced to answer it for every single field, including the ones everyone assumes are obvious. "Customer name maps to customer name" is where the trouble starts, because the old system has one name field and the new one has first, middle and last.

The columns I use

Here's the template. Each row is one target field.

A few of these columns earn their place more than people expect.

Example in / example out. This is the column that catches misunderstandings. When a stakeholder sees "03/11/2025" turn into March 11 instead of November 3, they speak up immediately. Abstract rules don't get that reaction. Concrete examples do.

Default if blank. Every required field needs an answer for what happens when the source is empty. "Reject the row," "use a default," and "load it and flag it" are all valid. Leaving it unanswered is not, because the developer will pick something, and it may not be what the business wants.

Owner. One named business person who can say yes. Not "the business." A person. Without an owner, open questions stay open until go-live week.

Status. Agreed, open question, or blocked. On a big migration, the count of open rows is the honest progress metric. Ignore anyone who says the mapping is "90% done" without that count.

How I build it, step by step

1. Start from the target, not the source

List every field in the target system first. Then go find a source for each. If you start from the source, you'll faithfully map a lot of fields nobody needs and miss target fields that are mandatory but have no obvious home.

2. Profile the source data before writing rules

Don't write a transformation rule from the data dictionary. Look at the actual data. A quick query tells you more than any spec:

SELECT TYPE_CD, COUNT(*) AS row_count
FROM CUST
GROUP BY TYPE_CD
ORDER BY row_count DESC;

The dictionary says TYPE_CD is R or W. The data says there are also 312 rows with D, 40 with a blank, and 6 with a lowercase r. Every one of those needs a rule.

3. Write rules a developer can't misread

"Clean up the phone number" is not a rule. This is:

Strip all non-digit characters. If 10 digits remain, format as (XXX) XXX-XXXX. If 11 digits and the first is 1, drop the 1 and format the same way. Otherwise load as-is and set phone_flag = 'CHECK'.

If two developers could implement your rule differently, rewrite it.

4. Review it with the people who use the data

Walk the business owners through the examples, not the rules. Ask: "If you saw this record in the new system, would it look right to you?" This meeting surfaces more problems than any technical review I've been in.

5. Turn every row into a test

Each mapping row becomes a test case in UAT: given this source value, I expect this target value. That's where the mapping pays for itself. Testers don't have to guess what correct looks like, because it's already written down, with examples.

The fields that cause the most trouble

From experience, these deserve extra time:

• Codes and statuses. Old and new systems almost never have the same list. Map every value, including the ones that "shouldn't exist."
• Dates stored as text. Format ambiguity (day first or month first) quietly corrupts data.
• Names and addresses. One field becoming many, or the reverse.
• Money and currency. Decimal places, currency codes, and whether tax is included.
• Anything with history. Does the new system get the current value only, or the full history? Decide explicitly.
• Keys and IDs. Will old IDs be kept, stored as a reference, or thrown away? Reports and integrations downstream depend on this answer.

Signs your mapping isn't ready

• Rows with no owner
• Transformation rules containing words like "appropriate," "clean," or "as needed"
• No example values
• No rule for blanks on required fields
• Open questions older than two weeks
• Nobody from the business has seen it

If you see any of these the week before a mock load, that's the risk to raise.

A short checklist before sign-off

• Every target field has a source or an explicit "no source, default to X"
• Every coded field has every source value mapped
• Every required field has a blank-handling rule
• Every row has an example in and out
• Every row has a named owner and status Agreed
• Every row is linked to a UAT test case

About me

I'm Prachit Parikh, a business analyst. I turn what stakeholders ask for into requirements a team can test, check the data with SQL, and see changes through UAT. I keep free templates, including a data mapping template, in my BA Toolkit on GitHub, and I make short animated explainers on YouTube. Besides Hashnode, I write on Substack, Medium, Dev.to, Blogger and WordPress, and I answer questions on Quora. You can also find me on LinkedIn, Bluesky, Kaggle, Credly, Crunchbase and Gravatar. Everything is collected on my website and my profile hub.

What's the worst mapping surprise you've hit on a migration? Tell me in the comments. I collect these.