How to create a data mapping document — worked example

How to create a data mapping document — with a real worked example

A data mapping document is a promise someone else implements: every target field, where its value comes from, and what happens when nothing fits. Most guides describe the document. This one shows a real row from one, because the difference between a reviewable mapping and a spreadsheet of guesses lives in the row.

What the document is — and what it is not

A data mapping document (also called a source-to-target mapping, STTM, or crosswalk) lists every field the target system needs and states, for each one: the source field or rule that feeds it, the transformation if any, and the evidence for believing the pairing. It is a design artifact — the input to whoever writes the migration or integration code, and the record a reviewer signs. It is not the pipeline itself, and it is not a data dictionary: a dictionary describes one system, a mapping commits to a relationship between two.

The columns that matter

Column sets vary by team; these are the ones that keep the document honest. (The free template ships exactly this layout as an ungated .xlsx.)

Column	What it holds
Target field / object	One row per target field the destination system needs — the document is complete when every in-scope target field has a row, not when the source runs out of columns.
Source field / object	Where the value comes from. Deliberately blank when nothing fits — a blank here with a question attached is a finding, not a failure.
Mapping type	direct, transformation, lookup, conditional, constant, generated — or unknown. “Generated” matters: fields the target system produces itself are things NOT to migrate.
Transformation rule / expression	The actual rule, precise enough to implement: truncations, concatenations, polarity flips, code-table lookups.
Evidence	The verbatim line from the source or target specification the pairing rests on. This is what makes a row checkable rather than merely plausible.
Assumptions & open questions	What must be true for the row to hold, and what nobody has decided yet. The uncomfortable column, and the most valuable one in review.
Status & reviewer decision	proposed → accepted / edited / rejected / deferred, with who decided. A mapping nobody reviewed is a draft, whatever the file name says.

A real populated row

From a real run mapping QuickBooks Online’s customer entity onto Dynamics 365 Business Central’s customer table — published unedited here:

Target: Contact · Source: Title, GivenName, MiddleName, FamilyName, Suffix · Type: transformation

COPYSTR(COALESCE(NULLIF(TRIM(CONCAT_WS(' ', Title, GivenName, MiddleName, FamilyName, Suffix)), ''), DisplayName), 1, 100)

Evidence: “If not supplied, the system generates DisplayName by concatenating customer name components supplied in the request from the following list: Title, GivenName, MiddleName, FamilyName, and Suffix.” — Intuit’s QuickBooks Online API reference

Assumption: blank name components are omitted; organization-only records fall back to DisplayName; result capped at Business Central’s 100-character field length.

Notice what the evidence column does: a reviewer can confirm the concatenation order came from the vendor’s own document in one read, without trusting whoever drafted the row. And here is the other kind of row the document must be able to hold — from the same run:

Target: Customer Posting Group · Source: none · Type: unknown

Open question: “How should Business Central-only setup fields such as posting groups, VAT groups, dimensions, location, and operational defaults be populated?” — no QuickBooks field can answer this; it decides which G/L accounts every migrated transaction posts to, and someone accountable has to choose.

Six steps to create a data mapping document

Inventory both sides completely. Every target field gets a row before any mapping starts — coverage is measured against the target, never against how much source was used. Partial inventories are how fields get silently lost.
Propose with evidence. Every derived rule cites the specification line it rests on, verbatim. A row that cannot cite a passage is suspect by construction.
Classify honestly — including “the target owns this.” Computed fields, number series and system identities are generated: mapping a snapshot balance into a field the system calculates from ledger entries is a classic migration defect the document should prevent, not cause.
Turn gaps into questions, not blanks. An empty cell reads as “not done yet.” A named open question with an owner reads as what it is: a decision someone must make. A wrong mapping is worse than an honest gap.
Review the rows, not the vibe. The reviewer’s job is per-row: check the evidence, judge the rule, decide. Confidence ratings — human or machine — order the queue; they never substitute for the check.
Version it and record the sign-off. The document outlives the project: auditors, support engineers and the next migration all read it. Who reviewed what, and when, is part of the artifact.

Where these documents usually fail

