Module 14: Interview Mastery

Concept 01

Junior / Mid-Level Interview Questions

🧠 ELI5 — Explain Like I’m Five

Junior and mid-level DV interviews test whether you understand the fundamentals: what are the three vault objects, what goes in each, what is a business key, what does insert-only mean, and can you explain HashDiff. These questions are direct — focus on clear, concise answers.

Explain Data Vault 2.0 to someone who knows Kimball dimensional modeling. ▶

Both store enterprise data for analytics, but they take different approaches. Kimball optimizes for query performance: you design facts and dimensions for the reports you expect. DV optimizes for integration and auditability: you store everything in a normalized, source-faithful form first (Raw Vault), then build reporting structures on top (Information Marts).

Key differences: DV is insert-only (Kimball uses SCD2 updates), DV tracks full history automatically for free (Kimball requires SCD design), DV can add new sources without redesign (Kimball often requires schema changes), and DV separates integration from reporting (Kimball mixes them).

For someone who knows Kimball: think of the Raw Vault as an SCD2 store for every entity and relationship simultaneously, with automatic lineage.

What are the three main objects in Data Vault and what does each store? ▶

Hub, Link, Satellite.

Hub: stores unique business keys — the identifiers the business uses for real-world entities (CustomerID, ProductSKU). One row per unique entity, insert-only, no descriptive data.

Link: stores relationships between hubs — the fact that two or more entities are related (Customer placed Order). One row per unique combination of Hub keys, insert-only, no descriptive data.

Satellite: stores descriptive attributes — the context that describes an entity or relationship (customer name, email, address). Many rows per hub/link key, each row capturing a point-in-time snapshot with load timestamp.

What is a business key and how is it different from a hash key? ▶

Business key (BK): the natural identifier the business uses to refer to an entity in real-world processes — printed on invoices, typed by users, used in conversations. It’s meaningful (CustomerID ‘1234’ means something to the business) and relatively stable.

Hash key (HK): a surrogate derived by hashing the normalized BK (UPPER/TRIM applied). It’s deterministic (same BK always produces same HK), fixed-length (32 chars for MD5), and used as the primary key of Hubs and as foreign keys everywhere else.

The HK allows hash key computation in parallel across multiple servers without coordination, enabling the vault’s parallel loading architecture.

What is HashDiff and why do satellites need it? ▶

HashDiff is a hash (MD5 or SHA-256) computed over all the payload (descriptive) columns in a satellite row. When loading a satellite, you compare the incoming HashDiff to the current satellite row’s HashDiff.

If they match, the data hasn’t changed — skip the insert. If they differ, something changed — insert a new row.

Without HashDiff, you’d need to compare every individual column between incoming and existing data, which is complex and slow for satellites with many columns. With HashDiff, change detection is always a single value comparison regardless of column count.

Why is Data Vault insert-only? What happens when source data is deleted? ▶

Insert-only is fundamental to auditability and parallel loading. Auditability: if you update or delete rows, you lose the history of what was true before. Insert-only means every change is permanently recorded with its timestamp. Parallel loading: INSERT operations don’t cause row-level locks the way UPDATE/MERGE does; multiple sessions can INSERT to the same table simultaneously.

When source data is deleted:

Option 1: Insert a new satellite row with a deletion flag (IS_DELETED='Y' in a Status Satellite) and a new LOAD_DATE.

Option 2: Use an Effectivity Satellite on a Link with IS_ACTIVE='N' when the relationship ends.

Option 3: Simply stop loading that entity’s satellite rows — future PIT table queries will show the entity as having no current record after a certain date.

What is the difference between Load Date and an event timestamp? ▶

LOAD_DATE (or LDTS) is the warehouse arrival time — when the data landed in our staging/vault system. It’s set once in staging (usually CURRENT_TIMESTAMP at batch start) and never changed.

An event timestamp (business date, effective date, transaction date) is when the business event actually occurred in the real world. They can differ significantly: a payment might have occurred on Jan 1 (event date) but the source extract ran on Jan 5 (LOAD_DATE).

