Source-to-Target Mapping: The Columns That Matter and the Sign-Off That Makes Them Stick

Home / Guides / Source-to-Target Mapping

The first full test load finished overnight. Eleven thousand customers went in, and this morning credit control notice that every one of them has a payment term of 30 days, including four accounts that are prepayment only because they have defaulted twice. Somebody set that default. It is not written down anywhere, the person who ran the load is on another project this week, and the only artefact anyone can point at is a spreadsheet with four columns and a tab called v7 FINAL (use this one).

That is a source-to-target mapping failure, and it is not a technical one. The rule took ten seconds to write. What is missing is the record of what was decided, what it was decided against, and who agreed to live with it. The people who hit this are migration leads, finance managers and IT managers somewhere between the first extract and the second test load, in the week they realise the mapping they inherited will not survive a real cutover.

This guide covers what a source-to-target mapping is, the columns it needs and why each earns its place, a worked customer and item master example with real rules including concatenation, lookup substitution, unit of measure conversion and truncation, and the sign-off model that keeps the business on the hook for its own decisions. If you want the document rather than the reasoning, the source-to-target mapping template is free and already has these columns in it.

What source-to-target mapping actually is

A source-to-target mapping, abbreviated to STTM or just called the mapping, is the field-by-field specification of how data moves from the systems you are leaving into the system you are going to. One row per target field, and each row answers four questions: where does this come from, what happens to it on the way, what must be true for it to be accepted, and who decided that.

It is harder than it sounds because it is three documents wearing one coat: a build specification the developer works from, a business decision log the process owners sign, and a test specification the reconciliation runs against. Most are written as the first and used as the other two, which is why they collapse the moment anyone asks why a field looks the way it does. When the finance director asks in week two of go-live why the aged debt report is wrong, either you can open a row and point at a rule, a validation and a signature with a date on it, or you cannot.

Why the four column mapping fails

The mapping most teams start with has four columns: source field, target field, notes, done. It fails in five predictable ways, and all five are governance problems rather than technical ones.

• Silent defaults. Every field mandatory in the target and empty in the source gets a value from somewhere. If the mapping does not state it, the developer picks one at 11pm during the load window.
• Untestable rules. "Map to the correct code" and "as per workshop" cannot be built or tested. A rule that requires the reader to have been in the room is a note, not a rule.
• No named owner. "Finance" cannot sign anything. A queried rule circulates until someone with a deadline answers it, and that person usually has the least standing to decide.
• No version. Two people build from two copies, the rule changed in one of them, and it surfaces during reconciliation as a count nobody can explain.
• No validation. Without an acceptance condition per field, errors do not surface at load time. They surface six weeks later in a month-end report, by which point they are a reporting problem, a finance problem and a trust problem at once. That is the road to a post go-live reporting rescue, which costs more than the mapping would have.

The columns a mapping document needs

Here is the core set. Ten columns, each existing because of a specific failure it prevents.

Column	What goes in it	What breaks without it
Source system	The named system and environment, not "the old system". Include the instance if there is more than one.	Two legacy systems hold the same customer. Nobody knows which one the rule read, so nobody can reproduce the result.
Source table and field	The physical table and column, as the data dictionary spells them, not the screen label.	The screen label and the stored field diverge. The developer maps the wrong column and it looks plausible.
Source data type and length	Type, length, precision, nullability. Copied from the dictionary, not eyeballed from a sample.	Truncation and rounding are found after the load rather than before it.
Target object and field	The target entity and field, with its own type and length. Read from the target's data dictionary.	You discover during load that the field you assumed was long enough is not, or is a code table entry that does not exist yet.
Transformation rule	The executable statement of what happens: direct, concatenate, substitute, convert, derive, split, constant, not migrated.	Everything. This is the rule.
Default value	The value used when the source is null, blank or unmatched, plus whether the default is acceptable or a placeholder awaiting a decision.	Defaults get invented during the load window and become permanent.
Validation rule	The condition a record must satisfy to be accepted, and whether a failure rejects the record or raises a warning.	Bad data loads cleanly and surfaces months later in reporting.
Owner	A named individual who can make the business decision, not a department or a job family.	Queries go nowhere and get resolved by whoever is closest to the keyboard.
Sign-off status	Draft, proposed, agreed, signed or superseded, with a date against the signed state.	You cannot tell which rules are decisions and which are guesses. All of them get treated as decisions.
Notes	The reasoning, the rejected options, the open questions and the date each was raised.	The same argument is had three times, and the third time somebody changes the rule back.

