Data Vault Interview Questions: Business Keys, Hubs, Links, and History You Can Rebuild

Data Vault interview questions often sound like a vocabulary quiz: define a hub, a link, and a satellite. The revealing follow-up is harder: two systems both call a customer C-17, an order changes owner, and an attribute correction arrives late. Can your model preserve what was received, avoid merging the wrong entities, and reconstruct a defensible answer for a chosen point in time? If not, naming the three table types is not enough.

This guide works through one original CRM/ERP fixture. Evidence boundary: definitions and load patterns are grounded in AutomateDV's Data Vault tutorials; the source rows, SQL, and interview rubric below are editorial examples. Data Vault implementations differ in hashing, source-key harmonization, and timeline design, so state your assumptions rather than presenting one package's convention as a universal law.

Start with the identity question, not a hash function

Assume a CRM sends customer_id = C-17 for Alice, while an ERP sends customer_id = C-17 for Acme Supplies. A naive hub keyed only by the text C-17 merges two people or organizations. A hash of the bare string merely makes the collision less readable; it does not solve the business problem. First ask whether the two identifiers share a common issuing authority and refer to the same kind of entity. If they do not, define a source-scoped business key such as (source_system, customer_id) or a curated enterprise identity mapping. Do not infer sameness from equal strings.

A hub holds distinct business keys for an entity, with metadata such as load timestamp and record source. Its hash key, when used, is a stable technical representation of the agreed business-key columns. A deterministic normalization rule must be documented: how are case, whitespace, nulls, and delimiters handled? Changing normalization later can change identity, so test it with real source values. A business key that is mutable in a source, like a display name or email, is usually a poor identity foundation without an explicit re-keying policy.

What if the organization later proves CRM C-17 and ERP C-17 refer to the same real customer? Add a governed mapping or same-as relationship with provenance and effective dates. Do not silently rewrite the raw hub history. The vault's value is that a reviewer can distinguish original source assertions from later identity resolution. This is the first strong interview answer: which business fact identifies the thing, and who owns that assertion?

A small fixture you can reason through

The following rows are invented for practice. effective_at is the source's claimed business time; loaded_at is when the warehouse learned it. The source may not always provide a trustworthy effective time.

Row	Source	Business event	Effective at	Loaded at
A	CRM	Customer C-17, name Alice	Mar 1	Mar 1
B	ERP	Customer C-17, name Acme Supplies	Mar 1	Mar 2
C	CRM	Order O-9 belongs to CRM C-17	Mar 4	Mar 5
D	CRM	Customer C-17, corrected name Alicia	Mar 3	Mar 7

At the end of Mar 4, the warehouse had only row A for this CRM customer. After Mar 7, it knows that the source claims “Alicia” was effective from Mar 3. Both statements can be true. The first is an as-known-then answer; the second is a business-effective as-of answer with later knowledge. A model that overwrites Alice with Alicia loses the ability to explain the difference. A model that keys both source systems as one customer produces the still worse answer “Acme Supplies” for a CRM order.

Write down expected outcomes before designing tables. The fixture should produce two customer hub keys, one order hub key, one CRM order-to-CRM customer link, and at least two CRM customer attribute versions. The ERP record must never become the owner of O-9 merely because its natural-key text matches. When an interviewer asks for “the customer name on Mar 4,” ask whether they mean information available on Mar 4 or information now believed effective on Mar 4.

Put associations in links and changes in satellites

A link records an association between business keys. Here, the order-to-customer relationship has a link hash or key built from the order identity and the source-scoped customer identity. The link is not a place to keep the customer's changing name. If the owner changes, preserve the newly observed association; do not overwrite the original link without a history/effectivity strategy. An effectivity satellite can help record when relationships are considered active or inactive, but its driving-key and source rules must be explicit.

A satellite carries descriptive attributes and change history for a hub or link. A customer satellite can hold name, status, source, load timestamp, and a hashdiff used to detect changed payloads. The record source and load timestamp are not decoration: they explain where a value came from and when the platform learned it. If CRM corrects a name, the vault can append a new satellite row rather than erase the old one. If the same payload is resent, a deterministic hashdiff plus source/order logic should prevent a duplicate version when the agreed load contract says it is the same observation.

Do not promise that every changed field belongs in one satellite. Group attributes by source and rate of change where useful; keep semantics and lineage clear. Nor should every source correction be treated as “customer changed on load date.” Row D demonstrates why business-effective time may be distinct from warehouse load time. If the source does not give reliable effective time, say what you can answer: an as-known-at timeline from load metadata, not a fabricated business-state timeline.

Show an incremental load, not just a diagram

