Data & SQL
How to Write a Data Mapping Document: Source-to-Target Rules, Defaults and Testing
Part of our guide to business analyst roles and responsibilities.
What a data mapping document is and who uses it
A data mapping document tells the team exactly how data moves from a source system to a target system. For every target field it answers four questions: where does the value come from, what happens to it on the way, what if it is missing, and what if it does not fit. Developers build from it, testers write checks from it, and business owners sign off its rules.
You will meet it on two kinds of project. In a migration, you move historical data once from an old system to a new one. In an integration, two systems exchange data again and again, often through an API or a file. The document looks almost the same in both. Writing it is a core part of what a business analyst does on data-heavy projects, because the rules are business decisions, not just technical ones.
It is different from a data dictionary. A data dictionary describes the fields inside one system. A mapping connects two systems, one field mapping at a time. You usually need both.
The columns of a mapping sheet
Most teams keep the mapping in a spreadsheet or a wiki table, with one row per target field. That is easier to check than one row per source field, because every target field must be either filled or deliberately left empty. These columns earn their place:
- Mapping ID: a stable reference such as
M04, so tests, tickets and decisions can point to the row. - Source system, table, field, type and format: for example text in DD/MM/YYYY.
- Target system, table, field, type, length and required flag: this is where mismatches first show up.
- Transformation rule: what happens to the value, in plain words a tester can check.
- Default or null rule: what the target gets when the source is blank or invalid.
- Error handling: reject the record, load it with a default, or hold it for review.
- Owner, status and notes: who agreed the rule, whether it is draft, agreed or tested, and open questions.
Add a separate tab for value maps (code translation tables) and another for open issues, so each main row stays short.
How to write transformation and default rules
Most transformation rules fall into a few types:
- Direct copy: the value moves unchanged.
- Format change: dates, phone numbers, case, trimming spaces.
- Value map: a code in one system becomes a different code in the other.
- Split or merge: a full name becomes first and last name, or address lines become one.
- Derivation: a value calculated from other fields, such as an age band from a date of birth.
- Constant: a fixed value, such as a marker that shows a record was migrated.
Write each rule so two people would build and test it the same way. Convert date is not enough. Convert DD/MM/YYYY text to YYYY-MM-DD; if it is not a valid date, load null and log the record is testable.
A default is a business decision, because it creates data nobody entered. For each default, record why it is safe, who agreed it, and how the business will find the records that received it, for example through a count in the load report.
Handling mismatched values between systems
Mismatches are the hard part: a source code with no match in the target, free text going into a dropdown, a value that is too long, or two records with the same key. Start by profiling the source. Count the distinct values in every coded field and compare them with the target list, because old systems often hold codes that no manual mentions. Then choose a treatment for each mismatch:
- Translate: add the value to the value map once the business confirms its meaning.
- Default: load an agreed value and report how many records got it.
- Reject: send the record to an exceptions file with a reason.
- Clean at source: ask the business to fix the records before the final load.
Duplicate keys need an explicit rule too: keep the first, keep the latest, or reject every record that shares the key. Never let the developer choose silently. Record each choice and its approver in a decision log, linked to the mapping ID.
Diagram in words
- Fix by hand (top left): Clean at source before the final load; Data owner reviews each record
- Agree a rule, test hard (top right): Value map signed off by the data owner; Extra test cases and sample checks
- Default and log (bottom left): Load the agreed default; List affected records in the load report
- Automate the rule (bottom right): Transformation rule in the mapping sheet; Count reported after every load
How to write a data mapping document, step by step
- Agree scope. Name the source and target systems, the tables in scope, and whether this is a migration or an integration.
- Collect definitions. Get the dictionary or schema for both sides and a sample source extract.
- List every target field. One row each, including fields with no obvious source. Those gaps are your first questions.
- Find the source for each field. Match by meaning, not by name.
STATUScan mean different things in two systems. - Profile the source data. Count blanks, distinct values, lengths and duplicates.
- Write the rules. Transformation, default and error handling, in testable sentences.
- Review with owners. Walk through the sheet with the data owner, a developer and a tester.
- Test, refine and baseline. Run a trial load, reconcile, update the sheet, then control later changes.
Diagram in words
- 1. Agree scope: Name the systems, the tables and the type of project: migration or integration.
- 2. Gather definitions: Collect dictionaries, schemas and a sample extract from the source.
- 3. Map each target field: One row per target field, matched by meaning, with a stable mapping ID.
- 4. Write the rules: Transformation, default and error handling in testable plain words.
- 5. Review with owners: Data owner, developer and tester agree each rule and log decisions.
- 6. Trial load and reconcile: Compare counts, values and samples, then update the sheet and repeat.
How to test a data mapping
A mapping is a set of requirements, so test it like one. Testers can turn each row into test cases, much as they write test cases from acceptance criteria. Plan these checks:
- Rule tests: for each mapping ID, a normal value, a blank, an invalid value and an edge case.
- Row count reconciliation: source rows must equal loaded rows plus rejected rows.
- Value distribution: counts by code in source and target should agree once the value map is applied.
- Sample comparison: compare a set of records field by field.
- Defaults report: confirm the business expected the number of defaulted records.
Simple SQL covers most reconciliation. If you are new to it, start with SQL for business analysts. These queries use the tables from the example below.
SQL
-- 1. Row count reconciliation
SELECT COUNT(*) AS source_rows FROM legacy_customer;
SELECT COUNT(*) AS loaded_rows FROM crm_customer WHERE record_source = 'LegacyOMS';
SELECT COUNT(*) AS rejected_rows FROM migration_exceptions;
-- 2. Status codes outside the agreed value map
SELECT STATUS, COUNT(*) AS records
FROM legacy_customer
WHERE STATUS IS NULL OR STATUS NOT IN ('A', 'I', 'S')
GROUP BY STATUS;
-- 3. Customer numbers that appear more than once
SELECT CUST_NO, COUNT(*) AS records
FROM legacy_customer
GROUP BY CUST_NO
HAVING COUNT(*) > 1;Any row returned by the second or third query needs a decision. Keep the code lists in queries in step with the value map tab.
Worked example: migrating customer records at a fictional retailer
Marigold Home Supplies, a fictional retailer, is moving customer records from its old order system, LegacyOMS, into a new CRM. Nadia is the business analyst, Sunil the operations lead who owns the data, and Riya the tester. The source extract has 1,000 customer records. Rule M01 rejects every record whose customer number repeats, because nobody can yet say which copy is correct.
Mapping sheet extract for customer records
LegacyOMS to CRM: customer table
ID | Source field | Target field | Rule | Default or error | Required
M01 | CUST_NO | customer_id | Direct copy, must be unique | Reject every record whose number repeats | Yes1
M02 | CUST_NAME | first_name, last_name | Split on first space; if no space, all to last_name | first_name blank | Yes2
M03 | DOB (text, DD/MM/YYYY) | birth_date (date) | Convert to YYYY-MM-DD | Null and log if invalid | No3
M04 | STATUS | status | A=Active, I=Inactive, S=Suspended | Reject blank or unknown codes | Yes4
M05 | PREF_CHANNEL | contact_channel | E=Email, S=SMS, P=Post | Email if blank; reject unknown codes | Yes5
M06 | (none) | record_source | Constant value | LegacyOMS | Yes6
- Unique key. All records that share a number are rejected, not merged. Choosing which copy to keep needs a person, not a rule.
- Split rule with a fallback. The rule says what happens when there is no space, so the developer does not have to guess.
- Format change plus error path. Invalid dates load as null and are logged, because birth date is optional in the CRM.
- Value map. The full code list lives on the value map tab. Unknown codes are rejected so they cannot hide in the new system.
- Value map plus agreed default. Sunil approved Email for blanks only. Codes not on the map are rejected, and the load report counts both.
- Target-only field. A constant marks migrated records so they can be found and checked later.
Profiling shows every PREF_CHANNEL value is E, S, P or blank, so Nadia expects no rejections under M05. In the first trial load, 980 records load and 20 go to exceptions, so 98% load on the first run. The counts reconcile: 980 plus 20 equals 1,000.
Reviewing the first trial load
Marigold Home Supplies, a fictional retailer, the morning after the first trial load of customer records into the new CRM.
- Riya QA tester
Trial load done. 1,000 records in the source extract, 980 loaded and 20 in the exceptions file. The counts reconcile.
- Nadia Business analyst
Good. What is in the 20?
- Riya QA tester
12 have status code X, which is not in mapping row M04. The other 8 are 4 pairs that share a customer number, so M01 rejected both records in each pair. Nothing was rejected under M05.
- Sunil Operations lead
X is an old code. We used it for closed accounts before the last system upgrade. Treat those as Inactive.
- Nadia Business analyst
Then I will add X=Inactive to M04, log the decision with your name, and ask you to approve it. The 8 duplicates go to your team for manual review, not to a rule.
- Riya QA tester
I will rerun. I expect 992 loaded and 8 left in exceptions.
The people and the company in this scene are fictional.
After the rerun, 992 records load and 8 stay in exceptions: 992 plus 8 equals 1,000. The defaults report shows 150 of the 992 loaded records had a blank PREF_CHANNEL and received Email, and 0 were rejected for an unknown channel. Sunil confirms that matches the old system.
Riya also compares 20 loaded records field by field. 19 match exactly. In 1, a two-part first name was split wrongly by M02. Sunil keeps the rule, because customers can correct their name, and Nadia logs it as a known issue against M02.
Common mistakes in data mapping documents
- Mapping by field name. Same name can mean different things. Confirm meaning with the data owner.
- Vague rules. Clean up the phone number will be built three ways. Say exactly what clean means.
- Forgetting blanks and bad data. Every row needs a default or error rule.
- Copying coded fields as is. Codes need a value map and a rule for codes not on it.
- Letting developers decide mismatches. It is a business decision and needs an accountable owner.
- Profiling too late. Rules written from the manual often break on the first real extract.
- No version control. If the sheet changes after testing without review, the results prove nothing.
Checklist before you sign off a mapping
Data mapping sign-off checklist
- Every target field has a row, a source or constant, and a mapping ID.
- Every rule is written in plain words that a tester can check.
- Every row says what happens to blank, invalid and unknown values.
- Every coded field has a value map covering each distinct code found when profiling.
- Duplicate key rules say exactly which records are kept and which are rejected.
- Each default and mismatch decision has a named owner and a log entry.
- Trial load counts reconcile: source rows equal loaded plus rejected.
- Sample records have been compared field by field and differences resolved.
If any item is not ticked, record it as an open issue with an owner and a date rather than signing off with a quiet gap.
Frequently asked questions
Is a data mapping document the same as a source-to-target mapping?
In practice the terms mean the same thing. Some teams use source-to-target mapping for the spreadsheet and data mapping document for the full pack, including value maps, open issues and the decision log. Pick one name and keep to it.
Who should write the data mapping, the BA or the developer?
Often the business analyst drafts it and owns the business rules, while a developer adds technical details such as data types and load order. The data owner approves the rules. Each rule needs a named owner.
What tool should I use for a mapping document?
A spreadsheet is the most common choice because it is easy to sort and filter. Some teams use a wiki page or an integration tool. Use what your developers and testers already use, with a single current version.
How is data mapping different for an API integration?
The columns are the same, but the source or target is often a JSON message, so you map field paths inside the message. You also need rules for missing required fields and error responses. The API contract is your main source of definitions.
This article was written by an AI system and published automatically after automated accuracy, safety and quality checks. All companies, people and numbers in the examples are fictional. BA Mentorship does not issue professional certifications and cannot guarantee any job or interview outcome. Spot an error? Email our support team and we will check and correct it.