Source data type and length is where the pain hides

Two columns are boring right up until they are the only thing that matters: source field length and target field length, side by side. Comparing them mechanically, straight from both data dictionaries, finds truncation, precision loss and code table overflow weeks before a load does.

Do not take a field length from documentation, blog posts or memory, including mine. Lengths differ by product, by version and sometimes by configuration, and vendors change them between releases. Read the real length from the target's data dictionary in the environment you are loading into, and re-check after any upgrade during the project.

A transformation rule is a function, not a sentence

The test is simple. Could a developer who has never met your business implement the rule and get the same answer you would? Could a tester write a pass or fail check from the row without asking? If not, it is not finished. There are only six kinds of rule, and naming which one you are using removes most of the ambiguity: direct copy, format change, concatenation or split, lookup substitution against an agreed cross-reference, calculation or unit conversion, and constant or default. A seventh, "not migrated", is a real answer and belongs in the mapping explicitly rather than as a gap.

A default is a business decision wearing technical clothes

Every default is somebody agreeing to be wrong in a specific, bounded way. Thirty days as a payment term default is a credit exposure. Zero as a credit limit default is a sales blockage on day one. The base unit of measure default is a stock valuation. Whoever signs them owns the consequence. A default that will be visibly wrong for more than about one percent of records is not a default at all, it is a cleansing task that has been misfiled.

Columns worth adding once the mapping is real

On anything larger than a single-entity move, add a stable rule ID such as CUST-014 that appears in the load code, the reject log and the test script; a mandatory in target flag, which forces the default conversation into a workshop rather than a load window; a measured fill rate, which reorders your priorities within an hour; cardinality, because anything other than one to one needs a rule for the collapse or expansion; and a load sequence and reconciliation check naming what must exist first and the count, sum or hash that proves the field arrived intact.

Worked example: customer master, legacy ERP to new ERP

This is an abbreviated but realistic customer master mapping. Field names are illustrative, the rule shapes are not.

ID	Source field	Type / len	Target field	Rule	Default	Validation	Owner	Status
CUST-001	ARCUST.CUST_NO	char(8)	Customer.Number	Direct, uppercase, strip leading zeros	None	Unique, not null, matches new format	Sales Ops Manager	Signed
CUST-002	ARCUST.CUST_NO	char(8)	Customer.LegacyRef	Direct copy, unchanged, searchable alias	None	Unique	Sales Ops Manager	Signed
CUST-003	NAME_1 + NAME_2	char(30) x2	Customer.Name	Concatenate with single space, trim, collapse doubles	None	Not null, length within target limit, overflow reported	Sales Ops Manager	Signed
CUST-004	ADDR_1..ADDR_4	char(30) x4	Address lines 1 to 3 + City	Split: last populated line becomes City, remainder fill lines 1 to 3	None	City not null where country is GB	Sales Ops Manager	Agreed
CUST-005	POSTCODE	char(10)	Address.Postcode	Uppercase, normalise to single inner space	None	Matches UK postcode pattern where country is GB, else warn	Sales Ops Manager	Signed
CUST-006	COUNTRY_TXT	char(20)	Address.CountryCode	Lookup substitution to ISO 3166-1 alpha-2 via agreed cross-reference	GB where blank and postcode matches UK pattern	Must exist in target country table; unmatched rejects	Sales Ops Manager	Signed
CUST-007	CURRENCY	char(3)	Customer.Currency	Direct, validated against ISO 4217	GBP where blank and country is GB	Must exist in target currency table	Financial Controller	Signed
CUST-008	VAT_REG	char(15)	Customer.VatNumber	Strip spaces and punctuation, uppercase, prefix country code if absent	Blank	Format check; blank allowed only where customer is non-VAT registered	Financial Controller	Agreed
CUST-009	TERMS_TEXT	char(20) free text	Customer.PaymentTermCode	Lookup substitution to term code via agreed cross-reference	N30 where blank	Unmatched value rejects, does not default	Credit Control Manager	Signed
CUST-010	CRED_LIM	numeric(9,2)	Customer.CreditLimit	Direct, currency of record, no conversion	0	Not negative; over 250,000 flagged for review before load	Credit Control Manager	Signed
CUST-011	STAT_FLAG	char(1)	Customer.Status	Substitute A to Active, P to Prospect, I to Inactive	None	Unmatched flag rejects	Sales Ops Manager	Signed
CUST-012	REP_CODE	char(3)	Customer.SalesPersonId	Lookup to new employee ID via leaver-adjusted cross-reference	House account where rep has left	Must exist and be active in target	Sales Director	Proposed
CUST-013	EMAIL_1	char(50)	Contact.Email	Lowercase, trim; split multi-address strings into separate contacts	Blank	Single valid address per contact; duplicates within account merged	Sales Ops Manager	Agreed
CUST-014	NOTES_TXT	varchar(2000)	Not migrated	Not migrated. Archived to read-only store with retention date.	n/a	n/a	Sales Director	Signed

