Module 07: Business Vault Architecture

Concept 01

Business Vault Purpose & Scope

🧠 ELI5 — Explain Like I’m Five

The Raw Vault stores everything exactly as the source sent it. But business users don’t query Raw Vault directly — it’s too complex, too normalized, and has no business logic applied. The Business Vault is the “interpretation layer” that sits between the raw data and the reporting layer. It’s where business rules turn data into information, without destroying the raw historical record.

📚 Analogy

The Raw Vault is like a surveillance archive — every frame recorded, every event captured, nothing ever deleted. The Business Vault is like the editor’s desk: analysts take that footage, add timestamps, labels, and context summaries, organize it into meaningful chapters. The raw tapes stay locked in the archive room, untouched. What business users see is the edited, annotated version — built from the raw footage but never replacing it.

What Lives in the Business Vault

Point-in-Time (PIT) tables — Pre-joined satellite timelines for fast “what was current on date X?” queries. One row per entity per snapshot date.
Bridge tables — Pre-flattened Hub–Link–Hub chains for faster relationship traversal across multiple levels.
Computed Satellites (BSAT) — Pre-calculated KPIs and derived metrics stored as satellite history (e.g., customer risk scores, 30-day moving averages).
Soft business rule applications — Code table lookups, classification logic, derived flags applied to Raw Vault data.
Reference data — Currency codes, country codes, hierarchy definitions structured as Hub + Satellite pairs.

What Does NOT Live in the Business Vault

Raw source data — That belongs in the Raw Vault (Hubs, Links, Satellites loaded directly from staging).
Final reporting structures — Star schemas, flat tables, and OLAP cubes belong in the Information Mart.
Hard business rules — Structural rules (key parsing, format standardization, NULL filtering) run in the Staging Layer before the Raw Vault load, never after.

💡

Key principle: “The Business Vault enhances raw data with business context while maintaining full lineage to the source.” Every Business Vault structure is derived from the Raw Vault — it can be dropped and fully rebuilt without data loss because the Raw Vault is always the source of truth.

Architecture Layers

Concept 02

Point-in-Time (PIT) Tables

🧠 ELI5 — Explain Like I’m Five

Imagine you want to know “what was the complete customer picture on January 1, 2023?” You need to query 5 different satellites and find the row that was current on that date for each. Without a PIT table, you write 5 MAX(LOAD_DATE) subqueries. That’s slow and complex. A PIT table pre-joins all those satellite timelines so you just query ONE table for the snapshot date — it already knows which satellite row was current on any given day.

What a PIT Table Contains

A PIT table stores one row per entity per snapshot date (daily, weekly, or by event). For each snapshot date, it records which satellite rows were “current” on that date by storing their LOAD_DATE values. This eliminates correlated subqueries at query time entirely.

The Hub hash key to join back to the entity
The snapshot date — the point in time being captured
One LOAD_DATE column per satellite — the LOAD_DATE of the satellite row that was current on the snapshot date
NULL in a satellite column means no record existed for that entity on that date

SQL — PIT_CUSTOMER Table DDL

-- PIT table for Customer entity
CREATE TABLE PIT_CUSTOMER (
    HUB_CUSTOMER_HK               CHAR(32)   NOT NULL,
    SNAPSHOT_DATE                 DATE       NOT NULL,
    -- One column per satellite: the LOAD_DATE of the row current on that date
    SAT_CUSTOMER_DEMOGRAPHICS_LDT TIMESTAMP,  -- NULL if no record existed on this date
    SAT_CUSTOMER_CONTACT_LDT      TIMESTAMP,
    SAT_CUSTOMER_FINANCIALS_LDT   TIMESTAMP,
    SAT_CUSTOMER_STATUS_LDT       TIMESTAMP,
    CONSTRAINT pk_pit_cust PRIMARY KEY (HUB_CUSTOMER_HK, SNAPSHOT_DATE)
);

SQL — PIT Generation (Daily Snapshot, Last 30 Days)

