The mapping is the deliverable

In this chapter

• The document every project runs on and nobody has.
• The anatomy of a mapping decision: recorded, reviewable, owned.
• Families: one decision carried across every copy of a column.
• Bridges for the gaps a mapping cannot close, with the lineage kept.

Everything so far has been preparation. Chapter 3 wrote down what your business means; chapter 4 put on record what your sources hold. This chapter joins the two, and everything that follows consumes the join.

What is the document every project runs on?

Every data project runs on a document that does not exist: the one that says which source table means which business entity, which column carries which attribute, and why. It is where the model meets the source and one of them has to give. You already know where that knowledge actually lives: in the head of the engineer who did the last integration, in a spreadsheet half-updated since the workshop that produced it, or nowhere at all. Everyone downstream pays for the vacancy:

• The modeler cannot tell how much of the model is real.
• The engineer re-derives the translation table by table.
• The next project starts the archaeology over.

The document is not missing because nobody thought of it. Kimball and Caserta’s 2004 book gave source-to-target mapping a whole chapter of discipline. There is stranger evidence that the document is inevitable: ask a language model to write a requirements document for a source database, as one of the teams behind this book’s demo datasets did, and it appends a source-to-business name mapping on its own initiative, for traceability, unprompted. A statistical model trained on the industry’s collective writing knows this document has to exist. The industry still does not keep it, because keeping it as a document is exactly what fails. A document goes stale the day after the workshop. What you need is a mapping that lives where the metadata lives.

A model that cannot bind is a poster

Be precise about why the spreadsheet fails, because the usual diagnosis blames people and the fault is not theirs. Your modelers hold what the business means. Your engineers hold what the sources are. Neither side is wrong, and someone still has to translate. The mistake is calling that a people problem. It is a problem of medium: a model that exists as a picture cannot meet a schema, so every comparison happens by hand, and every manual comparison is another place where meaning leaks out. A conceptual model that cannot bind to a discovered schema is a poster.

The two preceding chapters dissolved that problem. Chapter 3 made the conceptual model recorded metadata: entities, attributes, and definitions, each with a stable identity. Chapter 4 did the same for the source estate, giving you an inventory built on identifiers that survive a rename. Once both sides are made of the same material, the correspondence between them stops being a drawing and becomes a join. The mapping is that join, written down: this table is that entity, this column carries that attribute.

Anatomy of a mapping decision

A mapping worth trusting is not a line on a diagram. It is a small record with a fixed shape:

• the physical thing
• the business thing it means
• a confidence in the pairing
• who decided
• when
• why

The why is what every spreadsheet drops and what the next reader needs most. A pairing that looks wrong with no rationale gets silently undone; a pairing that looks wrong with its reasoning attached starts the right argument.

The record is bidirectional. Stand on a source table and ask what it means, or stand on a business entity and ask where it is held: both readings return the same answer because there is only one record. That also settles a scheduling fight that wastes months, because neither side waits for the other. If the modelers arrived first, mapping a table is a lookup into the model they built. If the engineers arrived first, the mapping raises a placeholder entity to be adopted into the model later. Whichever side gets there first, the other’s work attaches when it arrives.

The people part. Treat the review as the unit of work, not the match. A proposed pairing, however it was produced, is a question addressed to a person, and disagreement is a first-class outcome: accept it, correct it, or skip it with the reason recorded. All three are decisions, and a recorded no is worth nearly as much as a recorded yes, because it stops the same wrong suggestion returning next quarter. The signature matters just as much. A mapping has a decider the way chapter 4’s rulings had one, and the right decider is whoever can answer for the meaning, usually the modeler and the source system owner together, not whoever happened to be running the tool.

One decision, every copy

Source data rarely arrives once. It arrives as a family: a landing copy, a staging copy, a persistent staging copy, all descended from one source table and renamed a little at every hop. Map each copy independently and you multiply the work by the depth of your pipeline. You also create the conditions for the copies to disagree, so the same column means one thing in landing and another in staging, and the contradiction sits unnoticed until an audit finds it.