LOAD_DATE goes in the vault’s mandatory metadata columns. Event timestamps go in satellite payload columns as regular data.

What is a Satellite splitting strategy and why does it matter? ▶

Splitting satellites means dividing one entity’s attributes across multiple satellite tables rather than one giant table. Split by:

Source: CRM attributes in SAT_CUSTOMER_FROM_CRM, ERP attributes in SAT_CUSTOMER_FROM_ERP.

Rate-of-change: daily stock prices separate from rarely-changing product descriptions.

Classification: demographic data separate from financial data.

Why it matters: if all attributes are in one satellite, every change to ANY attribute creates a new row copying ALL attributes — massive duplication. With split satellites, a CRM address change only creates a new row in the CRM satellite; ERP data is unaffected.

Can you sketch a simple Data Vault model for an e-commerce order scenario? ▶

Entities and relationships: Customer places Order; Order contains Product; Product belongs to Category.

Hub tables: HUB_CUSTOMER (CUSTOMER_BK), HUB_ORDER (ORDER_BK), HUB_PRODUCT (PRODUCT_SKU), HUB_CATEGORY (CATEGORY_CODE).

Link tables: LINK_CUSTOMER_ORDER (HUB_CUSTOMER_HK + HUB_ORDER_HK), LINK_ORDER_PRODUCT (HUB_ORDER_HK + HUB_PRODUCT_HK + quantity as DD), LINK_PRODUCT_CATEGORY (HUB_PRODUCT_HK + HUB_CATEGORY_HK).

Satellite tables: SAT_CUSTOMER_DEMOGRAPHICS (name, email, DOB), SAT_CUSTOMER_ADDRESS (address columns — separate because change rate differs), SAT_ORDER_DETAILS (status, total_amount, order_date), SAT_PRODUCT_DESCRIPTION (name, description), SAT_PRODUCT_PRICING (price — changes frequently, separate satellite), SAT_CATEGORY_DETAILS (category name, hierarchy).

Business Vault: PIT_CUSTOMER (pre-joins customer satellite timelines), BRIDGE_CUSTOMER_ORDER_PRODUCT (pre-flattens the traversal chain).

Concept 02

Senior / Architect Interview Questions

🧠 ELI5 — Explain Like I’m Five

Senior and architect-level questions go beyond definitions. Interviewers want to see that you can design under constraints, handle edge cases, compare platforms, and understand the full architectural lifecycle. These questions often have no single correct answer — your reasoning and trade-off awareness matter as much as the solution itself.

Design a Data Vault for a multi-country bank that needs consolidated risk reporting under BCBS 239. ▶

(1) HUB_LEGAL_ENTITY uses GLEIF LEI (Legal Entity Identifier) as BK — globally standardized, available for all regulated entities, doesn’t change.

(2) SAL_LEGAL_ENTITY handles the mapping from internal party codes to LEI.

(3) HUB_TRADE with TradeID BK; HUB_INSTRUMENT with ISIN or CUSIP as BK.

(4) LINK_TRADE_RISK_EXPOSURE: three-way link (Trade, Instrument, Legal Entity) capturing the exposure relationship.

(5) Effectivity Satellites on all trade-related links for position open/close tracking.

(6) SHA-256 (not MD5) for hash keys — regulatory environment requires stronger collision resistance.

(7) Record Source includes DATA_QUALITY_TIER: 'AUTHORITATIVE', 'INDICATIVE', 'ESTIMATED'.

(8) Regulatory Mart: Point-in-time capability so regulators can request “what was our exposure on date X” retroactively.

(9) Access logging satellite: every query against the Regulatory Mart writes to an access log for BCBS 239 audit trail.

How would you handle late-arriving data in a Data Vault? Give a concrete example. ▶

Late-arriving data means a source system sends data for a past date after that date has already been processed.

Example: On Jan 10, a branch office sends transactions from Dec 31 that were delayed due to connectivity issues.

