Module 02: Core Modeling Concepts

Concept 01

The 9 DV2 Modeling Principles

🧠 ELI5 — Explain Like I'm Five

Dan Linstedt formalized 9 rules that every Data Vault design must follow. Think of them as the "constitution" of DV — they exist to guarantee auditability, agility, and parallel loading. No rule is optional. If you bend one, you no longer have a Data Vault.

All 9 Principles Explained

These principles are non-negotiable. Every table, every load pattern, every design decision must satisfy all nine simultaneously.

#	Principle	What it means in practice
1	All tables must have a Load Date (LDTS) and Record Source (RSRC)	Every single row in every Hub, Link, and Satellite carries a warehouse arrival timestamp and a source system identifier. No exceptions. These two columns are the foundation of auditability.
2	All Hubs must have a business key and only a business key	A Hub stores the natural business identifier and nothing else. No names, addresses, statuses, or any descriptive data. Those belong exclusively in Satellites. Violation of this rule is the most common DV design mistake.
3	All Links must reference only Hash Keys from their participating Hubs	A Link joins Hubs together using their Hash Keys as foreign keys. Links never store raw business keys directly — only the derived Hash Keys. This ensures referential integrity across the entire vault.
4	All Satellites must reference their parent Hub or Link via Hash Key	A Satellite hangs off exactly one parent: either a Hub (describing an entity) or a Link (describing a relationship). The connection is always through the Hash Key, never through raw business keys.
5	Satellites store all descriptive attributes and history	Every piece of descriptive, changing, or contextual information lives in Satellites. One row per version of the data, each with its own Load Date. This automatically produces complete, timestamped history.
6	All data is insert-only — no updates, no deletes, ever	The Raw Vault is immutable. New data is appended as new rows. Historical rows are never changed. This is what makes the vault a permanent, tamper-evident record of what every source system ever said.
7	All load operations must be idempotent (safe to run twice)	Running the same load job twice must produce exactly the same result as running it once. No duplicate rows, no errors. The HashDiff pattern on Satellites and NOT EXISTS checks on Hubs/Links enforce this.
8	Business keys define the grain of a Hub — one BK = one row	Each unique business key value gets exactly one row in its Hub, ever. Once loaded, a business key row is never moved, split, or merged. This fixed grain is what makes the vault a reliable integration anchor.
9	All relationships are stored in Links — never in Hubs or Satellites	If two entities are related, that relationship belongs in a Link table. Hubs and Satellites must never contain foreign key columns that express relationships. This strict separation is what allows independent, parallel loading of all vault objects.

💡

The DV litmus test: If a design choice violates any of these 9 rules, it's not Data Vault 2.0 — it's something else. Use this checklist on every table you design, and review it again before every sprint review.

Concept 02

Business Keys — The Foundation

🧠 ELI5 — Explain Like I'm Five

A Business Key is the real-world identifier your business uses for something — not a database-generated surrogate. The CustomerID in the CRM. The ISIN code for a financial instrument. The Part Number in the ERP. This is the most important concept in all of Data Vault. Get this wrong and everything built on top of it is wrong.

What Makes a Valid Business Key

A business key must satisfy all four of these criteria simultaneously:

Used by the business, not just IT — Business users, operational staff, or partner systems refer to this identifier. It appears in contracts, invoices, reports, or communications. It has meaning outside the database.
Relatively stable over time — The key doesn't change frequently. Customer numbers may stay the same for decades. A column that changes every month is not a reliable BK.
Unique within its domain — No two distinct real-world entities share the same BK value within the same source system context. Uniqueness may be partial (unique within region, within year) — in that case you need a composite BK.
Meaningful to the source system — The source system can look this entity up using this key. It's how the system identifies the entity, not just an internal tracking number.

What is NOT a Valid Business Key

