Enterprise
Case Studies

-- Same-As Link: two Hub rows that represent the same real-world customer
-- Used to reconcile customer IDs across 14 core banking systems
CREATE TABLE LINK_CUSTOMER_SAME_AS (
    LINK_CUSTOMER_SAME_AS_HK  CHAR(32)     NOT NULL,  -- hash of both HKs concatenated
    HUB_CUSTOMER_HK_MASTER    CHAR(32)     NOT NULL,  -- the surviving / golden record HK
    HUB_CUSTOMER_HK_DUPLICATE CHAR(32)     NOT NULL,  -- the matched / subordinate HK
    LOAD_DATE                  TIMESTAMP    NOT NULL,
    RECORD_SOURCE              VARCHAR(100) NOT NULL,  -- MDM system that determined match
    CONSTRAINT pk_lnk_cust_same PRIMARY KEY (LINK_CUSTOMER_SAME_AS_HK)
);

-- Satellite on Same-As Link: match quality metadata
CREATE TABLE SAT_L_CUSTOMER_SAME_AS_MATCH_DETAIL (
    LINK_CUSTOMER_SAME_AS_HK  CHAR(32)    NOT NULL,
    LOAD_DATE                  TIMESTAMP   NOT NULL,
    HASH_DIFF                  CHAR(32)    NOT NULL,
    RECORD_SOURCE              VARCHAR(100)NOT NULL,
    MATCH_SCORE                DECIMAL(5,4),           -- 0.0000 to 1.0000
    MATCH_METHOD               VARCHAR(50),            -- EXACT_SSN, FUZZY_NAME_DOB, etc.
    MATCH_CONFIDENCE           VARCHAR(20),            -- HIGH / MEDIUM / LOW
    REVIEWED_BY                VARCHAR(100),           -- human reviewer ID if manual
    REVIEW_DATE                TIMESTAMP,
    CONSTRAINT pk_sat_match PRIMARY KEY (LINK_CUSTOMER_SAME_AS_HK, LOAD_DATE)
);

-- Customer 360: all purchase and return events in the last 90 days
-- Bridge table pre-joins CUSTOMER to all related ORDER and RETURN links
SELECT
    c.EMAIL                         AS CUSTOMER_EMAIL,
    p.PRODUCT_NAME, p.GTIN,
    ch.CHANNEL_NAME,
    ev.EVENT_TYPE,                  -- PURCHASE or RETURN
    ev.EVENT_DATE,
    ev.QUANTITY, ev.NET_AMOUNT_USD

FROM  BIZ_VAULT.BRIDGE_CUSTOMER_EVENTS  br
JOIN  MART.DIM_CUSTOMER                 c   ON c.CUSTOMER_HK  = br.HUB_CUSTOMER_HK
JOIN  MART.DIM_PRODUCT                  p   ON p.PRODUCT_HK   = br.HUB_PRODUCT_HK
JOIN  MART.DIM_CHANNEL                  ch  ON ch.CHANNEL_HK  = br.HUB_CHANNEL_HK
JOIN  SAT_L_CUSTOMER_EVENTS_DETAIL      ev  ON ev.LINK_HK     = br.LINK_HK
                                                AND ev.LOAD_END_DATE IS NULL
WHERE c.EMAIL = 'jane.doe@email.com'
  AND ev.EVENT_DATE >= DATEADD('day', -90, CURRENT_DATE())
ORDER BY ev.EVENT_DATE DESC;

-- PHI Satellite: access controlled to identity team only
-- Clinical research views NEVER join to this table
CREATE TABLE SAT_PATIENT_PHI (
    HUB_PATIENT_HK  CHAR(32)     NOT NULL,
    LOAD_DATE        TIMESTAMP    NOT NULL,
    HASH_DIFF        CHAR(32)     NOT NULL,
    RECORD_SOURCE    VARCHAR(100) NOT NULL,
    -- PHI columns: role-secured; masked in non-prod via dynamic masking policy
    FIRST_NAME       VARCHAR(200),
    LAST_NAME        VARCHAR(200),
    DATE_OF_BIRTH    DATE,
    SSN_HASH         CHAR(64),   -- SHA-256 of SSN — not stored in plaintext
    MRN              VARCHAR(50), -- Medical Record Number per facility
    FACILITY_CODE    CHAR(10),
    CONSTRAINT pk_sat_phi PRIMARY KEY (HUB_PATIENT_HK, LOAD_DATE)
);
-- Snowflake Dynamic Data Masking: clinical role sees masked name
CREATE OR REPLACE MASKING POLICY phi_mask_policy AS
    (val VARCHAR) RETURNS VARCHAR ->
    CASE WHEN CURRENT_ROLE() IN ('PHI_ADMIN', 'IDENTITY_TEAM') THEN val
         ELSE '***MASKED***'
    END;
