In this chapter
- Six patterns, one gap. What each silver pattern does to a source table, and the question none answers.
- Where the AI payoff dies. Why an agent needs each column’s meaning.
- A level, not a gate. The five meaning levels, and which columns must be mapped.
If a model reads meaning stated as metadata, why doesn’t any existing method already state it? The methods aren’t careless. Every one of them states meaning down to the concept, the business thing a table is about. Then it stops, one step short of where an AI agent needs it most.
The platforms ask less of silver than the arguments do
Databricks’ documentation says the silver layer “should always include at least one validated, non-aggregated representation of each record.” Its glossary adds: “From a data modeling perspective, the Silver Layer has more 3rd-Normal Form like data models. Data Vault-like, write-performant data models can be used in this layer.”
Microsoft’s guidance for Fabric lists “Match customer records across systems” without saying how. Snowflake’s documentation gives silver one clause, “a cleaned or conformed dynamic table”. Databricks names two methods and requires neither. Microsoft and Snowflake name none.
So the shape is yours to choose.
The same glossary says how much work silver should take. Its data is “matched, merged, conformed and cleansed (‘just-enough’)”.
Our first book read the glossary’s verbs as four unanswered questions: matched on which keys, merged how, conformed to whose definitions, recorded where. AIMS (AI-ready, Meaning-first Silver) answers them without a method’s shape. It matches on a key you can read and merges only where a specific requirement calls for it. It conforms in meaning by agreeing what each column means rather than reshaping the data, and records that agreement in the catalog. That’s just enough modeling. Microsoft describes its ontology item the same way, as a means to “model business domains without requiring you to reshape the source data solely to fit the ontology.”
Six patterns, judged on what they do to a source table
A canonical third normal form model redesigns the source. Inmon’s integrated warehouse gives every concept one definition and one owner, then bends every source into that shape. It buys integration and a stable interface, and costs the analysis before anything ships.
Data Vault 2.0 splits the source. Linstedt’s hubs carry business keys, links carry relationships and satellites carry context. That buys parallel loading, the audit of every source version and new sources that add without altering. It costs several tables per source table, point-in-time tables to read them back, and automation as a precondition.
Hook wraps the source. Andrew Foad’s method keeps each source table whole, one to one. It adds business key columns. Each is tagged with a label saying which system and context the key belongs to (Hook calls it a key set). And Hook lets nothing exist without a glossary definition. That’s the lowest upfront modeling of any published method, at a cost named below.
dbt’s staging convention renames the source. It puts one light layer over each source table, often a view, renamed, cast and lightly cleaned, and dbt calls this first layer a staging model. It’s cheap, testable and widely known. But it brings no business key discipline. It keeps history only through dbt’s record of what changed between runs, its snapshots, and it defers integration.
A mirror copies the source. It’s typed, renamed, deduplicated, validated and historized, and nothing more. It’s the cheapest silver there is. But there’s no business meaning in it, so every gold model re-solves identity. Our post “Show Me Your Silver Layer” called the result replication: “If silver holds a faithful copy of each source, then everything downstream is replication with extra steps.”
An entity-shaped silver replaces the source. It builds one table per business entity, keyed on the business key and fed by every source. You get one governed record per entity, and you have to decide how to integrate before anything ships. Every feeding source needs a mapping first, and a rule that says which source wins when two disagree, a survivorship rule.
Two unpublished research documents prepared for the design of AIMS shaped it: a cited survey of the published methods and an advisory synthesis for practitioners. Both were inputs to the specification behind AIMS, which restates them. From different directions, both reached the same default, source-aligned tables.
Hook got closest, and we credit it
Of the six, Hook is the pattern we owe the most, and the one a careful reader will suspect we renamed. It’s the only published method that formalizes an ontology over source-shaped data.
In Foad’s words, a key set is “a qualifier that is tagged onto a business key, making it unique,” and the rule is absolute: “Each and every business key must, must, MUST be qualified with a keyset.” No restructuring and a qualified key on every row, gated by the glossary, is the instinct AIMS starts from.
Its limit is the one both research documents name. Integration in Hook means joinability, not resolved entities, where one combined record stands for each customer. Two key sets that identify the same customer stay two keys, and the columns describing that customer are never consolidated. So AIMS took the identity half and made the business key first-class metadata, written with its record source code in front. Then a customer’s awlt||AU||00001042 is generated and validated. And two record sources never join until an approved match says they describe the same thing.
“So this is Hook with new names.” The identity half shares Hook’s instinct, and we’d rather credit it than disguise it. Around it, AIMS adds what Hook leaves to the team: column meanings defined once, a code list behind every code, how much history each table keeps (its history policy), a person on every change of meaning or values, and a data contract per table saying what one row is.
What does every silver layer modeling method leave empty?
A silver table has to answer four questions. Which source record is this row? Which business thing is it about? How does it relate to the others? What does each column mean? The patterns that keep the source’s shape answer the first. All but dbt’s convention and the mirror answer the second. The canonical model, the vault and Hook answer the third. The fourth column is empty in every row but one. Only the canonical model fills it, and it pays by rewriting every source before anything ships.
Figure 3.1: the empty column. Six patterns against the four questions, the fourth column empty in every row but the canonical model’s.Take one attribute from the worked example. The order system’s Customer table has a Name column, and the sales system’s Account table has an AccountName column. Both mean Customer Account Name. No pattern short of the canonical model has a place to say so. AIMS gives it one: a business attribute, defined once, that both columns are tied to. The tie is called an attribute mapping. Without it, the gap costs you in six places.
- Gold re-derives the customer’s name per model. Every report decides for itself which column holds it.
- Cross-source joins work and cross-source comparison does not. Identity lets you join the two systems, but units, currencies and codes stay unaligned.
- Sensitivity is classified per column. Classification and masking belong to the attribute, so mapping once should mask everywhere.
- Business-level lineage has no edge to follow. “What breaks if Customer Account Name changes?” needs the column-to-attribute edge.
- Descriptions and rules are rewritten per column. Written once on the attribute, they should travel to every mapped column.
- Marts start from column names. Dimensions and facts could be drafted from attribute roles (identity, descriptive, measure) if the roles existed.
This is where the AI payoff dies.
Ask an assistant how many customer accounts you lost last quarter. It answers because the status column is mapped to a business attribute with a code list. Take the mapping away and it’s guessing from column names again.
A level, not a gate
Neither research document left the fourth column empty by accident. Agreeing what every column means across sources is where the effort of a canonical model lives. Require every column mapped before anything ships and you’ve rebuilt the upfront cost both set out to avoid, one column at a time. So what fills the empty column without paying that cost again?
We chose a level, not a gate.
Some columns carry weight, because a join, the reading of a code or a governance rule depends on what they mean. They identify a record, point to another record, hold a code or hold sensitive data. AIMS calls these four classes of column critical (identity, relationship, code and sensitive). For them, mapping is mandatory. A column that carries identity but has no mapping can’t be joined safely. A code with no code list is a value nobody can interpret, and an unclassified sensitive column is a governance failure.
Everywhere else, mapping is measured as coverage, one visible number per table and per entity, and low coverage warns rather than blocks.
And mapping is cheaper now. That’s because an AI proposes every column’s match, with its evidence, for a person to review.
AIMS names five meaning levels. Each says how much a table tells its reader, person or machine.
- Level 0, Typed. Typed, deduplicated, tested and historized, but silent about what its rows mean. This is replication, and it never publishes.
- Level 1, Identified. Tied to its entity, with a sentence saying what one row is (its grain sentence), a readable business key and a history policy. This is Hook’s level, the floor.
- Level 2, Related. Every reference to another business thing is tied to it, with its role, how many rows can match and what happens when the referenced row is missing.
- Level 3, Described. Each column is mapped to a business attribute or marked as having no business meaning, with its kind of value, classification and code list. Critical columns must reach it.
- Level 4, Integrated. A shared key across the entity’s record sources, then a golden record (one combined record per business thing), built by exception and never as a gate.
Publishing means gold and agents may read a table. A table publishes at level 2 complete plus level 3 for its critical columns, once it passes its acceptance checklist of identity, values, history, rules and governance. So AIMS conforms in meaning, on tables that keep their source’s shape.
| The structural answer | A canonical model fills the empty column by rewriting Name and AccountName into one Customer Account Name before anything ships, and a vault half-fills it later with a hand-designed satellite of conformed values, a business satellite. |
| What the agent reads | Nothing new yet, but the missing edge from column to business attribute is the one an agent needs most, because it turns two columns into one comparable fact. |
| Yours to decide | Whether a project, the unit of work a set of tables is organized and generated in, widens the four critical classes, for example to make every monetary amount critical, since the four are only the floor. |
In DeltaVault today
DeltaVault, our metadata catalog, holds business attributes. Its Map & Match workbench suggests an attribute match for every column of a table. Each suggestion carries a confidence score and a written rationale, and an accepted match keeps both.
The screen below shows the Customer table from Microsoft’s AdventureWorks sample database, tied to the Customer entity. A Mapped column is tied to a business attribute, such as CustomerID to Customer Identifier. A Described column has a business name and a description, but no attribute yet. It’s short of meaning level 3.