-- Generate PIT for all customers, daily snapshots for last 30 days
INSERT INTO PIT_CUSTOMER
SELECT
    h.HUB_CUSTOMER_HK,
    d.SNAPSHOT_DATE,
    (SELECT MAX(s.LOAD_DATE)
     FROM SAT_CUSTOMER_DEMOGRAPHICS s
     WHERE s.HUB_CUSTOMER_HK = h.HUB_CUSTOMER_HK
       AND s.LOAD_DATE <= d.SNAPSHOT_DATE
    ) AS SAT_CUSTOMER_DEMOGRAPHICS_LDT,
    (SELECT MAX(s.LOAD_DATE)
     FROM SAT_CUSTOMER_CONTACT s
     WHERE s.HUB_CUSTOMER_HK = h.HUB_CUSTOMER_HK
       AND s.LOAD_DATE <= d.SNAPSHOT_DATE
    ) AS SAT_CUSTOMER_CONTACT_LDT,
    (SELECT MAX(s.LOAD_DATE)
     FROM SAT_CUSTOMER_FINANCIALS s
     WHERE s.HUB_CUSTOMER_HK = h.HUB_CUSTOMER_HK
       AND s.LOAD_DATE <= d.SNAPSHOT_DATE
    ) AS SAT_CUSTOMER_FINANCIALS_LDT,
    (SELECT MAX(s.LOAD_DATE)
     FROM SAT_CUSTOMER_STATUS s
     WHERE s.HUB_CUSTOMER_HK = h.HUB_CUSTOMER_HK
       AND s.LOAD_DATE <= d.SNAPSHOT_DATE
    ) AS SAT_CUSTOMER_STATUS_LDT
FROM HUB_CUSTOMER h
CROSS JOIN (
    -- Date spine: one row per day for the last 30 days
    SELECT DATEADD('day', -seq, CURRENT_DATE()) AS SNAPSHOT_DATE
    FROM TABLE(GENERATOR(ROWCOUNT => 30))
    ORDER BY seq
) d;

The Query Simplification

The PIT table converts complex multi-correlated-subquery SQL into simple equality joins. Here is the same business query written both ways:

SQL — Without PIT vs With PIT

-- ❌ WITHOUT PIT: one correlated subquery per satellite — slow at scale
SELECT h.CUSTOMER_BK, d.FIRST_NAME, c.EMAIL, f.CREDIT_LIMIT
FROM HUB_CUSTOMER h
JOIN SAT_CUSTOMER_DEMOGRAPHICS d
  ON  d.HUB_CUSTOMER_HK = h.HUB_CUSTOMER_HK
 AND d.LOAD_DATE = (SELECT MAX(LOAD_DATE) FROM SAT_CUSTOMER_DEMOGRAPHICS
                    WHERE HUB_CUSTOMER_HK = h.HUB_CUSTOMER_HK
                      AND LOAD_DATE <= '2023-01-01')
JOIN SAT_CUSTOMER_CONTACT c
  ON  c.HUB_CUSTOMER_HK = h.HUB_CUSTOMER_HK
 AND c.LOAD_DATE = (SELECT MAX(LOAD_DATE) FROM SAT_CUSTOMER_CONTACT
                    WHERE HUB_CUSTOMER_HK = h.HUB_CUSTOMER_HK
                      AND LOAD_DATE <= '2023-01-01')
JOIN SAT_CUSTOMER_FINANCIALS f
  ON  f.HUB_CUSTOMER_HK = h.HUB_CUSTOMER_HK
 AND f.LOAD_DATE = (SELECT MAX(LOAD_DATE) FROM SAT_CUSTOMER_FINANCIALS
                    WHERE HUB_CUSTOMER_HK = h.HUB_CUSTOMER_HK
                      AND LOAD_DATE <= '2023-01-01');

-- ✅ WITH PIT: single snapshot date filter, direct equality joins — fast
SELECT h.CUSTOMER_BK, d.FIRST_NAME, c.EMAIL, f.CREDIT_LIMIT
FROM PIT_CUSTOMER pit
JOIN HUB_CUSTOMER h
  ON  h.HUB_CUSTOMER_HK    = pit.HUB_CUSTOMER_HK
JOIN SAT_CUSTOMER_DEMOGRAPHICS d
  ON  d.HUB_CUSTOMER_HK    = pit.HUB_CUSTOMER_HK
 AND d.LOAD_DATE           = pit.SAT_CUSTOMER_DEMOGRAPHICS_LDT
JOIN SAT_CUSTOMER_CONTACT c
  ON  c.HUB_CUSTOMER_HK    = pit.HUB_CUSTOMER_HK
 AND c.LOAD_DATE           = pit.SAT_CUSTOMER_CONTACT_LDT
JOIN SAT_CUSTOMER_FINANCIALS f
  ON  f.HUB_CUSTOMER_HK    = pit.HUB_CUSTOMER_HK
 AND f.LOAD_DATE           = pit.SAT_CUSTOMER_FINANCIALS_LDT
WHERE pit.SNAPSHOT_DATE = '2023-01-01';
-- LOAD_DATEs are pre-looked up. No MAX aggregations at runtime. 10–100× faster.