ALTER TABLE SAT_PATIENT_PHI MODIFY COLUMN FIRST_NAME
    SET MASKING POLICY phi_mask_policy;

-- Clinical satellite: no PHI — safe for research teams
CREATE TABLE SAT_PATIENT_CLINICAL_ENCOUNTER (
    HUB_PATIENT_HK   CHAR(32)     NOT NULL,
    LOAD_DATE         TIMESTAMP    NOT NULL,
    HASH_DIFF         CHAR(32)     NOT NULL,
    RECORD_SOURCE     VARCHAR(100) NOT NULL,
    ENCOUNTER_DATE    DATE         NOT NULL,
    ICD10_CODE        VARCHAR(20),  -- diagnosis code
    ENCOUNTER_TYPE    VARCHAR(50),  -- INPATIENT, OUTPATIENT, EMERGENCY
    FACILITY_CODE     CHAR(10),
    ATTENDING_PROVIDER_HK CHAR(32),-- FK to HUB_PROVIDER (de-identified)
    CONSTRAINT pk_sat_encounter PRIMARY KEY (HUB_PATIENT_HK, LOAD_DATE)
);

-- Daily reconciliation: compare legacy DW FACT_SALES to new MART.FACT_SALES
-- Run as a pipeline check during parallel run phase
WITH legacy_totals AS (
    SELECT
        DATE_TRUNC('day', TRANSACTION_DATE)  AS SALE_DATE,
        COUNT(*)                               AS ROW_COUNT,
        SUM(NET_AMOUNT)                        AS TOTAL_SALES
    FROM LEGACY_DW.FACT_SALES
    WHERE TRANSACTION_DATE = CURRENT_DATE() - 1
    GROUP BY 1
),
dv_totals AS (
    SELECT
        DATE_TRUNC('day', ORDER_DATE)         AS SALE_DATE,
        COUNT(*)                               AS ROW_COUNT,
        SUM(NET_SALES_AMOUNT)                  AS TOTAL_SALES
    FROM MART.FACT_SALES
    WHERE ORDER_DATE = CURRENT_DATE() - 1
    GROUP BY 1
)
SELECT
    l.SALE_DATE,
    l.ROW_COUNT          AS LEGACY_ROWS,
    d.ROW_COUNT          AS DV_ROWS,
    l.ROW_COUNT - d.ROW_COUNT              AS ROW_DELTA,
    l.TOTAL_SALES        AS LEGACY_SALES,
    d.TOTAL_SALES        AS DV_SALES,
    l.TOTAL_SALES - d.TOTAL_SALES           AS SALES_DELTA,
    ABS(l.TOTAL_SALES - d.TOTAL_SALES) /
        NULLIF(l.TOTAL_SALES, 0) * 100       AS PCT_VARIANCE
FROM legacy_totals l
FULL OUTER JOIN dv_totals d USING (SALE_DATE)
WHERE ABS(l.TOTAL_SALES - d.TOTAL_SALES) / NULLIF(l.TOTAL_SALES, 0) * 100 > 0.01;
-- Alert if variance exceeds 0.01% — investigate root cause before cutover

-- GDPR erasure satellite: records that a subject has exercised right-to-erasure
-- Raw Vault rows are NEVER deleted — tombstone controls mart visibility
CREATE TABLE SAT_CUSTOMER_GDPR_ERASURE (
    HUB_CUSTOMER_HK  CHAR(32)     NOT NULL,
    LOAD_DATE        TIMESTAMP    NOT NULL,
    HASH_DIFF        CHAR(32)     NOT NULL,
    RECORD_SOURCE    VARCHAR(100) NOT NULL,
    ERASURE_DATE     TIMESTAMP    NOT NULL,
    ERASURE_REQUEST_ID VARCHAR(100),         -- ticket/case reference
    ERASURE_SCOPE    VARCHAR(50)  DEFAULT 'FULL',
    CONSTRAINT pk_sat_gdpr PRIMARY KEY (HUB_CUSTOMER_HK, LOAD_DATE)
);

-- Mart view: automatically excludes erased customers
CREATE OR REPLACE VIEW MART.DIM_CUSTOMER_GDPR_SAFE AS
SELECT c.*
FROM MART.DIM_CUSTOMER c
WHERE NOT EXISTS (
    SELECT 1 FROM SAT_CUSTOMER_GDPR_ERASURE er
    WHERE er.HUB_CUSTOMER_HK = c.HUB_CUSTOMER_HK
      AND er.LOAD_END_DATE IS NULL
      AND er.ERASURE_DATE <= CURRENT_TIMESTAMP()
);