The discipline is to map the meaning once and let the decision travel the chain. Treat the family as a recorded structure rather than a naming coincidence, and one rule governs all of it: one pairing rule for the family, or none.

Ownership is what makes that cascade safe. A machine-made mapping belongs to the machinery, so it moves with its source, updates when the source’s mapping changes, and disappears when the pairing is cleared. A human-made mapping belongs to the person who made it, recorded with a decider and never silently overwritten by a later pass, however confident that pass may be. Without the distinction, a cascade is a hazard: the most careful decision in the catalog can be flattened by the next automated run. With it, automation fills every gap a person has not claimed, and stops at every gap a person has.

When the gap will not close

Some gaps a mapping cannot close. The model says Customer; the source offers three tables, a join nobody recorded, and not a foreign key in sight. This is where projects reach for paint and ship a diagram claiming the source fits the model. The discipline says otherwise: build the bridge, and keep the lineage.

The garden webshop from chapters 3 and 4 gives you the honest version. Willibald’s order data arrives from two systems that share a concept and nothing else. The webshop delivers orders as a numerically keyed header table and position table. The roadshow file delivers one row per product line with the header values repeated, and its order identifiers are text with a prefix deliberately unlike the webshop’s, so the integrated key has to carry the source system alongside the value. The two systems cannot even agree on who the customer is. Only about a fifth of roadshow buyers give a customer number.

Figure 5.1: two systems that share a concept and nothing else, and the bridge that was built between them.

Note: The documented rule for matching the rest to webshop customers runs through their payment card: card number, card company, and expiry together, never one field alone.

No single mapping records any of that, and pretending otherwise is how catalogs lose their readers’ trust. What closes the gap is a built structure. You recover the undeclared joins, define the bridging view that gives the sources the model’s shape, and encode the matching rule where it runs rather than on a slide. The mapping discipline still governs the bridge:

• The built structure maps to the entity it realizes.
• Every projected column records exactly which source columns feed it, computed expressions included.
• The lineage is metadata committed alongside everything else, not a report generated after the fact.

A bridge with its lineage kept is a recorded decision like any other. A bridge without it is the next generation’s archaeology.

The human is the loop

A mapping program stands or falls on where you put the people. Put them at the end, approving a finished batch of guesses under deadline, and you have a rubber stamp with better branding. Put them at the front, judging each pairing while it is still a proposal, and the work compounds: every accepted mapping becomes evidence for the next, because a table whose sibling is already mapped is no longer a stranger. Your decisions do not exit the process. They become it.

That compounding is what makes the mapping your governance backbone rather than documentation about it. Classify the Email Address attribute once, in the model, and every column mapped to it inherits the classification across every landing table, staging copy, and downstream structure the attribute touches. Policy attaches to meaning, and the mapping is how meaning reaches the physical estate. Chapter 9 takes this up.

The most valuable document your team produces

Metadata to capture. The mapping record holds five kinds of entry:

• Each mapping carries its physical thing, its business thing, a confidence level, the decider, the date, and the rationale.
• Each rejected pairing carries the reason for rejection.
• The family chain records which mappings are machine-owned and which are human-owned.
• Each bridging structure carries its column-level lineage.
• The classifications that flow through the mappings are recorded alongside them.

Regardless of stack. The mapping asks the same questions with or without tooling: what does this table mean, who says so, and what happens when the source changes. Tooling changes who keeps the answers honest, because a cascade will not forget the staging copy and a review queue will not lose the rationale. Still, a team with a spreadsheet and the discipline to record deciders and reasons is ahead of a team with neither.

Here is the payoff, and it is the sharpest test in the book. Integration claims are usually unfalsifiable: a slide saying customer data is integrated cannot be checked. A mapping can. Real integration leaves a record, and the record is a mapping: point to the entity, list the sources bound to it, read who decided and why. With that in hand, you are ready for the question the industry loves to argue about, which is what shape the integrated data should take. Chapter 6 takes it up with the one answer nobody markets: it depends, and here is how to decide.