(1) Insert the late record with LOAD_DATE = Jan 10 (the actual warehouse arrival time — this is immutable).

(2) The event date (Dec 31) is in the satellite payload column (TRANSACTION_DATE).

(3) The Raw Vault now has the December data, correctly timestamped as ‘arrived Jan 10’.

(4) Rebuild affected PIT tables and Information Marts to include the new records.

(5) Regulatory reports for December may need to be re-issued — the vault makes this possible because the late data is now present.

Key distinction: LOAD_DATE is always the actual warehouse arrival time, never backdated. The business event date goes in payload. This keeps the vault’s audit trail accurate.

Compare Data Vault modeling approaches for Snowflake vs Databricks Delta Lake. What changes? ▶

Both platforms support DV well, but with different optimizations.

Snowflake: no traditional indexes — use CLUSTER BY (HUB_HK) and CLUSTER BY (HUB_HK, TO_DATE(LOAD_DATE)) on Satellites; use Dynamic Tables for auto-refreshing Business Vault structures; leverage Zero-Copy Cloning for dev/test environments; MD5/SHA2 functions are built-in; MERGE is efficient and recommended.

Databricks: use Delta Lake MERGE INTO for satellite CDC; Z-ORDER BY (hub_hk, load_date) replaces clustering; Delta time travel (TIMESTAMP AS OF) complements DV history but doesn’t replace it; Python/PySpark often used for complex staging transforms; Unity Catalog provides governance integration.

Common to both: hash key computation pattern (MD5(UPPER(TRIM(BK)))), insert-only Hub/Link loading with NOT EXISTS or MERGE-when-not-matched, satellite CDC via HashDiff, PIT and Bridge tables in Business Vault.

Walk me through how you would architect a streaming Data Vault pipeline for real-time fraud detection. ▶

Requirements: transactions processed within seconds, fraud signal available before transaction settles.

(1) Source: payment processor emits transaction events to Kafka topic 'transactions'.

(2) Stream processor (Spark Structured Streaming or Flink): consume events, compute HKs and HashDiff in-stream, apply hard business rules (NULL BK filter, normalization), write to Delta Lake Raw Vault as micro-batches (every 10–30 seconds).

(3) Raw Vault: LINK_PAYMENT_TRANSACTION (non-historized, with amount as DD), HUB_CARD, HUB_MERCHANT, HUB_DEVICE, SAT_TRANSACTION_CONTEXT (location, device fingerprint).

(4) Business Vault trigger: after each micro-batch lands in Raw Vault, trigger a Business Vault compute for affected customer hub keys — update rolling aggregates (transactions_last_1h, transactions_last_24h, distinct_merchants_last_6h).

(5) Fraud scoring model reads from Business Vault aggregates (near-real-time, seconds old) and produces risk_score.

Result: fraud signal available within 5–10 seconds of transaction event. Information Mart for fraud analysts rebuilt every 5 minutes from Business Vault.

What is the CDVDM certification and what are the core knowledge areas it tests? ▶

CDVDM (Certified Data Vault Data Modeler) is the industry certification for Data Vault professionals, offered by the Data Vault Alliance and Dan Linstedt’s organization.

(1) Data Vault 2.0 methodology and all 9 modeling principles.

(2) Hub, Link, Satellite design rules and when to use each variant (Multi-Active Sat, Effectivity Sat, Same-As Link, Non-Historized Link).

(3) Raw Vault and Business Vault architecture, separation of concerns.

(4) Loading patterns: staging, HashKey, HashDiff, parallel loading.

(5) Performance optimization: PIT tables, Bridge tables, indexing.

(6) Information Mart design: converting vault to star schema, SCD types.

(7) NoSQL and cloud DV patterns.

(8) Methodology: SEI/CMMI-compliant agile sprint integration with DV2.

Exam format: multiple choice + scenario-based questions. Recommended prep: complete all DV2 Mastery modules, read Building a Scalable Data Warehouse with Data Vault 2.0 by Linstedt & Olschimke, practice SQL for all common patterns.

