The Source-to-Target Mapping Document

Every ERP migration has one document that everything else depends on: the source-to-target mapping. And on most projects that document has a shelf life: accurate on the day it is signed off, then quietly diverging from the scripts, the source system, and the client's expectations until someone discovers the gap during a trial load. This piece is about why that happens and how to structure the document so it survives to go-live.

Most articles on this topic hand you a template and stop. The template is not the problem. The problem is that a mapping document is a living artifact maintained under pressure by multiple people, and almost nobody designs it for that.

What the document actually has to capture

The field pairs are the easy part. "source.customers.cust_name maps to res_partner.name" takes thirty seconds to write and nobody argues about it. If field pairs were the whole job, a mapping document would be a half-day task.

The bulk of the content is where projects are won or lost:

• Transformations. The source stores country as free text, the target wants an ISO code. The source has one address field, the target has four. Every non-trivial mapping row carries a rule, and that rule needs to be written in the document, not just in the ETL script that implements it.
• Defaults and fallbacks. What goes in the target's mandatory company_type when the source has no equivalent? Who decided that, and when?
• Deduplication and merge rules. The source has tens of thousands of customer records and some meaningful fraction are duplicates. The rule for deciding which record wins, which fields merge, and what happens to child records is a mapping decision, and it belongs in the mapping document.
• Ownership. Every unresolved question has a name attached: who on the client side answers it, and who on your side implements the answer. A mapping row without an owner is a mapping row that will still be open at cutover.
• Exclusions. What you are deliberately not migrating, and the client's sign-off on that. Untracked exclusions resurface as "where is my data" tickets in week one of production.

If your document captures field pairs but not these five categories, it is a lookup table, not a mapping document. The lookup table is what dies first.

The three ways mapping documents die

Three failure modes account for most of the damage. They will look familiar to anyone who has lived through a mid-size migration.

Death by late schema discovery

The mapping was built from the schema the client described, plus whatever the team could reverse-engineer in the discovery phase. Then, mid-project, someone finds the custom fields a previous consultant added in 2019. Or the second database the warehouse module writes to. Or the fact that status = 7 means something nobody documented and a meaningful share of the rows carry it.

Each discovery invalidates a slice of the mapping. If the document has no way to mark rows as "invalidated, needs re-mapping," the team patches the ETL directly and the document falls one revision behind. Then two. By the third trial load, the document describes a migration that no longer exists.

Death by logic that only exists in scripts

A developer hits an edge case at 6 p.m. before a trial load: some source records have a null tax ID and the target rejects them. They add a fallback in the transformation script, the load passes, everyone moves on. The document still says "direct mapping."

Do this a few dozen times over a months-long project and the mapping document becomes fiction. The real mapping lives in the ETL code, readable only by the people who wrote it. When the client asks "why did these invoices land with this tax code," nobody can answer from the document, so someone reads the scripts, and the answer takes a day instead of a minute. Worse: when that developer rolls off the project, the reasoning behind each fallback leaves with them.

Death by client-side data changes

The mapping was validated against a snapshot from March. It is now June. The client has been running their business the whole time: new custom fields, a new product category coded differently from the old ones, a data cleanup initiative on their side that renamed half the categories the dedup rules keyed on.

The document was correct when it was written. The source moved. Nobody's job was to notice.

Why the document is where the risk concentrates

An ERP integrator we interviewed put data mapping at 10 to 15 percent of total migration effort, and told us it was the hardest and riskiest part of the project. Note what that claim is not about: it is not about the ETL runtime, the loading tooling, or the cutover mechanics. Those are engineering problems with engineering solutions, and teams are generally good at them.

The mapping is different because it encodes shared understanding between three parties: the client (who knows what the data means), the functional consultant (who knows what the target expects), and the developer (who knows what the scripts actually do). The document is the only place those three understandings are supposed to converge. When it dies, they diverge silently, and the divergence surfaces as trial-load surprises, reconciliation failures, and the post-go-live ticket queue.

An ETL bug costs you a rerun. A mapping error costs you a wrong decision that was loaded, reconciled, signed off, and discovered by an end user in production.

A structure that survives contact with reality

A flat grid of source-field / target-field / transformation dies for one structural reason: it has no way to represent uncertainty or change. Every row looks equally done. Here is a row structure that does not have that problem. Each row carries:

• Source field(s). One or more source fields, with table.
• Target field. Target field, with model.
• Transformation. The rule, in prose a client can read.
• Status. unmapped, proposed, client-validated, implemented, verified-in-trial-load.
• Confidence. high, medium, guess. Be honest. A guess marked high is a production incident on a delay timer.
• Open question. The specific unresolved question, with the name of who owes the answer.
• Decision log. Dated one-liners: "2026-05-14: client confirmed null tax IDs get fallback code X, per J. Muller on call."

The status column is the important one, and its top value is deliberately not implemented.

Implemented means a developer wrote code. Verified-in-trial-load means the row's output was checked against real data. The gap between those two states is exactly where the "logic only in scripts" death mode lives.

The decision log is what saves you in month four. When someone asks why a transformation works the way it does, the answer is in the row, dated and attributed, instead of in a Slack thread nobody can find.

Two operating rules make the structure work:

1. No ETL change without a row change. If a developer touches a transformation, the row's transformation text and decision log get updated in the same sitting. Enforce it in code review if the mapping lives in a repo; enforce it socially if it lives in a spreadsheet.
2. Status only moves forward with evidence. A row goes to client-validated when a named client contact confirmed it, logged in the decision column. It goes to verified-in-trial-load when someone checked the loaded output. No batch promotions.

Review rituals that catch drift early

Structure without ritual still drifts, just with nicer columns. Two rituals carry most of the weight:

The mapping walkthrough. A recurring session (weekly on active projects) where the functional lead walks the client through rows that changed status, rows marked guess, and open questions past their owner's promised date. This is not a status meeting. You are reading actual rows with actual sample values on screen. Thirty minutes of "this source value becomes this target value, correct?" catches more mapping errors than any amount of testing, because the client is the only party who knows what the data means.

The doc-versus-scripts reconciliation before every trial load. Before each trial load, someone diffs the document against the transformation code: every transformation in the scripts must appear in a row, every row marked implemented or better must have corresponding code. The first time you run this, expect to find a batch of script-only transformations. That is not failure, that is the ritual doing its job. Trial loads are your rehearsals; walking into one with a stale document means rehearsing the wrong play.

Add one lightweight check for the third death mode: re-profile the source before each trial load and diff against the previous profile. New fields, new distinct values in coded columns, and row-count jumps in mapped tables are your early warning that the client's data moved under you.

Where tooling changes the starting point

Everything above assumes the mapping starts from a blank spreadsheet and a source schema you reverse-engineer by hand. That first pass, working out what's there, what it means, how it connects, is where the late-discovery death mode is born: whatever discovery misses in week one becomes a surprise in week nine.

This is the problem we are building Datapace to remove. We are building it to do the migration analysis for you: discover the source data model, map it to the target, and flag the gaps before you start. Instead of a blank grid, you would begin with a generated first-pass mapping over a discovered source model, so the rows that need human judgment (the guesses, the open questions, the dedup rules) are visible from day one instead of surfacing mid-project. We are building it as participants in Vector Institute's DaRMoD Summer 2026 cohort and in the NextAI 2026 program at HEC Montréal with NEXT Canada.

The goal: know exactly what maps to what, and what breaks if you move it, before you touch production. If you run ERP migrations and the mapping document is where your projects hurt, read how we think about the reprise de données problem, or reach out through datapace.ai.