An interview-ready load explanation can be compact. Stage source rows with a stable source identifier, raw business keys, payload, effective time if present, ingestion/load time, and a record source. Normalize and hash the agreed business-key columns consistently. Insert unseen customer and order keys into their hubs; insert unseen order-customer pairs into the link; insert changed customer attributes into the satellite. Make the load idempotent under replay of the same batch. Track rejected or malformed rows rather than dropping them silently.

One possible pseudo-SQL shape is:

-- Illustrative; adapt hash syntax, merge semantics, and null rules to your warehouse.
customer_bk = source_system || ':' || customer_id
order_bk    = source_system || ':' || order_id

WITH candidate AS (
  SELECT customer_bk, MIN(loaded_at) AS first_seen_at,
         MIN(source_system) AS record_source
  FROM staged_customers
  GROUP BY customer_bk
)
INSERT INTO hub_customer (customer_hk, customer_bk, loaded_at, record_source)
SELECT hash(s.customer_bk), s.customer_bk, s.first_seen_at, s.record_source
FROM candidate s
WHERE NOT EXISTS (
  SELECT 1 FROM hub_customer h WHERE h.customer_hk = hash(s.customer_bk)
);

This deliberately leaves two review questions visible. First, hash() is not a portable SQL function or a magical collision-proof identity; specify algorithm, encoding, and normalization in a real implementation. Second, concurrent loaders can both see “not exists,” so a production load needs a uniqueness constraint, transactional MERGE, or a single-writer contract. The point is to expose the invariant: one hub row per governed business key, even if the source sends the same key again.

For a satellite, compare each new payload with the latest prior payload for the same hub, source, and chosen record-ordering rule. AutomateDV's hashing guidance explains why hashdiff column selection and ordering matter. A hashdiff should detect payload change; it should not be asked to decide whether two business keys mean the same real-world entity. That is a separate identity decision.

Rebuild two different historical answers

Assume the model preserved both effective_at and loaded_at for the CRM satellite. A query asking, “What name did we know by the end of Mar 4?” filters to rows loaded by Mar 4. It finds Alice. A query asking, “With everything loaded by Mar 8, what name does the source say was effective on Mar 4?” filters to effective_at <= Mar 4 and loaded_at <= Mar 8, then chooses the greatest effective time, with a deterministic tie-breaker. It finds Alicia. An as-of query should state the knowledge cutoff as well as the business timestamp when late corrections exist.

For a single customer, the selection is conceptually:

SELECT name
FROM (
  SELECT name,
         ROW_NUMBER() OVER (
           ORDER BY effective_at DESC, loaded_at DESC, source_row_id DESC
         ) AS rn
  FROM sat_customer
  WHERE customer_hk = :crm_customer_hk
    AND effective_at <= :business_as_of
    AND loaded_at <= :known_by
) s
WHERE rn = 1;

The tie-breaker matters when corrected records share an effective time. For a real multi-row warehouse query, partition the window by hub key; for a relationship that can end or change, apply its effectivity rules too. This simple query is not a complete universal Data Vault point-in-time implementation, but it forces the candidate to articulate the two clocks and expected rows. You can validate the fixture with four assertions: CRM and ERP C-17 have different keys; O-9 joins only to CRM C-17; the Mar 4/Mar 4 answer is Alice; the Mar 4/Mar 8 answer is Alicia.

Common follow-ups and defensible answers

“Why not just use a dimension table?” A dimension can be the appropriate serving model for reporting, but it solves a different downstream problem. A vault is useful when preserving source identity, lineage, relationships, and history under evolving sources matters. Build a dimensional mart from the vault when analysts need business-friendly grain and measures; do not force every dashboard to traverse raw hubs, links, and satellites. Conversely, do not call a star schema a vault by renaming dimensions as hubs.

“What if the source key is reused?” Then the source's key is not globally stable across time. You may need a source-provided generation, tenancy, effective interval, or additional discriminator. If none exists, flag an identity ambiguity. Do not quietly combine two lifetimes and claim exact history. “What if a relationship is deleted?” Distinguish deletion from absence in an incremental feed. A missing row in a daily delta is not proof that the relationship ended; a full snapshot or explicit end event can establish different semantics. State the source contract before choosing an effectivity satellite.

“Can we rebuild history after a backfill?” Only if the input evidence needed for that history was retained and load rules are deterministic enough to replay. A hash cannot recover overwritten source values. Test replay by loading the same batch twice and comparing row counts, then loading rows in different arrival order and checking whether the intended as-known and business-effective answers remain interpretable. This is where Data Vault becomes a recovery discipline rather than a drawing convention.

Sources and further reading

• AutomateDV: hubs — business keys and hub metadata.
• AutomateDV: links — modeled associations.
• AutomateDV: satellites — descriptive payload and change tracking.
• AutomateDV: effectivity satellites — relationship effectivity patterns.
• AutomateDV: hashing best practices — deterministic key and hashdiff considerations.