A report lands on your desk on a Tuesday. Revenue by customer is off by a small, stubborn amount, and one account shows a credit limit nobody in finance recognises. The pipeline ran without errors. The load succeeded. The only thing anyone can answer quickly is where the field sits, and the questions that matter, who decided this legacy account number belongs to that customer key, and what should happen when the source value is blank, have no owner and no record.
That's the gap source target mapping is meant to close, and it's why teams that treat it as a spreadsheet of field pairs keep finding themselves in the same conversation six months after go-live.
This walkthrough goes one layer at a time: what a mapping row has to record, how to match fields by meaning rather than name, how to get decisions approved by someone accountable, and how to prove after load that a migrated value still means what it meant in the source.
Table of Contents
- What Source Target Mapping Means and Why Systems Need It
- The Fields Every Mapping Specification Must Record
- Match Fields by Meaning Not by Name
- How to Build and Approve a Mapping Step by Step
- A Worked Example From SAP Customer Data to Enterprise Key
- Validating Mapped Data With Three Check Layers
- Document Your First Mapping Before the Next Load
What Source Target Mapping Means and Why Systems Need It
Say a family packs up a house. Boxes get labelled in the family's own shorthand: kitchen, fragile, office leads. The new house has different rooms, a different kitchen, and no office at all. Every box still needs a destination, and the shorthand means nothing to the people unloading it.
Data leaving SAP for a warehouse behaves the same way. The labels are field names, and the rooms on the other side are modelled attributes with rules attached to them.

Source target mapping is the written record of that move. The source is the system a value leaves, such as SAP ERP or a legacy billing database. The target is the governed model it has to land in, such as customer_key in a warehouse or a lakehouse table feeding a reporting layer. The NIST work on metadata standards describes the practice as the controlled translation layer that preserves meaning between ERP, analytics, integration and reporting platforms, and it notes that libraries were already exchanging structured catalogue records across unlike systems in the 1960s. The problem is older than cloud migration.
Take one customer. It may arrive three ways: SAP KNA1-KUNNR, a legacy billing account number, a CRM record id. All three resolve to a single enterprise customer key. Beside each link sits a datatype change, a code translation, a default value and a validation rule.
The three-line test: name a target field you already trust, then state its source field, its target field and the transformation. If you can't finish the third line, you have a column pairing, not a mapping.
The Fields Every Mapping Specification Must Record
A mapping row is small. Done properly, it carries enough context that an engineer who joined last month can rebuild the rule without asking anyone. A source name and a target name don't come close to that.
The eight fields a row must carry
| Field | What It Records | Example |
|---|---|---|
| Source field | System, table and field where the value originates | SAP KNA1-KUNNR |
| Target field | Governed attribute that receives the value | customer_key |
| Cardinality | How many source records map to one target record | Three legacy accounts to one customer_key |
| Transformation logic | Rule applied to the value | Convert weight from pounds to kilograms |
| Reference-data conversion | Source code translated to target code | Country key DE to Germany |
| Null handling | What the target receives when the source is blank | Net 30 default for missing payment terms |
| Exception rule | What happens to records that break the rule | Duplicate tax IDs held for review |
| Owner and approval | Who decided the rule and when | Billing lead, dated decision log entry |
Two of those rows do the most work and get skipped most often. Cardinality decides whether a load merges, splits or duplicates: three legacy accounts belonging to one customer either collapse into one key or become three customers, depending on a decision nobody wrote down. Null handling decides what a blank becomes, and a default is a business decision rather than a technical one. If missing payment terms become Net 30, someone in finance owns that choice, because it changes what the receivables report says.
Exception rules are where mapping stops being a document and starts being a control. A duplicate tax ID isn't a data defect to be scrubbed without review. It's a case a person has to adjudicate, and the specification should name that person.
What incomplete specifications cost
The exposure is well documented. An analysis of ETL migration failure patterns compiles industry figures showing that 83% of data-migration projects either fail outright or exceed their budgets and schedules, while a second summary attributes 84% of projects to failure to meet objectives or significant overruns. Treat both as industry estimates rather than universal measurements, and the message still lands: incomplete discovery, undocumented transformations and reconciliation gaps are recurring causes of that overrun.
The same analysis names the checks that make a specification testable, namely row counts, control totals and record-level comparisons. Those aren't extras bolted on at the end. They're the evidence that the written rule and the running pipeline agree.
Practical rule: if a row can't tell you who approved it and on what date, it isn't a specification. It's a draft with a long shelf life.
Reference data is the quiet failure mode inside all of this. A country key of DE becoming Germany, a unit code, a currency, a market restriction: each is a small lookup that determines whether a downstream report filters correctly. Teams working with regulated product data often start by comparing product compliance data platforms to decide where controlled vocabularies should live and who maintains them, which is the right question to ask before the lookup table gets hard-coded into a pipeline.
Match Fields by Meaning Not by Name
Two columns called STATUS rarely mean the same thing. In one system the value means billable; in the other it means shippable. The names match, the meanings don't, and the pipeline will happily produce a wrong answer for both.
The same customer can appear as KNA1-KUNNR in SAP, an account_no in a legacy billing table and an account ID in the CRM, while grain, data type, code set and table shape all differ. Matching is a schema-matching problem before it's a transformation problem. Schema-matching benchmarks show that methods scoring well on simple flat schemas degrade substantially once nesting and relationships between tables appear, which is why one matcher, human or automated, can't serve as the specification on an SAP-to-cloud programme.
Five checks before anyone drafts a rule
- Read the business definition first. The description attached to the field in the source system beats the column title every time, and it usually reveals the exceptions.
- Compare type, length and samples. SAP customer numbers are ten characters with leading zeros. A spreadsheet export drops those zeros, so a sample showing 12345 instead of 0000012345 points at the extraction, not at the source.
- Compare grain. One row per customer is not one row per account. Joining the two without a stated cardinality multiplies records, and the duplication usually surfaces in a finance report rather than in testing.
- Compare code sets. DE and Germany are one idea in two encodings. A lookup that handles the first and not the second produces blanks that look like missing data.
- Record the confidence and the evidence. Every proposed pair gets a score, the evidence behind it and a named owner when the score is low or the field carries two meanings.
A false match costs more than a missing one, because it loads without complaint. Getting names, types and definitions aligned before mapping begins is the same discipline behind data harmonization across mixed SAP and Microsoft estates. It holds outside ERP data too: anyone trying to map statement text to JSON schema meets the same split between layout matching and semantic matching, and the semantic check is the one that catches errors.
No transformation gets written until the correspondence and its confidence are on the page.
How to Build and Approve a Mapping Step by Step

