In this chapter
- A key needs its record source. The system a key is unique in, written into the key with its rules.
- A key you can read. One plain-text column per role, record source code first, and why not a hash.
- The key register. Every identity an entity has, and why a changed key is a one-way door.
A business key looks like the plainest column in a table. But it packs in the decisions every integration effort ends up arguing about: which system a key came from, what it’s unique within and how its parts are cleaned. AIMS (AI-ready, Meaning-first Silver) writes each of those decisions down.
Why does a business key mean little without its record source?
As our post “Where, Oh Where, Has Integration Gone?” put it, “Integration becomes real only when the agreement is written into a key that every load has to honor.”
A key alone doesn’t say where it’s unique. In the worked example, the order system’s customer number is unique only inside a company code. So the same number under a second company code is a different account. The sales system’s account identifier is unique system-wide, with no company code. Join the two systems on their customer columns and you’re betting they identify customers the same way. They don’t.
A key without its record source is a false join waiting to happen.
A business key carries its record source, with its rules
A business key is the set of values the business uses to identify one thing, in a fixed order, such as a company code and an account number. It’s unique only inside the system that issued it, its record source. Written out in a column, the business key starts with the record source code, such as awlt for the order system. Data Vault calls that prefix a business key collision code, and some tools call the whole value an integration key.
Each key part is a column or a fixed value. The key belongs to one entity and states the rules its parts follow.
- Normalization cleans a value before it identifies anything: whitespace trimmed and collapsed, upper-cased, and padded to the part’s own width. So 1042 and 00001042 are one account.
- The separator is a setting every key shares. It joins the parts of a composite key, one between every part. Without it, the parts 12 and 345 and the parts 123 and 45 would make the same key.
- The null rule says a missing part makes the identity unknown, never an empty string. An empty string is a value, and two customers with a missing number would quietly become one.
An entity has one business key per record source. So the order system’s customer key is one business key wherever it appears, on customers and on every order’s sold-to and bill-to. A business key per source table would multiply keys one system already shares. Two record sources never join until an approved match says they describe the same thing.
Isn’t that the mistake our post “How raw is your Data Vault?” warned against, a vault in which “each hub keys on whatever its source happened to key on”? No, because a person decides the business key rather than a constraint implying it, and the record source code on every value names its system.
The agreement across systems gets its own shared key (customer_account||tax_number||48123456789 in the worked example) only when a business question needs one record across both. When that key arrives, nothing re-keys, because Sales Order keeps pointing at awlt||AU||00001042.
The business key value spells out where it came from
The business key column carries the key physically, one generated column per role on every source-aligned table. It holds the record source code, then each key part in order, all joined by one separator. For the worked example’s customer, that’s awlt||AU||00001042.
We made the column physical and the value plain text on purpose. A person can read it in a query result, and so can an agent with nobody beside it. Why not derive identity at query time? Every join and orphan check would recompute the key.
Why not a hash? A hash buys a fixed width, so joins on it stay narrow. But a hash of awlt||AU||00001042 is a run of hexadecimal digits. It names no system, no company code and no customer, and nothing reads it back into them. The readable value answers on sight. Long keys make wide columns, a cost we accepted.
Every role carries its own key
Every source-aligned table carries one business key column for its own identity and one for every entity it refers to, in each role. The sales order table carries three:
- Its own key,
awlt||AU||SO-000123. - The sold-to customer,
awlt||AU||00001042. - The bill-to customer, the same account or another, from the same record source.
A role with its own column gets its own referential rule and orphan count, so a missing bill-to account never hides inside a healthy sold-to figure. It also makes the join executable. The sold-to column already carries the company code beside the customer number.
A dependent entity’s key starts from its parent. A Sales Order Line is its order’s identity plus a line number.
The key register holds every identity an entity has
Every sold-to key needs one place to find its customer, whichever system the customer came from. That place is the key register, every distinct business key of one entity across its record sources. Customer Account’s register unions the order system’s customers and the sales system’s accounts. So awlt||AU||00001042 and wb||A-937 are two rows. They stay two rows after a match says they’re one account, and both rows carry the same shared key. It’s built from the start, a view by default, for three jobs.
- It’s the referential rule’s target, where a sold-to key must find its identity.
- It counts the orphans, every reference with no matching identity, per role.
- A dimension reads it as its natural key in the mart.
A changed business key is a one-way door
When a business key changes (a new part, a new width, a different column), every value built from it changes. Every match a person typed for one of those values changes too. So in AIMS, nobody accepts a changed key without a stated migration of its values and those matches. That’s one reason the business key sits above the approval boundary, where a person approves every decision that changes meaning or values.
| The structural answer | A Data Vault carries the key and its source as a hash key and a record source column, and a hash says nothing until it’s joined back to its hub. |
| What the agent reads | Each business key’s record source code, parts and padding, the business key column for every role, each entity’s key register and each role’s orphan count. |
| Yours to decide | Every business key, its parts and their padding, and the migration when a key changes. |