What’s the most common mistake you see in real Data Vault implementations? ▶

Three consistent patterns:

(1) Soft rules in the Raw Vault — teams under time pressure put code lookups, derivations, or business logic in staging or the Raw Vault. This destroys the audit trail and makes rule changes expensive. Solution: strict architecture review checkpoints before any new staging transform is approved.

(2) Giant satellites — one satellite with 80+ columns for an entity. Every change to any column creates a new row with all 80 columns, causing massive storage bloat and slow change detection. Solution: satellite splitting by source, rate-of-change, and classification — enforced at design review.

(3) Skipping the Business Vault — teams build Raw Vault and then try to query it directly from BI tools. Performance is terrible and queries are impossibly complex. Solution: mandate PIT tables for every entity with more than 2 satellites before any reporting mart is built.

Concept 03

Live SQL Coding Challenges

🧠 ELI5 — Explain Like I’m Five

Many DV interviews include a live SQL coding challenge. These typically test: HashKey computation, satellite CDC loading, and point-in-time queries. Practice these until you can write them cold.

Challenge 1 — Hub Load

Write the Hub load for HUB_PRODUCT from staging table STG_PRODUCT. Staging has: PRODUCT_SKU, PRODUCT_NAME, PRICE, CATEGORY, LOAD_DATE, RECORD_SOURCE.

SQL — Challenge 1: Hub Load for HUB_PRODUCT

-- HUB_PRODUCT target: HUB_PRODUCT_HK, PRODUCT_BK, LOAD_DATE, RECORD_SOURCE

INSERT INTO HUB_PRODUCT (HUB_PRODUCT_HK, PRODUCT_BK, LOAD_DATE, RECORD_SOURCE)
SELECT
    MD5(UPPER(TRIM(PRODUCT_SKU)))  AS HUB_PRODUCT_HK,
    UPPER(TRIM(PRODUCT_SKU))       AS PRODUCT_BK,
    LOAD_DATE,
    RECORD_SOURCE
FROM STG_PRODUCT stg
WHERE PRODUCT_SKU IS NOT NULL           -- hard rule: never load null BK
  AND NOT EXISTS (
      SELECT 1 FROM HUB_PRODUCT h
      WHERE h.HUB_PRODUCT_HK = MD5(UPPER(TRIM(stg.PRODUCT_SKU)))
  );

-- Common mistakes to avoid:
-- 1. Forgetting NOT EXISTS → duplicates on re-run
-- 2. Forgetting IS NOT NULL filter on BK
-- 3. Not normalizing BK before hashing (UPPER/TRIM must match everywhere)

Challenge 2 — Satellite CDC Load

Write the Satellite CDC load for SAT_PRODUCT_PRICING. The staging table has pre-computed HUB_PRODUCT_HK and HASH_DIFF_PRICING.

SQL — Challenge 2: Satellite CDC Load for SAT_PRODUCT_PRICING

-- SAT_PRODUCT_PRICING: HUB_PRODUCT_HK, LOAD_DATE, LOAD_END_DATE,
--                      HASH_DIFF, RECORD_SOURCE, LIST_PRICE, SALE_PRICE, CURRENCY_CODE

INSERT INTO SAT_PRODUCT_PRICING (
    HUB_PRODUCT_HK, LOAD_DATE, LOAD_END_DATE,
    HASH_DIFF, RECORD_SOURCE,
    LIST_PRICE, SALE_PRICE, CURRENCY_CODE
)
SELECT
    stg.HUB_PRODUCT_HK,
    stg.LOAD_DATE,
    NULL                   AS LOAD_END_DATE,
    stg.HASH_DIFF_PRICING  AS HASH_DIFF,
    stg.RECORD_SOURCE,
    stg.LIST_PRICE, stg.SALE_PRICE, stg.CURRENCY_CODE
FROM STG_PRODUCT stg
LEFT JOIN SAT_PRODUCT_PRICING sat
    ON  sat.HUB_PRODUCT_HK = stg.HUB_PRODUCT_HK
    AND sat.LOAD_END_DATE IS NULL           -- join to CURRENT row only