Database sequence numbers / auto-increment PKs — These are generated by the database engine and have no meaning outside that specific database instance. If the database is rebuilt, the numbers change.
Row IDs (ROWID, OID) — Physical storage identifiers. Meaningless after any data movement or reload.
Surrogate keys generated during ETL — If your ETL tool generates a key that the business never sees, it's not a business key.
Foreign keys that point to another surrogate — A FK to a database-generated PK is still not a BK; it's a database artifact.

Composite Business Keys

Sometimes no single column uniquely identifies an entity — you need a combination of columns. This is a composite business key. Common examples: ORDER_DATE + ORDER_NUMBER in legacy ERP systems that reset the order counter daily. PLANT_CODE + MATERIAL_NUMBER in SAP where the same material can exist in multiple plants with different codes.

When hashing a composite BK, always concatenate the component columns with a consistent separator before hashing. The separator must not appear in any component value, or use a delimiter unlikely to collide (e.g., '||').

BK Standardization in Staging

Before a business key is hashed into a Hash Key, it must be standardized in the staging layer. Inconsistent normalization across loads is one of the top causes of phantom duplicates in Data Vault implementations.

SQL — BK Normalization in Staging

-- Staging: normalize business key before hashing
SELECT
    UPPER(TRIM(COALESCE(customer_id, 'UNKNOWN'))) AS CUSTOMER_BK,
    -- Always handle NULLs: a NULL BK would hash to the same value
    -- for every NULL row, causing catastrophic data collisions
    UPPER(TRIM(COALESCE(order_date,   '')))     AS ORDER_DATE_BK,
    UPPER(TRIM(COALESCE(order_number, '')))     AS ORDER_NUMBER_BK
FROM source_crm.customers
WHERE customer_id IS NOT NULL  -- hard filter: never load NULL BKs
  AND TRIM(customer_id) != '';  -- also filter empty string BKs

⚠

Critical warning: Never load a NULL business key into a Hub. A NULL BK would hash to the same value as every other NULL BK, causing all entities with missing keys to collapse into one Hub row. Filter with WHERE BK IS NOT NULL in staging — this is a hard stop, not a suggestion. Also filter empty strings: an empty-string BK is just as dangerous as NULL.

BK discovery tip: When analyzing a new source system, ask the business team: “If you had to look up this entity in the source system without logging into the database, what would you type?” Their answer is almost always the business key.

Concept 03

HashKey Generation

🧠 ELI5 — Explain Like I'm Five

Instead of using database-generated identity columns (which break across systems), Data Vault hashes the business key to create a deterministic surrogate key. Same business key = same hash, every time, from any system. This enables parallel loading without coordination — you can calculate the Hash Key in staging on any server and know it will match what's already in the Hub.

Why Not IDENTITY or SEQUENCE Columns?

Database-generated surrogate keys have three fatal problems in a Data Vault context:

Non-deterministic — The value depends on when the row is inserted and the state of the sequence counter. Rebuilding a table produces different surrogate values, breaking all foreign key relationships.
Cannot be pre-calculated in staging — You can't know the surrogate value before the row is inserted, so staging SQL cannot reference it. HashKeys can be computed in a single SELECT before any INSERT occurs.
Contention on parallel loads — Sequence generators are a serialization bottleneck. Multiple parallel loading threads compete for the next sequence value, destroying the parallel loading advantage DV is designed to deliver.

MD5 vs SHA-256

Both are valid hashing algorithms for Data Vault. The trade-off is between speed and collision resistance:

MD5 (32 hex characters / 16 bytes) — Faster to compute, smaller storage footprint, widely supported. Theoretical collision risk is extremely low for business key values. Adequate for most implementations. The standard choice in many enterprise DV deployments.
SHA-256 (64 hex characters / 32 bytes) — Cryptographically stronger, zero known collisions for practical inputs, preferred for financial services, regulatory environments, and high-volume vaults with billions of rows. The collision probability at scale justifies the extra storage cost.

Recommendation: Use SHA-256 for any environment with regulatory audit requirements (banking, insurance, healthcare) or expected row counts exceeding 1 billion. Use MD5 elsewhere for simplicity.

