Performance
Optimization

-- ── HUB INDEXES ─────────────────────────────────────────────
CREATE UNIQUE INDEX idx_hub_customer_pk ON HUB_CUSTOMER (HUB_CUSTOMER_HK);
CREATE INDEX        idx_hub_customer_bk ON HUB_CUSTOMER (CUSTOMER_BK);

-- ── LINK INDEXES ─────────────────────────────────────────────
CREATE UNIQUE INDEX idx_link_co_pk        ON LINK_CUSTOMER_ORDER (LINK_CUSTOMER_ORDER_HK);
CREATE INDEX        idx_link_co_customer   ON LINK_CUSTOMER_ORDER (HUB_CUSTOMER_HK);
CREATE INDEX        idx_link_co_order      ON LINK_CUSTOMER_ORDER (HUB_ORDER_HK);
CREATE INDEX        idx_link_co_composite  ON LINK_CUSTOMER_ORDER (HUB_CUSTOMER_HK, HUB_ORDER_HK);

-- ── SATELLITE INDEXES ────────────────────────────────────────
-- Filtered index: covers the dominant "current record" query pattern
CREATE INDEX idx_sat_cd_current ON SAT_CUSTOMER_DEMOGRAPHICS (HUB_CUSTOMER_HK)
    WHERE LOAD_END_DATE IS NULL;    -- partial index: current records only

-- HashDiff index: change detection during satellite loader
CREATE INDEX idx_sat_cd_hdiff   ON SAT_CUSTOMER_DEMOGRAPHICS (HUB_CUSTOMER_HK, HASH_DIFF);

-- ── CLOUD PLATFORMS (Snowflake / BigQuery) ───────────────────
-- Traditional B-tree indexes replaced by clustering keys
ALTER TABLE SAT_CUSTOMER_DEMOGRAPHICS
    CLUSTER BY (HUB_CUSTOMER_HK);   -- co-locate all rows for the same entity

-- Instead of rebuilding the entire PIT table daily,
-- only insert rows for dates that changed since last run
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
    -- ... repeat correlated subquery for each satellite in PIT scope
FROM HUB_CUSTOMER h
CROSS JOIN (SELECT CURRENT_DATE() AS SNAPSHOT_DATE) d

-- Guard 1: only process entities with NEW satellite data since last PIT refresh
WHERE EXISTS (
    SELECT 1 FROM SAT_CUSTOMER_DEMOGRAPHICS s
    WHERE s.HUB_CUSTOMER_HK = h.HUB_CUSTOMER_HK
      AND s.LOAD_DATE >= (
          SELECT COALESCE(MAX(LOAD_DATE), '1900-01-01')
          FROM   PIT_CUSTOMER p
          WHERE  p.HUB_CUSTOMER_HK = h.HUB_CUSTOMER_HK
      )
)

-- Guard 2: skip if this (HK, SNAPSHOT_DATE) row already exists
AND NOT EXISTS (
    SELECT 1 FROM PIT_CUSTOMER p
    WHERE p.HUB_CUSTOMER_HK = h.HUB_CUSTOMER_HK
      AND  p.SNAPSHOT_DATE   = d.SNAPSHOT_DATE
);

-- Modern SQL: LATERAL JOIN for ad-hoc point-in-time satellite lookups
-- No pre-built PIT table required
SELECT
    h.CUSTOMER_BK,
    d_snap.FIRST_NAME,
    d_snap.LAST_NAME,
    c_snap.EMAIL
FROM HUB_CUSTOMER h
CROSS JOIN (SELECT DATE '2023-01-01' AS snap_date) p

-- LATERAL: for each Hub row, find the as-of demographics row
JOIN LATERAL (
    SELECT FIRST_NAME, LAST_NAME
    FROM  SAT_CUSTOMER_DEMOGRAPHICS s
    WHERE s.HUB_CUSTOMER_HK = h.HUB_CUSTOMER_HK
      AND s.LOAD_DATE <= p.snap_date
    ORDER BY s.LOAD_DATE DESC LIMIT 1
) d_snap ON TRUE

-- LATERAL: for each Hub row, find the as-of contact row
JOIN LATERAL (
    SELECT EMAIL
    FROM  SAT_CUSTOMER_CONTACT s
    WHERE s.HUB_CUSTOMER_HK = h.HUB_CUSTOMER_HK
      AND s.LOAD_DATE <= p.snap_date
    ORDER BY s.LOAD_DATE DESC LIMIT 1
) c_snap ON TRUE

