Business Key Collision

A Data Vault's hub is a list of business keys. A platform integrates a CRM, a billing system and a support tool, taking customer_id from each as the business key for the customer hub. Billing's customer 1024 is a manufacturing firm in Leeds; the support tool's customer 1024 is a private individual in Cork. They are now one hub row, their satellites interleave two unrelated histories, and every link off that hub ties one company's invoices to another's support tickets.

No constraint was violated and no load failed. The vault did what it was told: treat equal strings as the same entity. The error is upstream of the model, in the claim that a source-local identifier is a business key.

Why it matters

The whole proposition of a vault is that business keys are the stable spine around which sources come and go. If the spine is wrong, the integration is not merely incomplete - it asserts false relationships, and every mart built on it inherits them.

Detection is the problem. A collision produces plausible data: a customer with slightly odd attributes, a support ticket on an account that never opened one. It surfaces months later as a finance query that will not reconcile, or as a privacy incident. Unpicking it means rebuilding the hub, re-keying links and satellites and deciding what to do about published marts - rarely under 10 days of work. The frequency is not marginal: two systems both issuing sequential integers from 1 collide across their whole shared range, so a source of 50,000 customers meeting one of 200,000 produces about 50,000 colliding keys - a quarter of the larger set.

Implementation patterns

• Qualify the key with its source when it is not globally unique: billing|1024. Ugly and correct, and the default position until proven otherwise.
• Prove uniqueness before choosing a key. Count distinct keys per source, count entities sharing a key across sources, inspect the overlaps by hand. One query prevents the whole class.
• Prefer an identifier the business transacts on - a tax number, an ISIN, a company register code - over a database sequence, because those are already unique outside your systems.
• Use a same-as link when two qualified keys turn out to be one entity, rather than merging hub rows, so the merge is a recorded assertion that can be reversed.
• Hash the qualified key, never the raw one, or the hash undoes the qualification.
• Guard the load: a satellite row whose name, country and tax number all differ from the previous version is a collision, not a change.

Industry example

Data Vault 2.0, published by Dan Linstedt from 2013 onward, is explicit that the business key must be the enterprise-wide identifier and that source-system surrogates are not business keys - the method's integration guarantee depends on it. The failure is routine anyway, because customer_id is the column teams reach for and the load succeeds. The same shape appears in any master-data or customer-360 build that unions identifiers from several systems without qualifying them.

Failure scenarios

• Two entities merged, with interleaved satellites producing a history that oscillates between two sets of attributes on every load.
• Links asserting relationships that do not exist, so one customer's transactions appear on another's statement.
• A privacy breach, where a subject access request or a deletion runs against the wrong person's data.
• The reverse error: one entity split across many hub keys because no same-as link was ever built.

Trade-offs

Qualifying keys is safe and costs integration: every cross-source question then needs a same-as link, and building those links is the real work rather than a side effect of a naming coincidence. The asymmetry decides it: an under-integrated vault is visibly incomplete and fixable with a link; an over-merged vault is invisibly wrong and expensive to reverse.

When not to use it

The concern does not apply when the key is globally unique by construction - a UUID, a tax identifier, a regulator-issued code - where qualifying it adds noise and no safety. It is also out of scope for a single-source warehouse: with one system of record the source identifier is the business key.

Interview question

Q: "You are modelling a customer hub across three systems that each have an integer customer_id. Before you write any load code, what do you check, and what would make you qualify the key by source even though it makes cross-source queries harder?"

What a strong answer covers: the cross-source uniqueness check and that overlap is the norm; qualification as the default with same-as links as the deliberate integration step; the asymmetry argument; the privacy consequence of a merge; and the one condition that justifies an unqualified key - an identifier issued outside the organisation.

Quick check

Quiz: Two sources use customer_id 1024 for different companies and both load into one hub. What is the first visible symptom? Usually none for months - then a satellite whose attributes flip on every load, or a reconciliation that fails because invoices and tickets belong to different companies.

Flashcard: Why is a source system's customer_id usually not a business key? — It is unique only inside that system, so loading two systems into one hub merges unrelated entities with no error; qualify the key by source or use an externally issued identifier.