The Hashing Formula

HK = HASH(UPPER(TRIM(BK))) — always normalize before hashing. The normalization step is not optional: if one load process uses UPPER(TRIM()) and another does not, you will generate two different hash values for the same business key, creating phantom duplicate Hub rows.

For composite keys: concatenate component values with a consistent separator that cannot appear in data: HASH(BK1 || '||' || BK2). Document your separator convention and never change it.

SQL — HashKey Generation Patterns

-- Single business key (MD5 and SHA-256 variants)
SELECT
    MD5(UPPER(TRIM(customer_id)))          AS HUB_CUSTOMER_HK,
    SHA2(UPPER(TRIM(customer_id)), 256)    AS HUB_CUSTOMER_HK_SHA256
FROM raw_landing.crm_contacts;

-- Composite business key (always use consistent separator)
SELECT
    MD5(
        UPPER(TRIM(order_date))   || '||' ||
        UPPER(TRIM(order_number))
    ) AS HUB_ORDER_HK
FROM raw_landing.erp_orders;

-- In Snowflake (preferred syntax with NVL for NULL safety)
SELECT
    MD5(UPPER(TRIM(NVL(customer_id, '-1')))) AS HUB_CUSTOMER_HK
FROM raw_landing.crm_contacts
WHERE customer_id IS NOT NULL;

-- Link hash key: hash of ALL participating hub business keys
SELECT
    MD5(
        UPPER(TRIM(customer_id)) || '||' ||
        UPPER(TRIM(order_id))
    ) AS LINK_CUSTOMER_ORDER_HK,
    MD5(UPPER(TRIM(customer_id))) AS HUB_CUSTOMER_HK,
    MD5(UPPER(TRIM(order_id)))    AS HUB_ORDER_HK
FROM raw_landing.erp_orders;

🔗

Cross-system integration magic: When the CRM and ERP both know about the same customer, their Hash Keys will automatically match in the vault — without any lookup table or central key registry — as long as both systems are loading the same UPPER(TRIM(BK)). This is the core mechanism that enables Data Vault to integrate data from multiple source systems.

Concept 04

HashDiff for Change Detection

🧠 ELI5 — Explain Like I'm Five

When loading a Satellite, we need to know if the incoming row is actually different from what we last stored. Instead of comparing 20+ columns one by one, we hash all the payload columns together into a single “fingerprint” (HashDiff). If the fingerprint matches what's already stored, skip the insert. If it's different, something changed — insert a new row.

How HashDiff Works

The formula is: HASHDIFF = HASH(concatenation of all payload columns, in a fixed order)

The HashDiff is computed in the staging layer alongside Hash Keys, before any vault loading begins. It is stored in every Satellite row as a metadata column. During load, it's the single comparison that determines whether a new row needs to be inserted.

Why Column Order Matters

The hash of ('Alice' || 'Smith') is different from the hash of ('Smith' || 'Alice'). Column order in the concatenation must be fixed, documented, and consistent across all loads. Any change to column order would invalidate every existing HashDiff value in the Satellite, causing every row to appear as “changed” and triggering a mass duplicate insert. Document your column order in metadata and enforce it through code generation or templates.

Handling NULLs in HashDiff

NULL concatenation is treacherous: in SQL, NULL || 'anything' = NULL. If any payload column is NULL and you don't handle it, your entire HashDiff becomes NULL. Use COALESCE(column, 'NULL_SUBSTITUTION_TOKEN') for every column. The substitution token must be a string that cannot plausibly appear as actual data (e.g., '^^' or '~NULL~') to avoid false collision with real empty-string values.

Performance Advantage

Instead of a WHERE clause comparing N columns at load time, the Satellite load becomes a simple single-column comparison (sat.HASH_DIFF != src.HASH_DIFF). This makes the load logic identical regardless of whether a Satellite has 5 columns or 50. On cloud platforms, this single comparison is extremely cache-friendly and scales linearly with row count.