⚡

Performance impact: On a vault with 10 million customers and 4 satellites, the without-PIT query executes 40 million correlated subqueries per run. The with-PIT query performs a single date filter and 3 direct equality joins. Production benchmarks consistently show 10×–100× improvement after PIT tables are introduced.

Concept 03

Bridge Tables

🧠 ELI5 — Explain Like I’m Five

Sometimes you need to traverse multiple Hub–Link–Hub–Link–Hub chains to answer a query. “Give me all products ordered by customers in Germany.” Without a Bridge table, that’s 4–5 joins every time any report runs. A Bridge table pre-flattens that chain into one wide lookup table with all relevant hash keys, turning a complex traversal into a single lookup join.

Bridge Table Structure

A Bridge table is a snapshot-based wide table containing all the hash keys from a Hub–Link traversal chain. It is rebuilt periodically from the Raw Vault. Bridge tables do not replace vault structures — they are performance aids for known, frequently-run traversal patterns.

SQL — BRIDGE_CUSTOMER_ORDER_PRODUCT Table DDL

-- Bridge: Customer → Order → Product (pre-flattened relationship chain)
CREATE TABLE BRIDGE_CUSTOMER_ORDER_PRODUCT (
    BRIDGE_CUSTOMER_ORDER_PRODUCT_HK  CHAR(32)  NOT NULL,
    SNAPSHOT_DATE                     DATE      NOT NULL,
    -- All keys in the traversal chain, pre-joined into one row
    HUB_CUSTOMER_HK                   CHAR(32)  NOT NULL,
    LINK_CUSTOMER_ORDER_HK            CHAR(32),
    HUB_ORDER_HK                      CHAR(32),
    LINK_ORDER_PRODUCT_HK             CHAR(32),
    HUB_PRODUCT_HK                    CHAR(32),
    CONSTRAINT pk_bridge_cop PRIMARY KEY (BRIDGE_CUSTOMER_ORDER_PRODUCT_HK, SNAPSHOT_DATE)
);

SQL — Bridge Generation

INSERT INTO BRIDGE_CUSTOMER_ORDER_PRODUCT
SELECT
    MD5(
        h_c.HUB_CUSTOMER_HK || '||' ||
        h_p.HUB_PRODUCT_HK  || '||' ||
        CAST(d.SNAPSHOT_DATE AS VARCHAR)
    ) AS BRIDGE_CUSTOMER_ORDER_PRODUCT_HK,
    d.SNAPSHOT_DATE,
    h_c.HUB_CUSTOMER_HK,
    lco.LINK_CUSTOMER_ORDER_HK,
    h_o.HUB_ORDER_HK,
    lop.LINK_ORDER_PRODUCT_HK,
    h_p.HUB_PRODUCT_HK
FROM HUB_CUSTOMER h_c
JOIN LINK_CUSTOMER_ORDER  lco  ON lco.HUB_CUSTOMER_HK = h_c.HUB_CUSTOMER_HK
JOIN HUB_ORDER             h_o  ON h_o.HUB_ORDER_HK    = lco.HUB_ORDER_HK
JOIN LINK_ORDER_PRODUCT   lop  ON lop.HUB_ORDER_HK    = h_o.HUB_ORDER_HK
JOIN HUB_PRODUCT           h_p  ON h_p.HUB_PRODUCT_HK  = lop.HUB_PRODUCT_HK
CROSS JOIN (SELECT CURRENT_DATE() AS SNAPSHOT_DATE) d;

Hub–Link Chain Visualization

When to build a Bridge table: Build one when (1) you frequently need to traverse 3+ levels of vault hierarchy; (2) reports consistently join Customer → Order → Product or a similar chain that appears across many queries; (3) the traversal path is stable and won’t change schema often. Bridge tables are rebuilt from the Raw Vault periodically — they don’t affect vault integrity and can be dropped and regenerated at any time.

Concept 04

Computed Satellites & Reference Tables

🧠 ELI5 — Explain Like I’m Five

Sometimes you want to pre-calculate something expensive and store it as satellite history. A customer’s credit risk score. A product’s 30-day moving average price. A customer segment derived from purchase behaviour. These are Computed Satellites — they look like regular satellites, but their payload is derived from vault data rather than arriving from a source system. They live in the Business Vault, not the Raw Vault, and carry a BSAT_ prefix to make this clear.

Computed Satellite Rules