The four rules in that table that always cause trouble

Concatenation (CUST-003). Two thirty character fields can produce sixty one characters of output. If the target is shorter, something is thrown away, and the record that truncates is almost always the customer with the long legal entity name, which is to say the important one. Never truncate silently: produce an overflow report before the load, let the business shorten the names it cares about, then apply a hard cut.

Lookup substitution (CUST-009, CUST-012). A substitution rule is only as good as its cross-reference table, and that table is a business artefact needing its own owner and sign-off. The critical design decision is what happens to unmatched values, and the answer is almost always reject rather than default. A rejected record is a phone call. A defaulted record is a discovery six months later.

Country and currency codes (CUST-006, CUST-007). A free text country field will contain UK, U.K., GB, Great Britain, England, ENG and a few blanks. Standardising on ISO 3166-1 alpha-2 and ISO 4217 codes is not pedantry, it is what makes tax determination, shipping and consolidated reporting work afterwards. Build the cross-reference from a distinct list of the values actually in the source.

VAT numbers (CUST-008). Format validation is not the same as existence. Where VAT status matters for tax determination, check the number against HMRC's check a UK VAT number service, which confirms validity and returns the registered name and address. It needs the number as input and cannot tell you whether a business is registered at all, so it validates what you hold rather than filling gaps.

Worked example: item master and unit of measure

Item master mappings carry a risk customer mappings do not, because the numbers get multiplied by each other. Get a unit of measure conversion wrong and you have not lost a name, you have misvalued stock.

ID	Source field	Type / len	Target field	Rule	Validation	Owner	Status
ITEM-001	ITEM.PART_NO	char(15)	Item.Number	Direct, uppercase, trim	Unique, fits target length, no reused numbers	Engineering Manager	Signed
ITEM-004	ITEM.UOM_TXT	char(4)	Item.BaseUom	Lookup substitution to target UoM code, aligned to UN/CEFACT Rec 20	Unmatched rejects; no free text accepted	Planning Manager	Signed
ITEM-005	ITEM.PACK_QTY	numeric(9,3)	Item.UomConversionFactor	Convert pack quantities to base UoM; retain three decimal places	Recalculated stock value within 0.01 of legacy per item	Financial Controller	Signed
ITEM-006	ITEM.WEIGHT_LB	numeric(9,2)	Item.GrossWeightKg	Multiply by 0.45359237, round half up to three decimals	Total weight variance across catalogue under 0.1 percent	Logistics Manager	Agreed
ITEM-007	ITEM.STD_COST	numeric(11,4)	Item.StandardCost	Direct; four decimal places preserved	Sum of cost times on-hand quantity reconciles to legacy stock valuation	Financial Controller	Signed