A status field passes its format checks, then fails during testing because finance and supply chain use the same value differently. The fix is not just another transformation. The mapping needs a recorded decision: which meaning applies, who approved it, and what evidence will show the rule worked after loading.
The sequence below moves from evidence to accountable approval. Profiling comes first; sign-off comes after the rules, exceptions and exclusions are clear.
The seven steps and why each one exists
Profile each source field. Count distinct values, check the null rate, confirm the data type and inspect frequent values. Without this evidence, a match based on a column name can miss that one status field has different meanings across departments.
Propose matches with evidence attached. Compare names, business definitions, types and sample values, then record confidence. Reviewers can focus on uncertain matches instead of spending equal time on every proposed pair.
Write a rule for each field. Specify transformations, unit conversions, code translations and defaults so that two engineers would implement the rule the same way. If a rule allows two reasonable interpretations, it needs a decision before implementation.
Assign ambiguous cases to a named owner and set a deadline. Send low-confidence matches and conflicting definitions to the person responsible for the relevant business meaning. If departments need different interpretations, record separate rules and owners rather than hiding the difference in a compromise.
Review generated or copied rules before implementation. Check for deletes, local exceptions and relationships across tables. Treat the rule as a proposal until the domain owner accepts it.
Record each decision and its scope. Include who approved the rule, why it was chosen, and what was excluded, deduplicated or defaulted. A dated decision log preserves the reasoning, so a later reviewer can distinguish an intentional exclusion from an overlooked record. Source-to-target mapping guidance also covers defaults for mandatory fields, merge rules and ownership.
Version the mapping with the pipeline. Tie each rule change to a release. If a report changes after deployment, the team can identify which mapping version changed the result rather than reconstructing the rule from SQL.
The sequence puts substantial effort into checking meaning and securing decisions before rules reach production.
What finished looks like
A finished specification records the owner and rationale for every rule, default, exclusion and merge. For critical objects such as customers, materials, suppliers and financial postings, it also records business acceptance and how the result will be checked after loading.
That record gives the mapping workstream a clear basis for data migration strategy and cutover planning.
A Worked Example From SAP Customer Data to Enterprise Key
A retailer merges an SAP customer master, a legacy billing system and a CRM into one enterprise customer key. Three rows carry the whole job.
| Source Field | Target Field | Rule | Blank or Conflict Handling |
|---|---|---|---|
| SAP KNA1-KUNNR (customer number) | customer_key | Pad to 10 characters with leading zeros | Reject and log if blank |
| Legacy billing account_no | customer_key | Trim spaces and match on tax ID | Create new key if no match, flag if two matches |
| CRM account ID | customer_key | Use only when no SAP key exists | Flag when both SAP and CRM keys are missing |
Why each rule looks the way it does
The SAP rule exists because the customer number is ten characters and the export isn't. A CSV drops the leading zeros, so the pad-to-ten step runs before any matching happens. Without it, 0000012345 and 12345 look like two customers, and the warehouse gains a duplicate that only appears when someone reconciles the customer count.
The legacy rule exists because account numbers arrive with trailing spaces and in two formats. Trimming is the easy half. Testing both formats against the tax ID is the half that gets forgotten.
The CRM rule is a precedence decision, and the business has to agree to it in writing rather than leaving it to whoever writes the join. Its blank handling matters just as much: when a record has neither an SAP key nor a CRM key, the mapping flags it instead of inventing a match.
Then the conflict. One legacy account matches two SAP customers that share a tax ID, which is common across legal entities or after an acquisition. The mapping shouldn't guess. It flags the pair, routes it to the billing lead and records the decision with a date, so the same question doesn't get reopened by the next person who notices it.
Write the same four columns for one field in your own migration: the rule, the default, the conflict handling and the owner.
Validating Mapped Data With Three Check Layers
Validation isn't a single gate at the end of a load. It's three layers, and each catches what the one above it can't.

