Slowly Changing Dimensions & Data Modeling

Investment Bank Interview, Question 6 — a detailed model answer for Senior Data Engineer / Data Architect roles

Interview Question

Design the data model for a counterparty/instrument reference data system at a global investment bank. Counterparty attributes (credit rating, legal entity, risk classification) change over time, and risk reports must be reproducible as-of any historical date. Explain your dimensional model, how you handle Slowly Changing Dimensions, and how you implement SCD Type 2 efficiently at scale with CDC.

Figure 6 — Star schema with SCD Type 2 dimensions and a counterparty version timeline

Why This Matters: Point-in-Time Correctness

In finance, a risk report run today for a past date MUST reflect the counterparty’s credit rating AS IT WAS on that date, not today’s rating. This ‘as-of’ reproducibility is a regulatory requirement. It’s the core reason SCD Type 2 exists.

Dimensional Model — Star Schema

• Fact table: fact_trades — grain is one row per trade, with foreign keys to dimensions and additive measures (notional, delta, P&L)
• Dimension: dim_counterparty (SCD Type 2) — credit rating, legal entity, risk class, with effective/expiry dates
• Dimension: dim_instrument (SCD Type 2) — asset class, sector, maturity, with versioning
• Dimension: dim_date — standard calendar dimension with business-day flags

Get Lakshman Gupta’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

SCD Types — When to Use Each

SCD Type 2 Structure

dim_counterparty
--------------------------------
counterparty_sk    BIGINT   -- surrogate key (unique per version)
counterparty_id    STRING   -- natural/business key (stable)
credit_rating      STRING
legal_entity       STRING
risk_class         STRING
effective_date     DATE     -- when this version became valid
expiry_date        DATE     -- when superseded (9999-12-31 if current)
is_current         BOOLEAN  -- fast filter for current version
hash_diff          STRING   -- hash of tracked columns for change detection

The fact table joins to the dimension on the surrogate key captured at trade time, so it permanently points to the counterparty version that was valid when the trade was booked. As-of queries filter the dimension on effective_date <= as_of_date < expiry_date.

Implementing SCD Type 2 at Scale with CDC

• CDC capture — Debezium or HVR captures inserts/updates/deletes from the source reference DB into Kafka
• Change detection — compute a hash_diff of tracked columns; only create a new version if the hash changed (ignores no-op updates)
• MERGE pattern — use Delta Lake / Iceberg MERGE to expire the old version (set expiry_date, is_current=false) and insert the new version in one atomic transaction
• Partitioning — partition the dimension by is_current or effective year so as-of queries prune efficiently

-- Changed rows are staged twice: once to expire the old version (merge_key = id)
-- and once with merge_key = NULL so they fall through to INSERT as the new version
MERGE INTO dim_counterparty tgt
USING (
  SELECT s.counterparty_id AS merge_key, s.* FROM staged_changes s
  UNION ALL
  SELECT NULL AS merge_key, s.* FROM staged_changes s
  JOIN dim_counterparty d
    ON d.counterparty_id = s.counterparty_id AND d.is_current = true
   AND d.hash_diff <> s.hash_diff
) src
  ON tgt.counterparty_id = src.merge_key AND tgt.is_current = true
WHEN MATCHED AND tgt.hash_diff <> src.hash_diff THEN
  UPDATE SET expiry_date = src.effective_date, is_current = false
WHEN NOT MATCHED THEN
  INSERT (...) VALUES (...)  -- effective now, expiry 9999-12-31, is_current true

Key phrase: ‘SCD Type 2 with surrogate keys is non-negotiable for reproducible as-of risk reporting. I detect real changes with a hash_diff to avoid version bloat, and apply them atomically with a Delta/Iceberg MERGE driven by Debezium CDC. The fact captures the surrogate key at booking time, so history is immutable.’

Disclaimer: This is a hypothetical architecture written for interview preparation. It does not describe any specific firm’s actual systems, and I am not affiliated with any bank named or implied here.