WHERE sat.HUB_PRODUCT_HK IS NULL          -- new product: no satellite row yet
   OR sat.HASH_DIFF != stg.HASH_DIFF_PRICING; -- existing product: price changed

-- Common mistakes:
-- 1. Joining to ALL satellite rows (should join only to current: LOAD_END_DATE IS NULL)
-- 2. Wrong null check: use IS NULL not = NULL
-- 3. Forgetting the new-product case (sat.HK IS NULL after LEFT JOIN)

Challenge 3 — Point-in-Time Query

Write a point-in-time query to get a product’s price as of '2023-06-01'. “What was the list price of product SKU-12345 on June 1, 2023?”

SQL — Challenge 3: Point-in-Time Query (without PIT table)

-- "What was the list price of product SKU-12345 on June 1, 2023?"
SELECT
    h.PRODUCT_BK,
    p.LIST_PRICE,
    p.SALE_PRICE,
    p.LOAD_DATE     AS effective_from,
    p.LOAD_END_DATE AS effective_to
FROM HUB_PRODUCT h
JOIN SAT_PRODUCT_PRICING p
    ON p.HUB_PRODUCT_HK = h.HUB_PRODUCT_HK
WHERE h.PRODUCT_BK = 'SKU-12345'
  AND p.LOAD_DATE = (
      -- Find the most recent load date that is on or before the target date
      SELECT MAX(p2.LOAD_DATE)
      FROM SAT_PRODUCT_PRICING p2
      WHERE p2.HUB_PRODUCT_HK = h.HUB_PRODUCT_HK
        AND p2.LOAD_DATE <= '2023-06-01 23:59:59'
  );

-- Common mistakes:
-- 1. Using > instead of <= in the subquery (wrong direction)
-- 2. Using LOAD_END_DATE IS NULL (gives CURRENT record, not as-of)
-- 3. Not handling the case where no record existed before the target date

💡

Interview tip: When given a SQL challenge live, narrate what you’re doing. Say “I’m filtering NULLs here because a Hub must never store a null business key” or “I’m joining only to current satellite rows by filtering LOAD_END_DATE IS NULL.” Interviewers want to see that you understand the why, not just the syntax.

Concept 04

System Design — DV at Scale

🧠 ELI5 — Explain Like I’m Five

System design interviews ask you to architect a complete Data Vault solution from scratch. The interviewer gives you a scenario (“design a data warehouse for a global bank”) and you’re expected to walk through the full architecture: source systems, staging, Raw Vault, Business Vault, Information Mart, tooling, and operational considerations.

Six-Step Framework for DV System Design Questions

Step 1 — Clarify Scope (2–3 minutes)

How many source systems?
Data volume (GB/day, TB/day)?
Latency requirement (batch daily, near-real-time, streaming)?
Regulatory environment (BCBS 239, GDPR, HIPAA, SOX)?
Cloud platform or on-premises?
Team size and skill level?

⚠

Never skip Step 1. Jumping straight to design without clarifying constraints is the most common system design interview mistake. Spend 2–3 minutes confirming assumptions — it demonstrates seniority and prevents designing the wrong system.

Step 2 — Define Entities and Hubs (3–5 minutes)

Identify the 5–10 most important business entities
Confirm their business keys with the interviewer
Sketch Hub names on a whiteboard or shared doc

Step 3 — Define Relationships (Links) (2–3 minutes)

What are the critical relationships between entities?
Are any relationships transactional (non-historized link pattern)?
Are there hierarchies (self-referencing same-as links)?

Step 4 — Define Satellite Strategy (2–3 minutes)

How many satellites per hub?
Split by source system? By rate-of-change?
Any multi-active or effectivity satellites needed?

Step 5 — Architecture Layers

Staging: where does raw data land? (cloud storage, landing zone, Kafka)
Raw Vault: cloud platform choice, compute sizing, clustering strategy
Business Vault: PIT refresh frequency, which Bridge tables are needed?
Information Mart: star schema or wide tables, primary consumers