Structural checks and reconciliation
Provenance research on ETL transformation history makes the case that quality controls belong at the mapping stage rather than after loading. Schema checks confirm the expected columns and data types arrived. Completeness checks catch a null rate crossing the baseline recorded during profiling. Domain checks validate code values and units. Uniqueness checks protect business keys. Reconciliation compares counts, sums and control totals between source and target, so a gap of 23 invoices is visible on the first load rather than in a month-end close.
| Check | What It Catches | Example Failure |
|---|---|---|
| Schema check | Expected columns and data types missing | Currency column loaded as text |
| Completeness check | Null rate above its profiled baseline | Payment-terms blanks rise above the profiled baseline |
| Domain check | Invalid codes or units | Currency code USD loaded as US |
| Uniqueness check | Duplicate business keys | Two customers share one customer_key |
| Reconciliation check | Count, sum or control total gaps | Source holds 4,210 invoices, target loads 4,187 |
| Semantic test | Values that lost their meaning | Blocked customer still shows open orders |
The layer row counts miss
Record counts prove that records arrived, not that they still mean anything. A semantic test asks whether the migrated value produces the behaviour it produced before: a blocked customer still shows open orders, a discontinued material still refuses new sales orders, a credit hold still stops a delivery. Migration-risk reporting separates format validation from meaning validation for exactly this reason, and ranks the absence of deterministic conversion validation among the recurring implementation problems it tracks.
The third layer is business-outcome validation. Run an invoice, check a stock balance, regenerate a report and compare it against the source-system output during a cutover rehearsal. If the invoice totals differ, the mapping is wrong somewhere, and a row count will never tell you where.
Ask the awkward question before cutover: what decision would this data produce today, and does it still produce the same one after migration?
Classify the miss before you fix it
The UK Government Data Quality Framework, cited in that same provenance research, recommends establishing a baseline against defined quality rules and distinguishing systemic defects from one-off errors such as an incorrect migration or import. The classification is actionable. Repeated failures point at the mapping, the source system or the reference data. An isolated failure usually points at a batch or a deployment.
Keep row-level error evidence and aggregate reconciliation results together as the audit trail, then have the business sign acceptance for critical objects. Pair that with runtime lineage from each rule: source system, extraction timestamp, target dataset, transformation version, affected records and execution outcome. That trail is what makes a later change to an SAP field, an API, a code list or the target semantic model traceable to the loads it affected, which is the core of SAP data validation done properly.
Document Your First Mapping Before the Next Load
Pick one critical object, customers or materials, rather than the whole environment, and finish it before the next load runs.
- Complete the eight fields for every feed into that object. Source, target, cardinality, transformation, reference-data conversion, null handling, exception path and a named owner.
- Put every open question in a dated log. Include anything excluded, deduplicated or defaulted, with the person who decided it.
- Run the checks on a rehearsal copy. Schema, completeness, domain, uniqueness and reconciliation, then one business-outcome test such as an invoice, a stock balance or a report that has to stay correct.
- Keep row-level errors beside the control totals. One without the other can't tell you whether a failure was systemic.
- Book the sign-off this week. Block production load of that object until the acceptance is dated.
Beyond one object, the scale question decides the tooling. A single customer feed can live in a spreadsheet plus the log. Many SAP and non-SAP sources landing on one target justify tooling that automates mapping and validation instead of relying on a file that goes stale between releases. A Kagool assessment can account for source systems, migration priorities, workload design and governance, and for how existing SAP environments and wider data platforms shape the target architecture. Organisations with a defined SAP migration capability or a workload gap can share their requirements with us directly.
Our own accelerator for this work is Pulse, which automates data mapping and validation and supports high-speed ingestion without dropping integrity checks. Where the target sits on a cloud platform, mapping decisions travel with the platform choice, and it helps to see how delivery partners structure that layer, which is what Faberwork's Snowflake partner collaboration sets out for teams weighing the same ownership and handover questions.
The week-one outcome is a review date on the calendar and one object with a complete, dated specification behind it.
Kagool delivers SAP data migration programmes where mapping is treated as a governed record of decisions rather than a spreadsheet that drifts from the pipeline. If you're planning an S/4HANA move or consolidating SAP, legacy and CRM data onto one governed model, our teams can profile your sources, build the mapping specification with named owners, and prove the result with reconciliation and business-outcome tests before cutover. Visit Kagool to talk through your migration requirements with our data specialists.