Naming convention — Use BSAT_ prefix (Business Satellite) or _COMPUTED suffix to clearly distinguish from Raw Vault satellites at a glance.
Derivation logic must be documented and versioned — The business rule (scoring formula, segment definition) must be tracked. The RECORD_SOURCE column identifies which rule version produced each row (e.g., 'BV.RISK_SCORING_ENGINE.V2').
Insert-only like all vault rows — If the rule changes, you do not update old rows. You insert new rows with the new computed values and a new RECORD_SOURCE. Old rows remain untouched.
Backtest capability is preserved — Old rows keep the old values, so you can always ask “what would the old model have given?” This is critical in regulated industries.
LOAD_DATE tracks computation time — Not the underlying event time, but when the computation ran.

SQL — BSAT_CUSTOMER_RISK_SCORE (Computed Satellite)

-- Computed Satellite: Customer Risk Score (Business Vault)
-- BSAT_ prefix identifies this as a Business Vault derived structure
CREATE TABLE BSAT_CUSTOMER_RISK_SCORE (
    HUB_CUSTOMER_HK   CHAR(32)     NOT NULL,
    LOAD_DATE         TIMESTAMP    NOT NULL,
    HASH_DIFF         CHAR(32)     NOT NULL,
    RECORD_SOURCE     VARCHAR(100) NOT NULL DEFAULT 'BV.RISK_SCORING_ENGINE.V2',
    -- Payload: entirely derived, not from any source system
    RISK_SCORE        DECIMAL(5,2),      -- 0.00 to 100.00
    RISK_CATEGORY     VARCHAR(20),       -- LOW, MEDIUM, HIGH, CRITICAL
    SCORE_VERSION     VARCHAR(10),       -- which model version produced this row
    COMPUTED_DATE     DATE,              -- date the computation ran
    CONSTRAINT pk_bsat_crs PRIMARY KEY (HUB_CUSTOMER_HK, LOAD_DATE)
);

-- When scoring model upgrades V2 → V3: insert new rows, never update old ones
INSERT INTO BSAT_CUSTOMER_RISK_SCORE
SELECT
    r.HUB_CUSTOMER_HK,
    CURRENT_TIMESTAMP()                              AS LOAD_DATE,
    MD5(CAST(r.new_score AS VARCHAR) || r.new_category) AS HASH_DIFF,
    'BV.RISK_SCORING_ENGINE.V3'                      AS RECORD_SOURCE, -- V3 explicit
    r.new_score                                      AS RISK_SCORE,
    r.new_category                                   AS RISK_CATEGORY,
    'V3'                                             AS SCORE_VERSION,
    CURRENT_DATE()                                   AS COMPUTED_DATE
FROM scoring_engine_v3_output r;
-- V2 rows are untouched: backtest queries can still retrieve V2 results.

Reference Tables in the Business Vault

Reference data — currency codes, country codes, product category hierarchies, cost centre mappings — also lives in the Business Vault. Rather than flat lookup tables, DV2 models reference data as Hub + Satellite pairs:

The Hub holds the code value (e.g., COUNTRY_CODE = 'DE'), hashed to produce the hub key.
The Satellite holds description, validity dates, and classification attributes (e.g., COUNTRY_NAME = 'Germany', REGION = 'EMEA').
When a code is renamed or reclassified, a new satellite row is inserted. The Hub key never changes, providing full history of the reference data itself.

✓ Computed Satellite Best Practices

Always use BSAT_ prefix to distinguish from raw satellites
Store the rule version in RECORD_SOURCE on every single row
Never update or delete old computed rows when a rule changes
Document the derivation logic and register it in a metadata catalogue
Use HASH_DIFF over payload columns to detect when a recomputed value has actually changed
Include a COMPUTED_DATE column to track when each run executed

✗ Common Mistakes

Placing computed satellites in the Raw Vault schema — blurs lineage and auditability
Updating rows when a business rule changes — destroys backtest capability permanently
Omitting the rule version from RECORD_SOURCE — V1 and V2 results become indistinguishable
Hard-coding derivation logic inside PIT generation instead of a dedicated BSAT table
Failing to re-run BSAT load after rule changes — stale derived values flow into reports
Treating computed satellites as permanent truth rather than derived, rebuildable artefacts

Concept 05

Interview Q&A: Business Vault

Business Vault Interview Questions

What is a PIT table and what problem does it solve? ▶

A Point-in-Time (PIT) table is a Business Vault structure that pre-materializes satellite LOAD_DATEs for each entity at specific snapshot dates. It solves the N × MAX(LOAD_DATE) subquery problem: without a PIT table, querying an entity’s state at a specific date requires one correlated subquery per satellite, each finding the most recent row as of the query date.

