Advanced
Data Vault 2.0

-- Bi-temporal satellite: two independent time dimensions
CREATE TABLE SAT_ACCOUNT_BALANCE_BITEMPORAL (
    HUB_ACCOUNT_HK   CHAR(32)      NOT NULL,
    -- Transaction Time: when loaded into Data Vault (immutable, system-set)
    LOAD_DATE        TIMESTAMP     NOT NULL,
    LOAD_END_DATE    TIMESTAMP     NULL,
    -- Valid Time: when the fact was true in the real world (business-provided)
    VALID_FROM       DATE          NOT NULL,  -- business effective date
    VALID_TO         DATE          NULL,      -- NULL = currently valid
    HASH_DIFF        CHAR(32)      NOT NULL,
    RECORD_SOURCE    VARCHAR(100)  NOT NULL,
    -- Payload
    BALANCE          DECIMAL(18,4),
    BALANCE_TYPE     VARCHAR(20),
    CONSTRAINT pk_sat_ab_bt PRIMARY KEY (HUB_ACCOUNT_HK, LOAD_DATE)
);

-- Q: Balance as of Jan 1, 2023 AS WE UNDERSTOOD IT on that date
--    Both Valid Time AND Transaction Time anchored to '2023-01-01'
SELECT
    sat.BALANCE,
    sat.BALANCE_TYPE,
    sat.VALID_FROM,
    sat.LOAD_DATE   AS WHEN_RECORDED
FROM SAT_ACCOUNT_BALANCE_BITEMPORAL sat
WHERE sat.HUB_ACCOUNT_HK = 'abc123...'
  -- Valid Time filter: the fact was true on Jan 1
  AND sat.VALID_FROM     <= '2023-01-01'
  AND (sat.VALID_TO      IS NULL OR sat.VALID_TO      > '2023-01-01')
  -- Transaction Time filter: we had loaded it by Jan 1
  AND sat.LOAD_DATE      <= '2023-01-01'
  AND (sat.LOAD_END_DATE IS NULL OR sat.LOAD_END_DATE > '2023-01-01');

-- Swap LOAD_DATE filter to CURRENT_TIMESTAMP() to ask:
-- "What do we NOW KNOW was true on Jan 1?" (late corrections included)

-- ✗ BAD: soft business rule baked into staging / load layer
--   If STATUS_CODE meanings change, raw history is permanently corrupted
SELECT
    src.CUSTOMER_ID,
    CASE
        WHEN src.STATUS_CODE = 'A' THEN 'Active'
        WHEN src.STATUS_CODE = 'I' THEN 'Inactive'
        ELSE 'Unknown'
    END AS STATUS_DESCRIPTION   -- soft rule in the WRONG layer!
FROM raw_source.customers src;

-- ✓ GOOD: soft rule lives in a Business Vault computed satellite (BSAT)
--   Raw Vault keeps STATUS_CODE='A' unchanged forever
--   BSAT re-derives the label whenever the rule or reference table changes
INSERT INTO BSAT_CUSTOMER_STATUS_RESOLVED
SELECT
    sat.HUB_CUSTOMER_HK,
    CURRENT_TIMESTAMP()                                        AS LOAD_DATE,
    MD5(sat.STATUS_CODE || '||' || ref.STATUS_DESCRIPTION)    AS HASH_DIFF,
    'BV.STATUS_RESOLUTION_RULE_V3'                             AS RECORD_SOURCE,
    sat.STATUS_CODE,
    ref.STATUS_DESCRIPTION,    -- looked up from BV reference table
    ref.IS_ACTIVE_FLAG
FROM SAT_CUSTOMER_STATUS sat
JOIN BREF_STATUS_CODES ref
    ON ref.STATUS_CODE = sat.STATUS_CODE
WHERE sat.LOAD_END_DATE IS NULL;   -- current satellite records only

-- When mapping changes (e.g., 'A' now means 'Archived'):
--   1. Update BREF_STATUS_CODES
--   2. Re-run the BSAT load — new rows reflect new rule
--   3. Raw Vault untouched — complete audit trail intact

-- SAL_CUSTOMER: stores MDM match decisions in the Business Vault
-- Decision: "CRM-1001" IS THE SAME ENTITY AS "ERP-XYZ-99"
INSERT INTO SAL_CUSTOMER (
    SAL_CUSTOMER_HK,
    DRIVER_HUB_CUSTOMER_HK,
    DEPENDENT_HUB_CUSTOMER_HK,
    LOAD_DATE,
    RECORD_SOURCE
)
VALUES (
    MD5('CRM-1001' || '||' || 'ERP-XYZ-99'),
    MD5('CRM-1001'),     -- DRIVER: canonical / golden identity
    MD5('ERP-XYZ-99'),   -- DEPENDENT: matched alternative key
    CURRENT_TIMESTAMP(),
    'MDM.MASTER_DATA_HUB.MATCH_PROCESS_V2'
);

-- A satellite on the SAL stores match metadata:
-- confidence score, algorithm, manual_review_flag, effective dates

-- Business Vault view: resolve SAL to produce a unified "golden" key
-- COALESCE: if this hub key is a DEPENDENT in the SAL, resolve to its DRIVER
CREATE VIEW VW_BV_CUSTOMER_GOLDEN AS
SELECT
    COALESCE(
        sal.DRIVER_HUB_CUSTOMER_HK,
        h.HUB_CUSTOMER_HK
    )                      AS GOLDEN_HK,    -- canonical identity
    h.HUB_CUSTOMER_HK      AS ORIGINAL_HK,   -- source identity preserved
    h.CUSTOMER_BK,
    h.RECORD_SOURCE
FROM HUB_CUSTOMER h
LEFT JOIN SAL_CUSTOMER sal
    ON sal.DEPENDENT_HUB_CUSTOMER_HK = h.HUB_CUSTOMER_HK;

-- CRM-1001, ERP-XYZ-99, and USR-44821 now share one GOLDEN_HK
-- Original source keys remain intact and individually queryable