ORDER BY h.CUSTOMER_BK;

-- ── Snowflake: cluster by load date ─────────────────────────
-- Automatic micro-partition pruning on the clustered column
ALTER TABLE SAT_CUSTOMER_DEMOGRAPHICS
    CLUSTER BY (TO_DATE(LOAD_DATE));

-- ── PostgreSQL: declarative range partitioning ───────────────
CREATE TABLE SAT_CUSTOMER_DEMOGRAPHICS (
    HUB_CUSTOMER_HK  CHAR(32)      NOT NULL,
    LOAD_DATE        TIMESTAMP     NOT NULL,
    LOAD_END_DATE    TIMESTAMP         NULL,
    HASH_DIFF        CHAR(32)      NOT NULL,
    RECORD_SOURCE    VARCHAR(100)  NOT NULL,
    FIRST_NAME       VARCHAR(100),
    LAST_NAME        VARCHAR(100),
    EMAIL            VARCHAR(200),
    PRIMARY KEY (HUB_CUSTOMER_HK, LOAD_DATE)
) PARTITION BY RANGE (LOAD_DATE);

-- Create quarterly child partitions
CREATE TABLE SAT_CUSTOMER_DEMOGRAPHICS_2023_Q1
    PARTITION OF SAT_CUSTOMER_DEMOGRAPHICS
    FOR VALUES FROM ('2023-01-01') TO ('2023-04-01');

CREATE TABLE SAT_CUSTOMER_DEMOGRAPHICS_2023_Q2
    PARTITION OF SAT_CUSTOMER_DEMOGRAPHICS
    FOR VALUES FROM ('2023-04-01') TO ('2023-07-01');

-- Cluster on BOTH hash key AND load date for maximum pruning efficiency
-- Satellite queries filter on HK (entity lookup) AND LOAD_DATE (time slice)
ALTER TABLE SAT_CUSTOMER_DEMOGRAPHICS
    CLUSTER BY (HUB_CUSTOMER_HK, TO_DATE(LOAD_DATE));

-- Query 1: entity-scoped history — prunes on HUB_CUSTOMER_HK
SELECT * FROM SAT_CUSTOMER_DEMOGRAPHICS
WHERE  HUB_CUSTOMER_HK = 'a1b2c3d4e5f6a1b2c3d4e5f6a1b2c3d4'
ORDER BY LOAD_DATE DESC;

-- Query 2: time-bounded audit — prunes on LOAD_DATE
-- scans only January 2023 micro-partitions, not all history
SELECT * FROM SAT_CUSTOMER_DEMOGRAPHICS
WHERE  LOAD_DATE >= '2023-01-01'
  AND  LOAD_DATE  <  '2023-02-01';

-- Materialized current-customer view (refreshed after each vault load)
-- Lives in the Information Mart or Business Vault layer — NEVER Raw Vault
CREATE MATERIALIZED VIEW MV_CUSTOMER_CURRENT AS
SELECT
    h.HUB_CUSTOMER_HK,
    h.CUSTOMER_BK,
    -- From demographics satellite (current row only)
    d.FIRST_NAME,
    d.LAST_NAME,
    -- From contact satellite (current row only)
    c.EMAIL,
    c.PHONE,
    -- From risk score Business Satellite (current row only)
    f.CREDIT_LIMIT,
    f.RISK_CATEGORY
FROM HUB_CUSTOMER h

-- LEFT JOIN so customers with no satellite row still appear
LEFT JOIN SAT_CUSTOMER_DEMOGRAPHICS d
    ON  d.HUB_CUSTOMER_HK = h.HUB_CUSTOMER_HK
    AND d.LOAD_END_DATE IS NULL

LEFT JOIN SAT_CUSTOMER_CONTACT c
    ON  c.HUB_CUSTOMER_HK = h.HUB_CUSTOMER_HK
    AND c.LOAD_END_DATE IS NULL

LEFT JOIN BSAT_CUSTOMER_RISK_SCORE f
    ON  f.HUB_CUSTOMER_HK = h.HUB_CUSTOMER_HK
    AND f.LOAD_END_DATE IS NULL;

-- Refresh after each vault load wave completes (PostgreSQL syntax):
-- REFRESH MATERIALIZED VIEW MV_CUSTOMER_CURRENT;
-- On Snowflake: replace with a Dynamic Table for managed incremental refresh