Three things make unit of measure rules different. They are usually one to many, because a legacy item with an "each" and a "box of 12" needs a base unit plus a conversion rather than one field. Precision is a design decision rather than an accident, because a conversion factor rounded to two decimals will not reconcile across a catalogue even when it looks right on any single item. And the code list should be a standard rather than a local invention: the UN/CEFACT Recommendation 20 unit of measure codes are the usual reference for units used in trade, and aligning to them saves an argument later when EDI or a customer portal expects them.

Item numbering deserves its own decision rather than a buried mapping row. If the migration is also the moment you clean up part numbers, settle intelligent versus sequential part numbering before you write ITEM-001, because that rule is far harder to change once it is loaded.

Writing rules that can be tested, not just read

Here is rule CUST-009 written as something a developer can build and a tester can check. The comment header is not decoration. It is the join between the mapping row and the code, and it is what lets someone audit the system a year later.

-- Rule CUST-009  Payment terms
-- Source: legacy ERP, ARCUST.TERMS_TEXT, char(20), free text
-- Target: Customer.PaymentTermCode
-- Owner: Credit Control Manager. Signed 2026-07-14.
-- Unmatched values REJECT. They must not default.

SELECT
    c.CUST_NO,
    CASE
        WHEN UPPER(TRIM(c.TERMS_TEXT)) IN ('30 DAYS','NET 30','30D','30')       THEN 'N30'
        WHEN UPPER(TRIM(c.TERMS_TEXT)) IN ('60 DAYS','NET 60','60D','60')       THEN 'N60'
        WHEN UPPER(TRIM(c.TERMS_TEXT)) IN ('EOM','END OF MONTH','30 EOM')       THEN 'EOM30'
        WHEN UPPER(TRIM(c.TERMS_TEXT)) IN ('PROFORMA','PRO FORMA','CASH','COD') THEN 'PREPAY'
        WHEN c.TERMS_TEXT IS NULL OR TRIM(c.TERMS_TEXT) = ''                    THEN 'N30'
        ELSE NULL
    END AS PaymentTermCode
FROM ARCUST c;

The important line is ELSE NULL. The tempting version is ELSE 'N30', which makes the load run clean and quietly gives thirty days of credit to every account whose terms nobody could read. A null forces a rejection, a rejection forces a conversation, and that conversation is with the person who signed the rule.

The same discipline applies to length. Run the overflow check before the load, and send the output to the field's owner rather than fixing it yourself:

-- Pre-load truncation check for CUST-003
-- Replace 40 with the real target length from the target data dictionary.

SELECT
    CUST_NO,
    TRIM(NAME_1) ||
      CASE WHEN COALESCE(TRIM(NAME_2),'') <> '' THEN ' ' || TRIM(NAME_2) ELSE '' END
      AS TargetName,
    LENGTH(
      TRIM(NAME_1) ||
      CASE WHEN COALESCE(TRIM(NAME_2),'') <> '' THEN ' ' || TRIM(NAME_2) ELSE '' END
    ) AS TargetLen
FROM ARCUST
WHERE LENGTH(
      TRIM(NAME_1) ||
      CASE WHEN COALESCE(TRIM(NAME_2),'') <> '' THEN ' ' || TRIM(NAME_2) ELSE '' END
    ) > 40
ORDER BY TargetLen DESC;

Both queries are ordinary SQL and will need adjusting for your dialect. The pattern is what matters: every rule in the mapping should have a query that proves it worked and a query that finds what it broke.

Who signs each rule, and why an unsigned mapping fails at go-live

This is the part everyone skips, and the part that decides whether the migration is defensible. A signature on a mapping row is not bureaucracy. It transfers a judgement from the person who can build the rule to the person who has to live with it. Until that happens, every default and every substitution is an IT decision by omission. At go-live the business finds them, correctly says it never agreed to them, and the migration is reopened in precisely the fortnight when finance, planning and the sales desk are already at capacity. The unsigned mapping did not remove risk. It moved the risk to the worst week of the year.

Sign-off has to be by named individual and by object, because the knowledge is distributed:

Data object	Who signs	What they are actually signing
Customer master	Sales operations lead	Which customers come across, how they are named, matched and deduplicated, and who owns each account
Credit and payment terms	Credit control manager	The credit exposure created by every default and every unmatched term
Supplier master	Purchasing lead	Approved supplier status, payment terms and bank detail handling
Item master	Engineering and planning leads	Numbering, units of measure, revision handling and what counts as the same item
Stock and valuation	Financial controller	Quantities, costing method and the reconciliation the auditors will read
Nominal and ledger structure	Financial controller or finance director	Account mapping, opening balances and the comparatives that follow from them
Open transactions	The process owner for that transaction type	What "open" means on cutover day, and what happens to partial receipts and part-shipped orders

Five states are enough for the status column. Draft, the analyst has written something. Proposed, it has gone to the owner. Agreed, the owner is content in principle but the rule has not been proven against real data. Signed, the owner has seen the rule applied to a real extract and accepted the result, with a date. Superseded, it was signed then changed, both versions kept because someone will ask why the second test load differs from the first.

The distinction between agreed and signed is what saves projects. Agreement in a workshop is agreement to a description. Signature after a test load is agreement to what the rule did to their data, including the eleven records that came out looking odd. Only the second holds up when challenged.

On a GBP 4.5m programme consolidating four legacy ERP systems onto a single Infor LN cloud instance for a 220 user group, the mapping was the one document four sets of process owners had to agree on line by line. The sign-off column kept those arguments in the design phase, where they were cheap.

If you cannot fill in the owner column because it is genuinely unclear who owns customer or item data, the mapping is not your first problem. Fix ownership with the master data ownership matrix, then come back. For finance objects, where signature is part of the audit trail rather than just good practice, the finance data migration sign-off pack puts the balance and control-total evidence in a form a financial controller will actually sign.

Edge cases and variations

The straightforward rows take an afternoon. These are the ones that take the other three weeks.

• Target fields with no source. Each needs a decision: derive, default, capture manually before go-live, or leave empty and accept the consequence. Put them in as rows with a blank source rather than meeting them during configuration.
• Source fields with no target. "Not migrated" is a legitimate rule, but sign it like any other, and it usually implies an archive with a retention date rather than deletion.
• Many to one and one to many. Four address lines collapsing into three plus a city; one legacy item row becoming an item plus two unit of measure conversions plus a supplier price. The first needs a rule for which source wins. The second needs a load sequence and a parent key, and it is where most spreadsheets quietly stop being one row per target field.
• Multi-source survivorship. The same customer in the ERP and the CRM with different addresses. Document the rule (ERP wins for financial fields, CRM wins for contact fields) and sign it, because it will produce visibly odd results for a handful of accounts.
• Open transactions, balances and history. Three mappings with three acceptance tests. Balances reconcile to a control total, open transactions to a count and an aged profile, history usually should not be migrated at all. Deciding this late is a reliable way to lose a go-live weekend.
• Dates and sentinel values. Two digit years, dates stored as text, and the perennial 01/01/1900 or 31/12/9999 values that mean "empty" in one system and are a real date in the next.
• Character encoding and personal data. Single-byte legacy extracts mangle accented names and currency symbols on the way into a UTF-8 target, so set the encoding rule at extract level and test with an accent and an ampersand. Migration is also the cheapest moment to stop carrying personal data you no longer need: flag those fields with a lawful basis and a retention position.
• Reporting and warehouse mappings. The same document works, but the validation column changes character: instead of "must exist in the target code table" it becomes "this measure must reconcile to the source report for these three periods". If reporting is moving too, the post go-live reporting reconciliation checklist covers the checks that come after the mapping.

Troubleshooting checklist

Run through this when the mapping exists but the loads keep going wrong.

• Is there exactly one master copy? If the answer involves the word "latest", there is not.
• Does every mandatory target field have a row? Diff the target's mandatory field list against the mapping. Missing rows become emergency defaults during the load.
• Does every substitution have an unmatched path? If it is not "reject and report", find out who agreed to the alternative.
• Are source and target lengths compared field by field? Not sampled. Compared, from both data dictionaries, in the spreadsheet.
• Do rejects have a route back? A reject nobody owns is a record that will not be in the new system on Monday. Each reject reason should carry the rule ID and its owner.
• Is the owner a person, and is the signature against real data? Departments do not sign, and agreement in a workshop is agreement to a description rather than to a result.
• Is the reject count falling between loads? If load three rejects as much as load two, either the source is not being cleansed or one rule is failing repeatedly and nobody reads the log.
• Do the reconciliation checks exist as runnable queries with a documented expected result, and has the target configuration changed since the mapping was written? Code tables gain values and field lengths change on upgrade.