Step 6 — Operational Concerns

Orchestration tool (Airflow, dbt Cloud, Azure Data Factory, Prefect)
Monitoring and alerting (change rate anomalies, load window SLA breaches)
Data quality (Error Mart, reconciliation row counts, source vs vault comparisons)

Worked Example: Global Airline with 50M Passengers/Year

Step 1 — Clarifications: 80 countries, 15 source systems (PMS, PSS, loyalty, ground handling, cargo, finance), 2 TB/day, batch + near-real-time for loyalty transactions, cloud (Azure), regulated (GDPR, IATA).

Step 2 — Key Hubs: HUB_PASSENGER (Frequent Flyer Number), HUB_FLIGHT (Flight Number + Date as composite BK), HUB_AIRCRAFT (tail number), HUB_ROUTE (origin–destination pair), HUB_AIRPORT (IATA code), HUB_BOOKING (PNR code), HUB_LOYALTY_ACCOUNT (account number).

Step 3 — Key Links: LINK_PASSENGER_BOOKING, LINK_BOOKING_FLIGHT (non-historized — high volume, transactional), LINK_FLIGHT_AIRCRAFT (with Effectivity Sat for aircraft swaps), LINK_FLIGHT_ROUTE, LINK_PASSENGER_LOYALTY.

Step 4 — Satellites: SAT_PASSENGER_IDENTITY (name, DOB, passport — PII, encrypted at rest), SAT_PASSENGER_CONTACT (email, phone — separate satellite for GDPR right-to-erasure handling), SAT_FLIGHT_STATUS (departure status, delay reason — fast-changing), SAT_BOOKING_DETAILS (fare class, price, cabin), SAT_LOYALTY_TIER (Multi-Active Satellite — passenger can hold tiers in multiple programs simultaneously).

Step 5 — Layers: Staging on Azure Data Lake Gen2. Raw Vault on Azure Synapse (serverless + dedicated pool). Business Vault: PIT_PASSENGER refreshed every 30 min, BRIDGE_BOOKING_FLIGHT for revenue reporting. Information Mart: Passenger 360 (wide flat table, rebuilt daily), Revenue Mart (star schema, Kimball-style), Loyalty Mart.

Step 6 — Operations: Azure Data Factory + dbt Cloud for orchestration. Alerting on flight status satellite row count anomalies (cancellation spikes). GDPR: SAT_PASSENGER_CONTACT has an IS_ERASED flag; erasure request triggers a new satellite row with PII columns nullified — insert-only preserved.

Scoring criteria in system design interviews: Clarity of thought > completeness of design. An interviewer would rather see you methodically work through a 60% complete solution with clear reasoning than rush through a 100% complete answer you can’t explain. Always say why you made each design choice.

Concept 05

CDVDM Certification Prep & Final Review

🧠 ELI5 — Explain Like I’m Five

The CDVDM exam tests everything covered in Modules 01–13. This final concept gives you a rapid-fire review of the key facts you must know cold, plus common exam traps that trip up even experienced practitioners.

Hubs — Must Know

Rule	Detail
Only 4 columns	HK, BK, LOAD_DATE, RECORD_SOURCE — nothing else ever
No descriptive data	Names, addresses, status codes all go in Satellites
One row per unique BK	Insert-only; never update or delete
BK requirements	Must be not-null, business-meaningful, and relatively stable

Links — Must Know

Rule	Detail
FK references Hub HKs only	Never reference business keys directly from a Link column
Link HK derivation	Hash of ALL participating BKs concatenated (order matters — document it)
No descriptive data	Exception: Degenerate Dimensions (DDs) allowed in transaction links
One row per unique combination	One row per unique set of participant HKs; insert-only

Satellites — Must Know

Rule	Detail
Composite PK	(parent HK, LOAD_DATE) — never the HK alone
HashDiff coverage	Covers ALL payload columns — missing any column breaks change detection
Insert condition	Insert when HashDiff changes OR no existing row exists for that HK
Splitting strategy	Split by source system, rate-of-change, and classification

