In this chapter
- Every source gets a register entry. It says what the source can tell you, what it can’t, and who owns it.
- A refresh is a diff you approve. Schema drift never stops, so nothing enters the catalog unread.
- Profiles are observations, not rulings. Evidence piles up by itself. A ruling needs a person.
- A source is a witness. It’s true about how it stores data and silent about what your business means.
Every data project starts by proving you can read the source. Can you connect, list the tables, and pull the columns and their types? And what can this source never tell you? Teams treat that as throat-clearing before the real work. It is the real work. A team that doesn’t write it down does it again every sprint.
Part of that answer holds for every source. A source is accurate about how its application stores data and silent about what your business means by it. So treat it as a witness, never the author of meaning.
Meaning lives in the conceptual model, the business language agreed and written down before anything is stored. Sources answer a plainer question: what did we actually land? The gap between the two is the silver layer’s work.
A register, not a pile of connection strings
A live database connection can hand you tables, columns, types, primary and foreign keys, and views. A nightly file drop gives you headers and whatever the rows imply. Most sources arrive with their table and column descriptions empty. So every connection has its own mix of what it can tell you, what it can’t and what you’ll learn elsewhere: its capability profile.
Write that profile down, one entry per source, with how you connect, who owns the system, when extracts arrive, what the connection supports, and the limits you’ve found. That’s the source register. A team without tooling still keeps one, because the alternative is rediscovery.
The engineer who learned in March that the webshop extract carries dates but no timestamps leaves in June. In July, someone burns two days learning it again.
An unwritten source limit is a recurring cost booked as a surprise.
An inventory that survives a rename
What you read goes into a catalog as an inventory of every table and column, the first record everything else points at. Later work pairs each source table and column with the business meaning it carries. That pairing is the mapping, and it points at these entries, as every decision about them does. So each entry needs an identifier that stays put while everything around it changes.
Names can’t do that job. Columns get renamed, tables move between schemas, and one logical source turns up under different names in development and production. When that happens, every reference into a spreadsheet keyed on names breaks without an error message.
So give each entry an identifier that never changes, and hang the names off it as facts free to change. Those stable identifiers carry identity. Names are only labels.
Import is a diff you approve
A source inventory would be a one-time chore if sources held still. They don’t. Columns appear, types widen, a table splits in two, and none of it waits for your review meeting. That steady change is schema drift. It isn’t an incident. It’s the weather.
So a catalog refresh is a staged change you review, not a reload. The import lands as a proposal: these tables are new, these columns changed type, these disappeared. A person approves the diff, and the catalog moves forward with its history intact.
A truncate-and-reload fails twice. It destroys every description, mapping and decision keyed to the inventory. It also waves drift through as silently as the source produced it.
A dropped column should be a decision made with the diff in front of you, not a blank report found downstream.
Figure 4.1: the export, the reviewed diff, and the catalog entry. Seventy-two rows arrive honest about structure and empty about meaning; the review is where meaning gets attached, and where it stays.The worked example is Willibald, the garden webshop of Maluga, the outdoor-living retailer the book’s examples run on. Its metadata arrives as exactly that export: 72 rows describing ten tables, from Bestellung (the order header) to Wohnort (a customer’s places of residence over time).
The file is honest about structure. It proves that Bestellung.KundeID is a thirteen-character foreign key to Kunde, and that Position hangs off Bestellung by a composite key. It’s just as honest about meaning, since every table and column has a description field and all 72 arrive empty.
The source shows exactly how the order data is shaped and nothing about what an order is.
What does data profiling tell you that the schema will not?
The inventory records what the source claims about itself. Checking those claims against the data, column by column, is data profiling: null rates, distinct counts, ranges and top values. Where values run unique, a profile proposes candidate keys, and where value sets overlap, candidate relationships.
None of this is new. Kimball and Caserta gave profiling a named place in the warehouse in 2004, in their book on extracting, transforming, and loading warehouse data. That book is still the last mainstream text to give it one. Profiling is also where the model meets the source for the first time.
Profiling earns its keep on the gap between declared and true. The Willibald data carries exactly the failures profiling exists to catch. Product rows are duplicated in the extract. One table has no business key at all, nothing the business uses to tell one occurrence from another. And order numbers from an earlier load period are reused in a later one, a key collision that never shows in a schema and always shows in a join. A profile run finds each as a measured fact, with numbers attached.
What you do next decides whether the catalog stays trustworthy. A profile run observing that Position.PosID never repeats is evidence. Declaring the pair of order and position the key is a decision. Evidence piles up by itself and goes stale harmlessly. Decisions are agreed by people and carry authority. So call the first an observation and the second a ruling, and keep them in separate places with separate lifecycles.
Record each promotion too: who turned which observation into which ruling, and when. A catalog that files observations as facts has automated its own gullibility, because one bad extract’s profile becomes policy without anyone deciding.
The witness tells the truth, but only about itself
Why can’t a source tell you what it means? Because a relational schema records how an application chose to persist something, not what it means: your database is not your business. Surrogate keys, junction tables, status codes and audit columns are implementation decisions, made under a deadline, often by a vendor who never met your business. As testimony about the application, a schema is excellent evidence. As testimony about your business, it’s a witness quoted on questions it never saw.
Your conceptual model keeps hitting that limit. The model says a customer has a delivery address. The source has a customer table, an address table and a type column carrying values like Shipping. The clean noun on your diagram turns out to be a join and a filter that nobody has written down. Writing it down is a mapping’s job.
Neither side is wrong. They’re answering different questions.
Figure 4.2: the clean noun on your diagram turns out to be a join and a filter.So the last instrument isn’t a query. It’s a conversation with the people who run the process. In a twenty-five minute walk through Willibald’s order tables, field by field, with the person who operates the webshop in the room, the model gains facts no profile run could reach.
Wunschdatum is the delivery date the customer requested, not a promise the business made. Early delivery isn’t automatically good, because plants can arrive before the customer is ready.
The order discount reads as an amount off the total, but no discount-type column exists. So the room records the reading and files a validation question instead of letting the guess harden. No order status is stored anywhere. Open and delivered are derived states, read off the positions and deliveries.
The roadshow table arrives as a separate file from Willibald’s second sales channel. It’s named like an order table, but each row is one product line of a point-of-sale transaction, with header values repeated on every row. And nothing in either schema says the webshop customer and the roadshow buyer are the same person. The rule matching them by payment card lives in a documented business rule, written by people and invisible to any scan.
Every one of those facts changes the model or the pipeline, and not one of them was a column.
That walk needs the source system owner in the room. Data teams schedule that role last. So book the owner’s hours when the source enters the register, not on the day their absence blocks a mapping. Run it as a repeatable format: one table at a time, every field named and questioned, the answers written into the catalog rather than into minutes.
The evidence file
Together, the steps above put on record what each source holds, with the gaps marked. That’s the source’s evidence file. Without it, every mapping is a confident guess. With it, the source stops being an assumption and becomes a witness you can cite.
| The people part | Book the source system owner’s hours when the source enters the register, and walk their tables field by field. |
| Metadata to capture | Keep each source’s register entry, inventory on stable identifiers, approved import diffs, profiles, candidates and promotions to rulings. |
| Regardless of stack | The register can be a wiki page, the diff a pull request and the profile a script with a results table, since tooling only makes the discipline cheaper and repeatable. |
In DeltaVault today
DeltaVault, our metadata catalog, ends every import on one review screen, whether the metadata came from a live database, an uploaded query result or a sample file. In its Review changes grid, every table and column is marked Added, Changed, Removed or Unchanged, and a changed row expands to show which fields differ, before and after. When a source drifts, a re-import lands as rows to review, not as duplicates to reconcile. Nothing reaches the catalog until you commit it, and the commit lands every table on your current branch, or none of them.
Data profiling waits for you the same way. A profile run comes back with statistics per column (null percent, distinct count, top values), and you choose Accept or Discard before anything is written.
