Source-to-Target Data Mapping: Guide and Template

A source-to-target data mapping document should answer 15 questions for every field: where the value starts, where it lands, what rule changes it, what happens when it is blank or invalid, who approves the decision, and how the result will be tested. If any of those answers live only in a meeting or a developer's memory, the migration is not ready to build.

This guide gives you the mapping structure, decision classes, worked example and review process. It covers field and value rules. For tables, objects, keys and relationships, use our schema mapping guide. For project stages and approval gates, use the data migration framework.

What is source-to-target data mapping?

Source-to-target data mapping connects each source field and value to a target field, a transformation, a lookup, a derived result, a default or an approved exclusion. The finished data mapping document becomes the shared specification for business owners, analysts, developers and testers.

It is more than a two-column crosswalk. customer_status may map to Account.Status, but that line does not explain whether A means Active, how blanks are treated, which records are rejected or what result a test should expect. A build-ready source target mapping records those decisions explicitly.

Mapping level	Decision it records	Example
Dataset	Which source population moves	Active customer records only
Record	Which rows qualify or merge	Exclude test accounts
Field	Which target receives the value	email_addr to Contact.Email
Value	How codes and formats change	A to Active
Exception	What happens when a rule fails	Quarantine malformed email
Test	How the team proves the rule	Expected output for sample record

What should a source-to-target mapping document contain?

Use one row per mapping decision. A spreadsheet works for a small, stable project. A repository-backed specification is safer when several teams edit rules, mappings are generated, or releases must remain traceable.

Column	What to record	Why it matters
1. Mapping ID	Stable rule identifier	Links requirements, code, defects and tests
2. Source system	Application, database or file	Prevents ambiguity across data sources
3. Source object	Table, worksheet, API object or file section	Defines record context
4. Source field	Exact column or property name	Makes the input executable
5. Source type	Type, length, precision and format	Exposes conversion risk
6. Source definition	Business meaning, not just the label	Prevents false name matches
7. Target object	Destination table or object	Establishes load location
8. Target field	Exact destination name	Makes the output testable
9. Target type	Required type, length and constraint	Detects truncation and precision loss
10. Mapping class	Direct, transformed, lookup, derived, default or excluded	Tells the build team what action is required
11. Transformation rule	Deterministic expression or decision table	Removes interpretation from implementation
12. Null and default rule	Action for blank, missing or invalid values	Stops hidden assumptions
13. Validation rule	Allowed range, format, uniqueness or relationship	Defines acceptance
14. Example and expected result	Input paired with output	Creates an immediate test case
15. Owner and status	Approver, version and decision state	Makes governance visible

Add sensitivity classification, lineage, retention or masking columns when regulated or personal information is involved. Do not force every project into the same template. Keep the 15 decision fields, then extend the document for the actual risk.

How do you create source-to-target mapping?

1. Freeze the population before mapping fields

Name the source systems, environments, extraction date, record filters and target release. A field mapping cannot be approved if the team disagrees about which records are moving.

Profile the real data source, not just its documentation. Compare null rates, distinct values, minimum and maximum lengths, invalid dates, duplicate identifiers and code distributions. Documentation may say a column contains ISO dates while production stores free text from an older import.

2. Inventory source and target fields

Export both field lists with names, descriptions, types, lengths, required flags and keys. Add sample values only after applying the project's security controls. For each source field, decide whether it is used, retained for audit, archived or excluded.

Do the reverse check too. Every required target column needs an input, a generated value or an approved default. This catches target fields that have no source before the first test load.

3. Classify every mapping rule

Mapping class	Use it when	Example
Direct	Meaning and format remain the same	first_name to FirstName
Transformed	Format or representation changes	MM/DD/YYYY to YYYY-MM-DD
Lookup	A reference table converts codes	IN to India
Derived	Several inputs produce one output	first name + last name to display name
Default	Target needs a controlled fallback	missing locale to approved default
Split	One source becomes several targets	address string to street, city and postal code
Merge	Several source records become one	duplicate customers to master customer
Excluded	Data must not move	obsolete internal note

Classification helps estimate work. A page with 300 direct mappings is different from one with 80 lookups, 40 derived values and a disputed merge rule.

4. Write transformation rules that can be executed and tested

Replace vague instructions such as “clean phone number” with an ordered rule:

1. Trim leading and trailing spaces.
2. Remove approved punctuation.
3. Apply the source country code when one is missing.
4. Validate length and allowed characters.
5. Write valid values in E.164 format.
6. Send failures to the named exception queue with a reason code.

The rule should produce the same result whether a developer reads it, a tester writes a case from it or an automated process executes it. If the result depends on judgment, name the decision owner and escalation path.

5. Define nulls, defaults and exceptions separately

Blank, missing, invalid and not applicable are different states. A blank source value might be accepted, rejected, enriched, given a default or held for review. Record the action and reason rather than letting the load process decide silently.

Defaults need business approval. A convenient fallback can change reporting, customer segmentation or compliance meaning. Preserve the original value and exception reason when traceability is required.

Source-to-target data mapping example

