In this chapter
- The source register: connections, capabilities, and what each source cannot tell you.
- Metadata import as a reviewed change, because schema drift never stops.
- Profiling and key discovery: observations, held apart from decisions.
- Reading a source honestly: witness, not author.
Chapter 3 closed by promising to put the witnesses on the stand, and this chapter is the cross-examination. The conceptual model records what your business means. Now you find out what your systems actually hold, and the distance between those two answers is exactly the work the silver layer exists to do.
Every data project starts the same way, whether anyone dignifies it with a name or not: by proving you can read the source. Can you connect? Can you list the tables? Can you pull the columns and their types? And, just as important, what can this particular source never tell you? Teams treat all of that as throat-clearing before the real work begins. It is the real work, and the teams that skip writing it down get to do it again every sprint.
A register, not a pile of connection strings
No two sources answer questions the same way. A live database connection can hand you tables, columns, types, primary keys, foreign keys, and views. A nightly file drop gives you column headers and whatever you can infer from the rows. Some sources carry table and column descriptions, but most arrive with those fields empty. Every connection, then, has a capability profile: what it can tell you, what it cannot, and what you will have to learn some other way.
The source register is where that profile lives, one entry per source: how you connect, who owns the system, when extracts arrive, which capabilities the connection supports, and the limits you have already discovered. It is deliberately unglamorous, and 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, and in July someone burns two days learning it again. An unwritten source limit is a recurring cost booked as a surprise, every time.
An inventory that survives a rename
Once you can read a source, what you read goes into a catalog: every table and every column, held as an inventory. That inventory only earns its keep if other artifacts can point at it. Mappings reference these columns and rulings attach to them, so every entry needs an identifier that stays stable while everything around it changes.
Names are the wrong identifier for that job. Columns get renamed, tables move between schemas, and the same logical source turns up under different names in development and production. A name-keyed spreadsheet does not object when any of that happens; every reference into it simply breaks, and it breaks without an error message. So the inventory keys on stable identifiers, and the names hang off them as facts that are free to change. Identifiers carry identity, names are only labels. The idea returns at full strength in chapter 9, when the metadata itself goes under version control; it enters the book here because the inventory is the first artifact the others point at.
Import is a diff you approve
A source inventory would be a one-time chore if sources held still, and they do not. Columns appear, types widen, a table splits in two, and none of it waits for your review meeting. Drift is not an incident; it is the weather.
That is why a catalog refresh is a staged, reviewable change rather than a reload. The import lands as a proposal: these tables are new, these columns changed type, these disappeared. A person reads the diff, approves it, and the catalog moves forward with its history intact. The alternative, truncate-and-reload, fails twice at once. It destroys everything you attached to the inventory, every description, mapping, and ruling keyed to those entries, and it waves drift straight through, importing every surprise as silently as the source produced it. A dropped column should be a decision you make with the diff in front of you, not something you discover downstream when a report goes blank.
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 runs on Willibald, the garden webshop from chapter 3. Its metadata arrives as exactly the export in
the figure: 72 rows describing ten tables, from Bestellung (the order header) down to Wohnort (a customer’s places
of residence over time). The file is honest about structure, and it can prove that Bestellung.KundeID is a
thirteen-character foreign key to Kunde, and that Position hangs off Bestellung by a composite key. It is just as
honest about meaning, though in the other direction: the export has a description field for every table and every
column, and all 72 of them arrive empty. The source can tell you precisely how the order data is shaped and nothing at
all about what an order is.
What does data profiling tell you that the schema will not?
The inventory says what the source claims about itself. Data profiling checks those claims against the data: null rates, distinct counts, ranges, and top values per column, with candidate keys proposed where values actually run unique and candidate relationships proposed where value sets overlap. None of this is new discipline. Kimball and Caserta gave profiling a named place in the warehouse back in 2004, in their book on extracting, transforming, and loading warehouse data, and it says something about the industry’s attention span that a twenty-year-old book is still the last mainstream text to treat it as one. It 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 ships with exactly the failures the discipline exists to catch: product rows duplicated in the extract, a table whose business key simply is not there, and order numbers from an earlier load period reused in a later one, the kind of key collision that never shows up in a schema and always shows up in a join. A profile run finds each of these as a measured fact, with numbers attached.
What you then do with a measured fact is what decides whether your catalog stays trustworthy, and the essential split is
that observations are not decisions. A profile run observing that Position.PosID never repeats is evidence; declaring
the pair of order and position the key is a ruling. Evidence accumulates automatically and goes stale harmlessly, while
rulings are agreed by people and carry authority. Keep the two apart, in separate places with separate lifecycles, and
record the promotion: who promoted which observation into which ruling, and when. A catalog that files observations
directly as facts has automated its own gullibility, because the profile of one bad extract becomes policy without
anyone deciding anything.
The witness tells the truth, but only about itself
There is a deeper limit underneath all of this, and it is the one chapter 3 flagged. A relational schema tells you how an application chose to persist something, not what that something means: your database is not your business. Surrogate keys, junction tables, status codes, audit columns are implementation decisions, made under a deadline, often by a vendor who never met your business. Read a schema as testimony about the application and it is excellent evidence. Read it as testimony about your business and you are quoting a witness on questions it never saw.
Your conceptual model will keep colliding with that limit. The model says a customer has a delivery address, while 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, and writing it down is precisely the mapping work of chapter 5. Neither side is wrong here. They are simply answering different questions.
Figure 4.2: the clean noun on your diagram turns out to be a join and a
filter.So the last source-understanding instrument is not a query at all. It is a
conversation with the people who run the process, and the Willibald team shows
what one is worth. In a twenty-five minute walk through the 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, and early
delivery is not automatically good, because plants can arrive before the
customer is ready for them. The order discount reads as an amount deducted
from the total, but the source carries no discount-type column, so the room
records the reading and files a validation question instead of letting the
guess harden. No order status is stored anywhere either: open and delivered
turn out to be derived states, read off the positions and deliveries. The
roadshow table is named like an order table and is nothing of the kind, since
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 that matches them through
their payment card lives in a documented business rule, written by people,
invisible to any scan of the source.
Every one of those facts changes the model or the pipeline, and not one of them was a column.
The people part. That meeting only works if the source system owner is in the room, and they are the role data teams schedule last. Chapter 2’s grid puts them at the core of bronze and contributing at silver, so book their hours when the source enters the register, not on the day their absence blocks a mapping. The interview itself is a repeatable format: one table at a time, every field named and questioned, with someone writing the answers into the catalog rather than into minutes.
What the stand produced
Metadata to capture. Every output of this chapter is recordable: the register entry per source with its capability profile and limits, the table and column inventory keyed on stable identifiers, each approved import diff, the observed profiles, the candidate keys and relationships still held as candidates, and the promotion record that moves an observation to a ruling. Taken together, they form the evidence file for every source you touch.
Regardless of stack. Nothing above named a platform. Your register can be a wiki page, your diff can be a pull request, and your profile can be a script plus a table of results. Tooling makes each step cheaper and repeatable, but the discipline is what makes any of them worth doing.
The conceptual model from chapter 3 states what the business means, and the evidence file from this chapter states what the sources actually hold, on the record, with the gaps marked. Chapter 5 is where the two get joined through the mapping that ties each source column to the meaning it feeds. Without the evidence, that mapping is guessing with confidence behind it. With the evidence, the mapping becomes the most valuable document your team produces.