Loading Patterns — Must Know

Stage	Rule
Staging	Computes ALL hash keys and HashDiff values before any vault loads begin
Hub / Link load	INSERT WHERE NOT EXISTS (or MERGE WHEN NOT MATCHED only)
Satellite load	INSERT WHERE no current row OR HashDiff differs from current row
Parallelism	ALL vault loads can run in parallel — staging is the common synchronization point

Business Vault — Must Know

Object	Purpose
PIT Table	One row per entity per snapshot date; stores satellite LOAD_DATEs for fast joins without MAX() aggregation at query time
Bridge Table	Pre-flattens Hub-Link-Hub chains into wide lookup tables; eliminates multi-hop joins in Information Marts
Computed Satellites	Business Vault objects with `BSAT_` prefix; derived data, soft rules only, never raw source data
No hard rules	Business Vault applies soft rules only; hard structural rules belong in staging

Common Exam Traps

“Does a Hub contain descriptive attributes?” — Trap question ▶

Answer: NO. This is the most common trick question. Hubs contain exactly 4 columns: Hash Key, Business Key, Load Date, Record Source. Descriptive attributes (names, addresses, status codes) go in Satellites. Putting descriptive data in a Hub is the #1 beginner DV design mistake and a guaranteed exam failure.

“Can a Satellite be the primary store for a business key?” — Trap question ▶

Answer: NO. Business keys belong exclusively in Hubs. A Satellite references the Hub through its parent Hash Key (foreign key). Satellites are for descriptive, time-variant data — not for entity identity. If you put a business key in a Satellite, you have no stable identity anchor for history tracking.

“Is LOAD_DATE the business event date?” — Trap question ▶

Answer: NO. LOAD_DATE is the warehouse arrival time — when the data landed in the vault. The business event date (when something actually happened in the real world) goes in the satellite payload as a regular data column (e.g., TRANSACTION_DATE, EVENT_TIMESTAMP). Never confuse these two. LOAD_DATE is metadata; event date is business data.

“Is MERGE acceptable for vault loading?” — Nuanced question ▶

Answer: Yes, with strict conditions. For Hubs and Links: MERGE with WHEN NOT MATCHED only (no WHEN MATCHED update) is acceptable and equivalent to INSERT WHERE NOT EXISTS. For Satellites: MERGE is acceptable but must be limited to WHEN NOT MATCHED for new rows. Never use WHEN MATCHED UPDATE on Raw Vault satellites — that would violate the insert-only principle and destroy the audit trail.

“Can you UPDATE a Raw Vault satellite?” — Trap question ▶

Answer: NO — with one narrow exception. Raw Vault satellites are insert-only. The one permitted exception is populating LOAD_END_DATE when a new version of the record arrives (to support range-based queries). Many teams avoid even this by using PIT tables instead, which compute effective date ranges at query time without ever updating satellite rows. If asked on the exam, the default answer is “no updates” unless the question specifically references LOAD_END_DATE maintenance.

🏅 You’ve Completed the DV2 Mastery Program

All 14 modules. 200+ concepts. 100+ SQL examples. You now have the knowledge to design, build, and optimize enterprise Data Vault 2.0 implementations at any scale. Good luck in your interviews and certifications.

Foundation (M01–02) — DV2 introduction, core modeling concepts, history, and vocabulary
Modeling Deep Dives (M03–05) — Hubs, Links, and Satellites in full architectural detail
Architecture (M06–07) — Raw Vault and Business Vault patterns, PIT and Bridge tables
Performance & Advanced (M08–09) — Indexing, optimization, edge cases, ensemble modeling
Automation & Cloud (M10–11) — dbt, metadata-driven loading, Snowflake, Databricks, streaming
Delivery (M12–13) — Dimensional Information Marts, real-world case studies across industries
Interview Mastery (M14) — This module — Q&A by seniority, live SQL, system design, CDVDM prep