Completeness theater — every cell filled, because a full sheet looks finished. The uncertain rows are exactly the ones that need to look uncertain.
Source-first thinking — walking the source system’s fields and mapping each somewhere, which leaves required target fields with no row at all.
Evidence-free rules — “obvious” pairings nobody can check six months later. The two fields with the same name are not always the same field.
The unreviewed review — practitioners tell us the failure mode in real migrations is not the mapping but the client review that never truly happened. A document that makes its uncertain rows visible is what forces a real review to happen.

Three complete, unedited documents produced this way are public: the QuickBooks → NetSuite customer mapping, the QuickBooks → Business Central customer mapping, and the Salesforce → HubSpot contact mapping. The blank layout is the free template, the ways the workbook breaks in review are in source-to-target mapping in Excel, and the engineering behind evidence-first drafting is in LLM data mapping — described less technically on the AI data mapping page.

Draft yours from real specs — free Or walk a worked sample first

How to create a data mapping document — with a real worked example

What the document is — and what it is not

The columns that matter

Column sets vary by team; these are the ones that keep the document honest. (The free template ships exactly this layout as an ungated .xlsx.)

Column	What it holds
Target field / object	One row per target field the destination system needs — the document is complete when every in-scope target field has a row, not when the source runs out of columns.
Source field / object	Where the value comes from. Deliberately blank when nothing fits — a blank here with a question attached is a finding, not a failure.
Mapping type	direct, transformation, lookup, conditional, constant, generated — or unknown. “Generated” matters: fields the target system produces itself are things NOT to migrate.
Transformation rule / expression	The actual rule, precise enough to implement: truncations, concatenations, polarity flips, code-table lookups.
Evidence	The verbatim line from the source or target specification the pairing rests on. This is what makes a row checkable rather than merely plausible.
Assumptions & open questions	What must be true for the row to hold, and what nobody has decided yet. The uncomfortable column, and the most valuable one in review.
Status & reviewer decision	proposed → accepted / edited / rejected / deferred, with who decided. A mapping nobody reviewed is a draft, whatever the file name says.

A real populated row

From a real run mapping QuickBooks Online’s customer entity onto Dynamics 365 Business Central’s customer table — published unedited here:

Target: Contact · Source: Title, GivenName, MiddleName, FamilyName, Suffix · Type: transformation

COPYSTR(COALESCE(NULLIF(TRIM(CONCAT_WS(' ', Title, GivenName, MiddleName, FamilyName, Suffix)), ''), DisplayName), 1, 100)

Assumption: blank name components are omitted; organization-only records fall back to DisplayName; result capped at Business Central’s 100-character field length.

Target: Customer Posting Group · Source: none · Type: unknown

Six steps to create a data mapping document

Inventory both sides completely. Every target field gets a row before any mapping starts — coverage is measured against the target, never against how much source was used. Partial inventories are how fields get silently lost.

Propose with evidence. Every derived rule cites the specification line it rests on, verbatim. A row that cannot cite a passage is suspect by construction.

Classify honestly — including “the target owns this.” Computed fields, number series and system identities are generated: mapping a snapshot balance into a field the system calculates from ledger entries is a classic migration defect the document should prevent, not cause.

Turn gaps into questions, not blanks. An empty cell reads as “not done yet.” A named open question with an owner reads as what it is: a decision someone must make. A wrong mapping is worse than an honest gap.

Review the rows, not the vibe. The reviewer’s job is per-row: check the evidence, judge the rule, decide. Confidence ratings — human or machine — order the queue; they never substitute for the check.

Version it and record the sign-off. The document outlives the project: auditors, support engineers and the next migration all read it. Who reviewed what, and when, is part of the artifact.

Where these documents usually fail

Completeness theater — every cell filled, because a full sheet looks finished. The uncertain rows are exactly the ones that need to look uncertain.

Source-first thinking — walking the source system’s fields and mapping each somewhere, which leaves required target fields with no row at all.

Evidence-free rules — “obvious” pairings nobody can check six months later. The two fields with the same name are not always the same field.

The unreviewed review — practitioners tell us the failure mode in real migrations is not the mapping but the client review that never truly happened. A document that makes its uncertain rows visible is what forces a real review to happen.