Related services

The mapping is the centre of gravity of an ERP data migration, and it is where most of the time goes on a data migration engagement: building it with the process owners, proving each rule against a real extract, chasing the sign-off column until it is full. Migrations rarely fail because the tooling was wrong. They fail because forty rules were never decided by anyone, and forty defaults decided them instead.

If a go-live date already exists and you need an honest read on whether the data will be ready for it, the ERP data migration readiness review is the right first step: a fixed 5 to 10 day piece of work that profiles the actual data, tests the existing mapping against it, and says what will fail and when, while there is still time to act.

Where the problem is the records rather than the rules, the duplicates and contradictions no mapping can transform its way out of, that is data quality work and it belongs before the mapping. For Infor LN and Baan, where item code segmentation, multi-company structures and the standard interfaces shape what the mapping can even attempt, see Infor LN consultancy, and for ownership of rules and definitions after go-live, data management.

The natural next step from here is the document itself. The source-to-target mapping template is a free Excel workbook with these columns already in it, owner and sign-off included. Pair it with the ERP data migration checklist if you are earlier in the project and still deciding what to migrate at all.

Frequently asked questions

What is source-to-target mapping?

Source-to-target mapping is the field-by-field specification of how data moves from the systems you are leaving into the system you are going to. One row per target field, stating the source system, source table and field, data type and length at both ends, the transformation rule, any agreed default, the validation the record must pass, a named owner and a sign-off status. It is build specification, business decision log and test specification at once.

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

At minimum: source system, source table and field, source data type and length, target object and field, target type and length, transformation rule, default value, validation rule, owner, sign-off status and a notes column carrying the reasoning. Once it is being built from rather than discussed, add a stable rule ID, a mandatory flag, a measured fill rate, cardinality and the reconciliation check that proves the field arrived intact.

Who writes the source-to-target mapping, and who signs it?

A business analyst or migration lead drafts it, because that role can read both data dictionaries. The process owner signs it, because that person lives with the result: sales operations for customer rules, credit control for terms and limits, engineering and planning for item and unit of measure rules, the financial controller for ledger and balances. Always by named individual, because a department cannot be held to a decision.

Should we use Excel, or a mapping tool?

Excel is fine, and usually correct for an SME migration, provided there is exactly one master copy, it is versioned, and rule IDs are stable enough to quote in code and in email. What breaks a mapping is not the tool, it is uncontrolled copies and rules that live in someone's head. Whatever holds the mapping must be what the developer builds from and the tester tests against.

How detailed does the mapping need to be?

Detailed enough that a developer who has never met your business could implement the rule and get the same answer you would, and a tester could write a pass or fail check from the row without asking a question. "Map to the correct code" fails both tests. A named cross-reference table, an explicit unmatched path and a stated default pass both.

What if we run out of time and skip sign-off?

Then the defaults, truncations and substitutions become IT's decisions by omission, the business finds them at the first month-end after go-live, and it will be right to say it never agreed to them. If time is genuinely short, sign the highest-consequence objects first: credit terms, stock valuation, ledger structure and open transactions. Those four carry most of the money.

If you are mid-migration this week

Nobody reads a guide on source-to-target mapping out of curiosity. If you are here, there is a load coming, a date in a plan, and a spreadsheet you are not entirely confident in.

Send it to me with a sample extract and I will tell you which rules are decisions and which are guesses, which fields will truncate, and which mandatory target fields have no row at all. Book a 30-minute data risk call, or start the data readiness review if the go-live date is fixed and you would rather know now than in week two of live running.

Matty Hatton is the founder of Digital Adaption, an ERP and data consultancy on the Wirral working across the North West and the UK, specialising in ERP data migration, master data and automation for manufacturing SMEs. Delivery is ISO 9001 certified. LinkedIn | Get in touch