SQL — HashDiff Computation in Staging

-- HashDiff computation in staging layer
-- ^^ is the NULL substitution token (unlikely to appear in data)
SELECT
    MD5(
        COALESCE(UPPER(TRIM(first_name)),  '^^') || '||' ||
        COALESCE(UPPER(TRIM(last_name)),   '^^') || '||' ||
        COALESCE(UPPER(TRIM(email)),       '^^') || '||' ||
        COALESCE(CAST(date_of_birth AS VARCHAR), '^^') || '||' ||
        COALESCE(UPPER(TRIM(country_code)), '^^')
        -- Column order is FIXED: changing it invalidates all existing HashDiffs
    ) AS HASH_DIFF,
    -- Pre-compute the Hash Key too
    MD5(UPPER(TRIM(contact_id))) AS HUB_CUSTOMER_HK,
    first_name, last_name, email, date_of_birth, country_code
FROM raw_landing.crm_contacts
WHERE contact_id IS NOT NULL;

-- Satellite load: only insert when HashDiff differs from current record
INSERT INTO SAT_CUSTOMER_DEMOGRAPHICS
SELECT
    src.HUB_CUSTOMER_HK,
    src.LOAD_DATE,
    NULL AS LOAD_END_DATE,
    src.HASH_DIFF,
    src.RECORD_SOURCE,
    src.first_name, src.last_name, src.email,
    src.date_of_birth, src.country_code
FROM stg_customer_demographics src
LEFT JOIN SAT_CUSTOMER_DEMOGRAPHICS sat
       ON sat.HUB_CUSTOMER_HK = src.HUB_CUSTOMER_HK
      AND sat.LOAD_END_DATE IS NULL         -- current (open) record only
WHERE sat.HASH_DIFF IS NULL             -- no existing record yet, OR
   OR sat.HASH_DIFF != src.HASH_DIFF;    -- payload has changed

Metadata tip: Store your HashDiff column order as metadata (in a config table or dbt YAML). Every satellite's column list should be auto-generated from this metadata, not hand-coded in each load script. This is the first step toward a fully automated, metadata-driven DV pipeline.

Concept 05

Load Date & Record Source

🧠 ELI5 — Explain Like I'm Five

Every single row in every vault table must carry two mandatory metadata columns: LOAD_DATE (when did this data arrive in the warehouse?) and RECORD_SOURCE (which system sent it?). These two columns make the difference between “a data warehouse” and “an auditable data vault.”

LOAD_DATE: The Arrival Timestamp

LOAD_DATE records when the data arrived in the warehouse, not when the business event occurred. This distinction is critical and commonly misunderstood.

Set once in the staging layer — Assign CURRENT_TIMESTAMP() or a batch-controlled timestamp at the beginning of each load. Propagate this value unchanged through all Hub, Link, and Satellite loads for that batch.
Never update it — LOAD_DATE is immutable. Once written, it never changes. It is part of the primary key for Satellites.
Use a batch timestamp for consistency — Using CURRENT_TIMESTAMP() individually per row means rows loaded a microsecond apart get different LOAD_DATEs. Instead, capture the batch start time once and use that single value for all rows in the batch.
Business event time is NOT LOAD_DATE — If a customer changed their address on Jan 1 but you didn't receive the extract until Jan 5, LOAD_DATE = Jan 5. The effective date (Jan 1) goes in a satellite payload column.

RECORD_SOURCE: The Lineage Trail

RECORD_SOURCE is a free-text string identifying exactly which source system provided this row. It is the single most powerful debugging and compliance tool in the vault.