Assume an older CRM stores customer status as a one-letter code and the new system requires a named status.

Mapping ID	Source	Target	Class	Rule	Exception	Expected result	Owner
CRM-014	legacy_customer.status_cd	Account.Status	Lookup	A=Active, I=Inactive, H=On Hold	Unknown code to review queue	A becomes Active	Sales Operations
CRM-015	legacy_customer.email_addr	Contact.Email	Transformed	Trim, lowercase, validate format	Invalid email retained in exception file	ADA@EXAMPLE.COM becomes ada@example.com	CRM Owner
CRM-016	legacy_customer.created_dt	Account.CreatedDate	Transformed	Parse source timezone, store UTC	Invalid date blocks record	08/15/2026 14:30 CST becomes approved UTC value	Data Owner
CRM-017	legacy_customer.segment	Account.Segment	Default	Map known codes; blank to approved Unclassified	New code requires owner review	blank becomes Unclassified	Marketing Operations

Turn each row into positive, boundary, null and failure cases. CRM-014 needs tests for A, I, H, blank and an unknown code. CRM-015 needs a valid email, mixed case, spaces, malformed input and a missing value. The mapping document and test evidence should reference the same mapping ID.

How do keys and relationships affect field mapping?

Business keys identify the same entity across systems. Technical keys may be regenerated in the target database. Record how old identifiers connect to new identifiers, where the crosswalk is stored and how dependent records are loaded.

For example, contacts may need the new account ID before they can load. The source-to-target specification records the identifier lookup at field level, while the database schema mapping guide owns cardinality, foreign keys and load order.

Never validate only record counts. A migration can move every row and still attach orders to the wrong customers. Test uniqueness, referential integrity, orphan records and representative parent-child paths.

Excel template or mapping tool: which should you use?

Situation	Spreadsheet is usually enough	Managed specification is safer
Team	1 small working group	Several business and technical teams
Change rate	Stable source and target	Schemas and rules change during delivery
Mapping volume	Hundreds of straightforward rows	Thousands of rules or generated mappings
Governance	Simple approval	Audit trail, role controls and version history required
Execution	Manual handoff	Rules feed ETL, integration or test automation

The format matters less than synchronization. One approved version must connect mapping rules, implementation code and test results. If the spreadsheet changes after code is written, the change needs an owner, review and regression test.

How do you review and approve a data mapping document?

Use three reviews:

• Business review: definitions, exclusions, defaults and exceptions reflect real operating rules.
• Technical review: types, formats, dependencies and transformations can be implemented without hidden interpretation.
• Test review: each rule has representative inputs, expected outputs and acceptance criteria.

Mark rows as draft, reviewed, approved, built, tested or rejected. Do not use colour alone to communicate status. Record the approver, date, version and decision note.

Before build approval, confirm:

• Every in-scope source field has a decision.
• Every required target field has a source, derivation or approved default.
• Lookups have owners and controlled values.
• Null, invalid and duplicate cases have defined outcomes.
• Key crosswalks and dependent load rules are documented.
• Each executable rule has test data and an expected result.
• Sensitive values follow access, masking and retention controls.

Common source-to-target mapping failures

Matching by name instead of meaning. Two fields called status may represent account state and transaction state. Compare definitions and values.

Hiding decisions in code. If a default or exclusion exists only in an ETL script, business owners cannot approve it and testers may miss it.

Treating profiling as optional. Real values expose legacy codes, impossible dates and unexpected lengths that the schema does not show.

Ignoring reverse coverage. Mapping only source fields misses required target fields that have no valid input.

Losing version alignment. A current spreadsheet paired with old code and newer test cases creates false assurance.

FAQs about source-to-target data mapping

What is the difference between data mapping and schema mapping?

Data mapping defines how fields and values move, including transformations, defaults, lookups and exceptions. Schema mapping defines how tables, objects, types, keys and relationships correspond. They work together but require different decisions.

What should a source target mapping template include?

Include source and target context, types, business definitions, mapping class, transformation rule, null handling, validation, example input and output, owner, status and version. Add security and lineage fields when the data requires them.

Who owns source-to-target mapping?

Business owners approve meaning, defaults and exclusions. Technical owners confirm implementation constraints. Test owners confirm that every rule can be verified. One named mapping owner controls the approved version.

Can source-to-target mapping be automated?

Software can profile fields, suggest matches and execute approved mappings. People still need to resolve ambiguous meaning, new codes, defaults, exclusions and relationship changes. Suggested matches should carry confidence and review status.

How do you test a mapping document?

Create positive, boundary, null, invalid and relationship cases for each rule. Compare actual output with the expected result, reconcile totals where relevant and link defects back to the mapping ID.

What happens after the mapping is approved?

Developers build from the approved version, testers derive cases from the same IDs, and changes follow review and regression testing. The data migration checklist can track the evidence required before cutover.

If your team has the source export and target field list but the rules are still spread across meetings and code, Acelerar can scope the mapping, data conversion and QA work. Talk to our data migration team about the systems, record volume and acceptance criteria.