In this chapter
- A mapping is a recorded decision. The physical thing, the business thing, a confidence, who decided, when and why.
- One decision travels every copy. A family of copies takes one mapping, and automation never overwrites a person’s.
- Some gaps need a bridging view. A built structure closes them, with its lineage kept.
- Source precedence picks the surviving value. A rule per attribute says whose value to keep when matched records disagree.
What is the document every project runs on?
Every data project runs on a document that doesn’t exist: the one that says which source table means which business entity, which column carries which attribute, and why. It’s where the model meets the source and one of them has to give. That knowledge lives in the head of the engineer who did the last integration, in a spreadsheet half-updated since its workshop, or nowhere.
Everyone downstream pays. The modeler can’t tell how much of the model is real. The engineer re-derives the translation table by table, and the next project starts the archaeology over.
The document isn’t missing because nobody thought of it. Kimball and Caserta’s 2004 book gave source-to-target mapping a whole chapter. Stranger still, ask a language model for a requirements document on a source database and it appends a source-to-business name mapping for traceability, unprompted. One of the teams behind this book’s demo datasets tried it. Even a model trained on the industry’s writing knows this document has to exist.
So why doesn’t the industry keep it? Because as a document, it goes stale the day after the workshop.
You need a mapping that lives where the metadata lives.
A model that cannot bind is a poster
Why does the spreadsheet fail? The usual diagnosis blames people, wrongly. Your modelers hold what the business means and your engineers hold what the sources are. Neither side is wrong, and someone still has to translate. It’s a problem of medium, not people.
A model that exists as a picture can’t meet a schema. So every comparison happens by hand, and each one is another place where meaning leaks out.
A conceptual model that cannot bind to a discovered schema is a poster.
So make both sides of the same material. One is the conceptual model, the agreed business language written down before anything is stored. The other is the source inventory, every source table and column kept on stable identifiers. Once both are recorded metadata, their correspondence 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 isn’t a line on a diagram. It’s a small record with a fixed shape: the physical thing, the business thing it means, a confidence in the pairing, who decided, when, and why.
The why is what every spreadsheet drops and the next reader needs most. A wrong-looking pairing with no rationale gets silently undone. One with its reasoning attached starts the right argument.
The who matters as much. The right decider is whoever can answer for the meaning, usually the modeler and the source system owner together, not whoever ran the tool.
The record is bidirectional. Ask a source table what it means or a business entity where it’s held, and one record answers both. That ends a scheduling fight that wastes months, because neither side waits. If the modelers came first, mapping a table is a lookup into their model. If the engineers did, the mapping raises a placeholder entity for the model to adopt later.
One decision, every copy
Source data rarely arrives once. It arrives as a family of copies, descended from one source table and renamed a little at every hop: a landing copy, a staging copy, and one more. Sources overwrite a row when it changes. So the third copy keeps every version of every row it has received, and history survives. That copy is persistent staging. All three keep the source’s shape and integrate nothing. So whatever your platform calls them, they sit with the data as it landed, on the bronze side of the bridge to silver.
Map each copy independently and you multiply the work by the depth of your pipeline. You also invite the copies to disagree. Then one column means one thing in landing and another in staging, unnoticed until an audit finds it. So map the meaning once, record the family as a structure rather than a naming coincidence, and let the decision travel the chain.
One pairing rule governs the family, or none does.
Ownership 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. It’s recorded with a decider and never silently overwritten by a later pass, however confident.
Without the distinction, the next automated run can flatten the catalog’s most careful decision. With it, automation fills every gap a person hasn’t claimed and stops at every gap a person has.
When the gap will not close
Some gaps a mapping can’t close. The model says Customer, and the source offers three tables, a join nobody recorded, and no foreign key in sight. That’s when projects reach for paint and ship a diagram claiming the source fits the model.
Build the structure instead, and keep the lineage.
Willibald, the garden webshop in this book’s demo dataset, shows the honest version. Its orders come from two systems that share a concept and nothing else. The webshop delivers a numerically keyed header table and position table. The roadshow file delivers one row per product line with the header values repeated. The roadshow’s text order identifiers carry a prefix deliberately unlike the webshop’s, so the integrated key must carry the source system too.
Only about a fifth of roadshow buyers give a customer number. A documented rule matches the rest to webshop customers through their payment card: card number, card company and expiry together, never one field alone.
Figure 5.1: two systems that share a concept and nothing else, and the bridging view that was built between them.No single mapping records any of that. And pretending otherwise costs a catalog its readers’ trust. A built structure closes the gap. You recover the undeclared joins, define a view that gives the sources the model’s shape, and encode the matching rule where it runs, not on a slide.
That view is a bridging view. The mapping discipline governs it too. The view maps to the entity it realizes. Every projected column records exactly which source columns feed it, computed expressions included. That column-level lineage is metadata committed with everything else, not a report generated afterward.
A bridging view with its lineage kept is a recorded decision. One without it is the next generation’s archaeology.
Matching tells you two systems’ records describe one customer. But when they disagree, whose email address or delivery address do you keep? Matching doesn’t say. That choice takes a rule per attribute: which source is trusted for it, and what breaks a tie, such as the most recent change. Whoever answers for the meaning rules it. It’s recorded with its decider, date and reason like any mapping, so every load applies it alike.
That rule is source precedence. Each attribute two matched records can disagree on needs one.
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’s still a proposal, and the review becomes the unit of work, not the match.
A proposed pairing, however it was produced, is a question for a person. Accepting, correcting and skipping with the reason recorded are all decisions. A recorded no is worth nearly as much as a yes, because it stops the same wrong suggestion returning next quarter.
Then 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 don’t exit the process. They become it. That makes the mapping your governance backbone, not documentation about it. Policy attaches to meaning, so a classification set once on the Email Address attribute reaches every column mapped to it.
The most valuable document your team produces
Integration claims are usually unfalsifiable. A slide saying customer data is integrated can’t 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, and read who decided and why.
| The people part | Whoever answers for the meaning, usually the modeler and the source system owner together, decides each mapping and each precedence rule. |
| Metadata to capture | Record each mapping’s decider, date, reason and confidence, every rejection’s reason, which family mappings are machine-made, each bridging view’s lineage and each attribute’s source precedence. |
| Regardless of stack | The mapping asks the same questions with or without tooling, and while tooling keeps the answers honest for you, a team with a spreadsheet and the discipline to record deciders and reasons is ahead of a team with neither. |
In DeltaVault today
In DeltaVault, our metadata catalog, you map a table to a business entity in the Business entity field on its Details tab. The Name from source table card walks back through the landing and staging copies to the source table and offers its name with two buttons: Use as business name, and Create entity and select. Saving carries the link across the rest of the table’s family, skipping any copy already linked to a different entity.
A link you make there is yours, with no badge. A mapping accepted from a Map & Match suggestion keeps its confidence score and written rationale, with an AI badge until a person changes it. Re-running a match never overwrites a mapping someone accepted, and every change records who made it and when.