Naming convention: SYSTEM.SUBSYSTEM.TABLE — for example, 'CRM.SALESFORCE.CONTACT_V2', 'ERP.SAP.CUSTOMERS', 'WEB.ADOBE.CLICKSTREAM'. Be specific enough that a DBA can trace the value back to an exact pipeline and source object.
Debugging data quality — When a value looks wrong, the RECORD_SOURCE tells you exactly which system to investigate.
Conflict resolution — When two source systems both have data for the same customer but disagree, RECORD_SOURCE tells you which system to trust for which attributes.
Regulatory compliance — Auditors frequently ask “which system says this?” RECORD_SOURCE answers that question directly, without any reverse-engineering.

SQL — Staging Layer Metadata Pattern

-- Staging layer: add vault metadata before loading
-- Everything computed here; vault loads are pure INSERT...SELECT
SELECT
    s.*,

    -- Set once per batch; all rows in this batch share this timestamp
    CURRENT_TIMESTAMP()              AS LOAD_DATE,
    'CRM.SALESFORCE.CONTACT_V2'      AS RECORD_SOURCE,

    -- Pre-compute the Hub Hash Key
    MD5(UPPER(TRIM(s.contact_id)))   AS HUB_CUSTOMER_HK,

    -- Pre-compute the HashDiff for SAT_CUSTOMER_DEMOGRAPHICS
    MD5(
        COALESCE(UPPER(TRIM(s.first_name)), '^^') || '||' ||
        COALESCE(UPPER(TRIM(s.last_name)),  '^^') || '||' ||
        COALESCE(UPPER(TRIM(s.email)),      '^^')
    )                                AS HASH_DIFF

FROM raw_landing.crm_contacts s
WHERE s.contact_id IS NOT NULL  -- filter NULL BKs early, in staging
  AND TRIM(s.contact_id) != '';

-- The downstream Hub load then becomes simply:
INSERT INTO HUB_CUSTOMER (HUB_CUSTOMER_HK, CUSTOMER_BK, LOAD_DATE, RECORD_SOURCE)
SELECT HUB_CUSTOMER_HK, contact_id, LOAD_DATE, RECORD_SOURCE
FROM stg_crm_contacts
WHERE NOT EXISTS (
    SELECT 1 FROM HUB_CUSTOMER h
    WHERE  h.HUB_CUSTOMER_HK = stg_crm_contacts.HUB_CUSTOMER_HK
);

📋

Architecture principle: The staging layer is the only place where CURRENT_TIMESTAMP() and source system literals are used. Every downstream vault load is a pure INSERT...SELECT from staging. This means vault loads are stateless, repeatable, and trivially testable.

Concept 06

The Insert-Only Pattern

🧠 ELI5 — Explain Like I'm Five

In Data Vault, you never UPDATE a row. You never DELETE a row. You only ever INSERT new rows. If something changes, you add a new row with a new timestamp. If something is “deleted” in the source system, you either record a soft-delete satellite row or simply stop seeing that key in future loads — which itself becomes part of the history.

Why Insert-Only?

Updates destroy the audit trail — The moment you UPDATE a row, you permanently lose what was there before. There is no way to prove what the data said yesterday. This is catastrophic for regulatory compliance and data trust.
Deletes lose data forever — DELETE is irreversible in a traditional sense. In a vault, “deleted” data is still needed: auditors want to know it existed, machine learning models need historical patterns, business analysis requires full timelines.
Inserts are idempotent and parallelizable — INSERT...SELECT with a NOT EXISTS guard is safe to run twice (idempotent) and does not require row-level locking. Multiple loading threads can INSERT into the same table simultaneously without coordination.

Handling Source System Deletes

When a source system “deletes” a record, Data Vault responds in one of two ways, depending on whether the source system signals the deletion explicitly:

Explicit deletion signal — Add a Status Tracking Satellite with an IS_DELETED flag column. When the source marks a record as deleted, insert a new Satellite row with IS_DELETED = 'Y' and a new LOAD_DATE. The full history remains; the deletion is just another data point.
Implicit deletion (record stops appearing) — Use an Effectivity Satellite that tracks whether the entity was present in the latest extract. When the record disappears from the source file, close the effectivity satellite row with a LOAD_END_DATE. The absence of data is itself recorded as data.

