Applying SCD Type 2 to monthly credit band changes causes a massive explosion in dimension row counts and degrades join performance across all queries. The proper solution is to extract the volatile attributes into a separate mini-dimension of discrete banded combinations and link the fact table to both the base customer and the mini-dimension.
Why Type 2 row explosion happens
If a company has ten million customers and updates their credit score band every month, an SCD Type 2 dimension adds 120 million rows every year. Within three years, the dimension table balloons to hundreds of millions of records. This rapid growth creates serious operational issues:
- Dimension table size dwarfs transaction facts, bloating memory during joins.
- Storage indexes grow unwieldy and degrade point lookups.
- Simple customer name searches require scanning millions of historical version rows.
Mini-dimension banding approach
Instead of storing continuous volatile values on the customer row, group them into discrete bands in a standalone table:
dim_credit_profile credit_profile_key | credit_score_band | income_bracket | risk_tier 1 | 750-799 | 75k-100k | Low 2 | 700-749 | 50k-75k | Medium 3 | 650-699 | 50k-75k | High
A short summary of the mini-dimension structure:
- The table contains only the distinct combinations of discrete categories.
- Total row count remains small, often under a few thousand rows.
- The mini-dimension stays virtually static over time.
Connecting facts to profiles
The transaction fact table (fact_loan_payments or fact_orders) captures two foreign keys at load time: customer_key pointing to the stable customer dimension, and credit_profile_key pointing to the customer score band at that moment. Alternatively, a monthly periodic snapshot fact records the customer monthly standing. This isolates volatility, prevents table bloat, and keeps customer lookups fast.