With a PIT table, all those LOAD_DATEs are pre-computed and stored — a query simply filters on SNAPSHOT_DATE and joins directly to the satellite rows using stored LOAD_DATE values, skipping aggregation entirely. This can improve query performance by 10–100× on large vault datasets. PIT tables are rebuilt periodically (typically nightly) from the Raw Vault and are always safe to truncate and rebuild.

How often should PIT tables be refreshed? ▶

It depends on your reporting SLA. Three common patterns:

1. Daily snapshot — PIT has one row per entity per calendar day, refreshed nightly. Good for SCD2-style reporting and month-end snapshots. Most common in enterprise implementations.

2. Load-event triggered — PIT is refreshed after each vault load batch, capturing the vault state after every load. Good for near-real-time reporting where users need vault data within minutes of each load completing.

3. On-demand (view-based) — PIT is generated at query time using a view or stored procedure. Good for ad-hoc analysis and time-travel but loses the performance benefit entirely because the MAX aggregations run on demand.

For most enterprise implementations, a nightly daily snapshot PIT is standard, with on-demand generation available as a fallback for ad-hoc time-travel queries.

What is the difference between the Business Vault and the Information Mart? ▶

The Business Vault is still structured as Hubs, Links, and Satellites — it is a normalized extension of the Raw Vault. It contains derived data (computed satellites, soft rule applications) and performance structures (PIT tables, Bridge tables) but preserves the vault’s historical fidelity and insert-only discipline. Its structures carry full lineage back to Raw Vault rows.

The Information Mart is consumer-facing: star schemas, flat wide tables, or OLAP cubes optimized for BI tools and data scientists. It is not historized in the vault sense — it presents current or period-summarized data in the shape business users expect.

Business Vault structures (PIT tables, Bridge tables) are intermediary building blocks that make constructing Information Mart structures efficient. The Business Vault evolves as business rules change; Information Marts are rebuilt from it. Neither layer is queried directly against the Raw Vault.

Can a Computed Satellite change its values? What happens if the business rule changes? ▶

No — computed satellite rows are insert-only like all vault rows. If a business rule changes (e.g., the risk scoring model is updated from V2 to V3), you do not update old rows. You insert new satellite rows with the new computed values and a new RECORD_SOURCE identifying the new rule version (e.g., 'BV.RISK_SCORING_ENGINE.V3').

Old rows retain the values computed under the old rule. This means you can always backtest: “what risk score would the old model have given this customer?” — the answer lives in the rows with RECORD_SOURCE = 'BV.RISK_SCORING_ENGINE.V2'.

This auditability of rule versions is a significant advantage in regulated industries (banking, insurance, healthcare) where you must prove that a decision was made using a specific, identifiable version of a model or formula.

What’s a Bridge table and when would you build one? ▶

A Bridge table is a Business Vault structure that pre-flattens a Hub–Link–Hub–Link chain into a wide lookup table containing all relevant hash keys in a single row. It is snapshot-based and rebuilt periodically from the Raw Vault.

Build one when:

(1) You frequently need to traverse 3+ levels of vault hierarchy in queries — the traversal cost is paid once at build time, not at every query execution.

(2) Reports consistently join Customer → Order → Product or similar multi-level chains — the same traversal pattern appearing across many different reports is a strong signal a Bridge table will pay off.

(3) The traversal path is stable and its schema won’t change frequently. Bridge tables are worth the maintenance cost only for well-established paths.

Bridge tables don’t affect vault integrity because they are derived artefacts that can be dropped and fully rebuilt from the Raw Vault at any time.

✅ Module 07 Key Takeaways

The Business Vault sits between the Raw Vault and Information Marts — it enhances raw data with business context (soft rules, derived values, query optimization) without ever touching Raw Vault rows.
PIT tables solve the N × MAX(LOAD_DATE) subquery problem by pre-materializing satellite LOAD_DATEs per snapshot date, delivering 10–100× query performance improvements on large vaults.
Bridge tables pre-flatten Hub–Link–Hub traversal chains into wide lookup rows, converting multi-join traversals into single-lookup joins for frequently-used relationship paths.
Computed Satellites (BSAT_ prefix) store derived metrics as insert-only satellite history. When business rules change, new rows are inserted with the new rule version in RECORD_SOURCE — old rows are never altered, enabling backtest and rule version auditing.
All Business Vault structures are derived from the Raw Vault and can be dropped and fully rebuilt without data loss. The Raw Vault is always the permanent, unalterable source of truth.