Handling Corrections

If incorrect data was loaded, do not update or delete the wrong row. Instead, insert a new Satellite row with the corrected data and the current LOAD_DATE. The old incorrect row remains permanently as a record of what was believed to be true at that time — which may itself be needed to prove what decisions were made based on that data.

Performance at Scale

INSERT is faster than UPDATE/MERGE at scale on cloud columnar databases. Snowflake, BigQuery, and Databricks are all optimized for bulk inserts into append-friendly table structures. UPDATE requires locating existing rows, acquiring locks, and rewriting blocks — a much more expensive operation on a columnar store.

✓ Best Practices

Always use INSERT...SELECT for all vault loading
Use MERGE only in staging layer (never in vault)
Let the HashDiff pattern prevent duplicate Satellite inserts
Use NOT EXISTS guards on Hub and Link inserts
Record deletions as new Satellite rows with a status flag
Document your insert-only policy in team standards

✗ Common Mistakes

Using UPDATE to “fix” data already in the vault
Deleting rows because “it was a mistake” or “wrong data”
Using TRUNCATE + reload for Raw Vault tables
Running a MERGE directly against Hub or Satellite tables
Skipping the NOT EXISTS guard (causes duplicates on re-run)
Treating insert-only as optional for “small tables”

⚠

The golden rule: If you find yourself writing an UPDATE or DELETE statement against a Raw Vault table, stop and reconsider the design. There is always an insert-only alternative that preserves history. The only valid reason to UPDATE a vault row is a catastrophic load error discovered within the same batch window — and even then, it should be documented as an exceptional event.

Concept 07

Interview Q&A: Core Modeling

Junior / Mid-Level Questions

What is a business key and why is it important in Data Vault? ▶

The business key is the natural identifier used by the business to refer to a real-world entity. It's the cornerstone of Data Vault because Hubs are organized around BKs, Hash Keys are deterministically derived from BKs, and all integration across sources happens by matching BKs. Without a reliable BK, you cannot build a Hub — and without a Hub, you have no Data Vault.

A valid business key must satisfy four criteria: (1) it must not be NULL, (2) it must be unique within its domain, (3) it must be relatively stable over time, and (4) it must be meaningful to the source system — not just an internal database surrogate. Auto-increment PKs and database sequence numbers fail criterion (4) and are not valid business keys.

Why does Data Vault use hash keys instead of sequence-generated surrogate keys? ▶

Three reasons:

(1) Determinism: The same BK always hashes to the same HK, so you can compute the HK in the staging layer on any server, at any time, without coordination. The value is guaranteed before INSERT.

(2) Parallel loading: No identity column means no sequence contention. Multiple loading threads can INSERT into the same Hub simultaneously — each thread independently computes the same HK for the same BK. This is the mechanism that enables Data Vault's parallel loading architecture.

(3) Cross-system joins: If two source systems both know about the same customer, their Hash Keys will match automatically when their BKs match, enabling integration without a central key registry or lookup table. This is how Data Vault integrates data across source systems without explicit mapping tables.

How does HashDiff work and what problem does it solve? ▶

HashDiff is a hash of all payload columns in a Satellite row, computed in the staging layer before any vault loading begins. When loading a Satellite, you compare the incoming HashDiff to the current satellite record's HashDiff. If they match, the data hasn't changed — skip the insert. If they differ, the data has changed — insert a new row.

The problem it solves is the expensive column-by-column comparison problem: instead of comparing 30 individual columns between the incoming row and the current satellite record, you compare exactly one hash value. This also makes Satellite load logic identical regardless of how many columns the Satellite has — the SQL pattern is the same for 5 columns and for 50 columns.

Critical implementation detail: all payload columns must be wrapped in COALESCE(col, 'NULL_TOKEN') before concatenation, because NULL concatenation in SQL returns NULL, which would break the comparison.

What's the difference between LOAD_DATE and an event timestamp? ▶

LOAD_DATE is the warehouse arrival time — when the data landed in our system during an ETL/ELT batch. It answers the question: “When did we learn about this?”

An event timestamp (sometimes called “effective date,” “business date,” or “as-of date”) is when the business event actually occurred. It answers: “When did this happen?”

These are different! Example: a customer changed their address on Jan 1 (event timestamp), but the source system's daily extract didn't arrive until Jan 5 (LOAD_DATE). LOAD_DATE = Jan 5 is what goes in the mandatory vault metadata columns. The effective date = Jan 1 goes in a satellite payload column if the business needs it for as-of queries.

Confusing these two timestamps is one of the most common errors in first-time DV implementations and leads to incorrect point-in-time analysis.

Can you explain the insert-only principle and what happens when source data is “deleted”? ▶

The Raw Vault never deletes or updates rows. Every change to data produces a new INSERT. This preserves a complete, tamper-evident record of everything every source system ever said, at every point in time.

When a source system deletes a record, there are two approaches:

(1) Ghost / soft-delete pattern: Add a Status Tracking Satellite row with an IS_DELETED = 'Y' flag and a new LOAD_DATE. The satellite history now shows exactly when the entity was marked as deleted, by which source system. Future loads that do not see the entity simply don't insert a new row — the last row with IS_DELETED = Y remains the current state.

(2) Effectivity Satellite pattern: Use an Effectivity Satellite that tracks whether the entity was present in the latest extract. When the source stops sending the record, close the effectivity satellite row. The absence of data is itself data, and the effectivity satellite records the timeline of presence and absence.

Both approaches preserve full history. The choice depends on whether the source system explicitly signals deletions (approach 1) or simply stops including the record in extracts (approach 2).

Senior / Architect Level Questions

How do you handle NULL business keys? What are the risks? ▶

NULL BKs must be filtered out in staging before vault loading — this is a hard stop, not optional and not a best-practice suggestion. It is a fundamental requirement.

The risk: HASH(NULL) behavior depends on the database. In most systems it returns NULL (which cannot be a primary key and causes insert failures) or a constant hash of the empty string (which would merge all NULL-key records into a single Hub row, causing complete data integrity collapse). In either case, you cannot distinguish between two different entities that both had NULL as their BK.

Implementation: Filter with WHERE BK IS NOT NULL AND TRIM(BK) != '' in the staging query. This prevents NULL and empty-string BKs from ever reaching the vault.

For genuinely unknown BKs: If a record legitimately has an unknown or missing identifier, use a Ghost Record: assign BK = 'UNKNOWN' or '-1' and document this as a conscious design decision. The ghost record will have its own Hub row and can be linked to data that couldn't be attributed to a real entity. This is different from accidentally loading a NULL — it is an explicit design choice with a known meaning.

✅ Module 02 Key Takeaways

The 9 DV2 Principles are non-negotiable. Any design that violates even one of them is not Data Vault — use them as a checklist for every table you design.
Business keys are the foundation of every Hub. They must be non-NULL, unique within their domain, relatively stable, and meaningful to the source system. Auto-increment database PKs are never valid BKs.
Hash Keys are deterministic surrogates derived from normalized (UPPER + TRIM) business keys. The same BK always produces the same HK, enabling parallel loading without coordination and automatic cross-system integration.
HashDiff is a hash of all payload columns in a Satellite, computed in staging with consistent column order and NULL substitution tokens. It enables single-value change detection instead of expensive column-by-column comparison.
LOAD_DATE is the warehouse arrival timestamp — not the business event time. RECORD_SOURCE is the source system identifier. Both are mandatory on every vault row and are set exactly once, in the staging layer.
The Raw Vault is insert-only forever. Source deletions become new Satellite rows. Corrections become new Satellite rows. Nothing is ever updated or deleted. This is what makes the vault an auditable, regulatory-grade record of enterprise data history.