Slowly Changing Dimensions (SCD) in iGaming: Definition, Types and Why Handling History Correctly Matters
Slowly Changing Dimensions (SCD) are the data modelling techniques for handling dimensional attributes that change over time: a customer's VIP tier, a game's RTP variant, a market's regulatory status. The question SCD answers is: when an attribute changes, do we overwrite the old…
TL;DR
Slowly Changing Dimensions (SCD) are the data modelling techniques for handling dimensional attributes that change over time: a customer's VIP tier, a game's RTP variant, a market's regulatory status. The question SCD answers is: when an attribute changes, do we overwrite the old value, keep history or both? The answer affects historical reporting, time-series analysis, regulatory accuracy and ML training. Getting SCD wrong is a quiet way to produce systematically inconsistent analytics across an organisation.
How it works
Slowly Changing Dimensions come in several documented types, with Types 1, 2 and 3 being the most common:
- Type 0: dimensions never change after initial load (rare for most attributes).
- Type 1: overwrite the old value with the new value, no history preserved.
- Type 2: preserve full history through versioned rows (each version has effective_from and effective_to dates).
- Type 3: limited history through additional columns (current value and one previous value).
- Type 4: separate history table preserving all changes (the dimension always shows current values).
- Type 6 (Hybrid): combines Type 1, 2 and 3 capabilities for complex requirements.
The choice depends on whether historical analytical questions need to use historical values. A customer's VIP tier as of when they placed a specific bet (Type 2) is different from a customer's current VIP tier applied retrospectively to old bets (Type 1). Both are valid choices for different analytical needs.
Why it matters in iGaming
iGaming has many dimensional attributes that change over time: customer VIP tiers, customer segments, game RTP variants (providers sometimes offer multiple RTP configurations), market regulatory status, payment method risk classifications, KYC verification levels. How these changes are handled affects historical reporting, regulatory accuracy and ML model training. The wrong SCD choice produces systematic inconsistencies that compound over time.
Different teams care about SCD differently:
- Finance needs historical accuracy for regulatory reporting (correct customer tier at point of transaction).
- CRM analytics needs historical context (which segment was the customer in when they engaged with a campaign).
- ML teams need consistent time-aligned training data.
- Compliance audits often require historical attribute values as they existed at specific points.
- Executive reporting may use Type 1 attributes deliberately (always show current segment performance).
SCD handling is also where common analytical inconsistencies emerge. An operator that uses Type 1 for customer segments produces month-over-month reports where last month's segment changes appear in this month's historical view. An operator using Type 2 produces historically-accurate views that differ from current-state views in ways that need explicit handling. Neither approach is universally correct; both need to be applied consistently with documented choices.
Common mistakes and how operators get SCD wrong
No SCD strategy. Dimensions overwriting attributes without explicit choice produce inconsistent historical reporting. The default behaviour (typically Type 1) may not match business needs.
Type 1 for everything. Always overwriting prevents historical analytical accuracy. Regulatory reporting suffers, ML training data becomes inconsistent, time-series analysis loses meaning.
Type 2 for everything. Preserving full history for every attribute produces huge dimension tables and complex queries. Some attributes legitimately don't need history (display labels, descriptions); applying Type 2 universally produces unnecessary complexity.
Type choice without documentation. SCD choices that aren't documented produce confusion when analysts inherit dimensional models. New analyses may make wrong assumptions about whether attributes preserve history.
Effective dating inconsistencies. Type 2 dimensions need consistent effective_from and effective_to dating. Gaps, overlaps or timezone issues produce wrong attribute attribution for facts.
SCD changes ad hoc. Changing an attribute's SCD type after the dimension is in use requires careful migration and historical reconstruction. Operators that change SCD treatment casually produce broken historical reports.
What good looks like
SCD practices observed in well-run operators:
- Explicit SCD choice per attribute, documented in dimensional model definitions.
- Type 2 for attributes where historical accuracy matters.
- Type 1 for attributes where current state is the only relevant view.
- Consistent effective dating with timezone discipline.
- Migration processes for SCD type changes.
- Integration with downstream metric layer respecting SCD choices.
How Gamblitude handles Slowly Changing Dimensions
Gamblitude's dimensional models apply explicit SCD choices appropriate to each attribute. Customer attributes affecting historical analysis (VIP tier, segment, KYC level) use Type 2 with full history preservation. Stable identifiers (customer ID, brand) use Type 1. Game attributes with regulatory significance (RTP variant) use Type 2. The semantic and metric layers respect these choices, ensuring queries return correct historical or current values depending on context. Operators benefit from consistent SCD handling without designing it themselves.
FAQ
Typically Type 2. Customer VIP tier affects historical analytical questions: what tier was the customer in when they made this deposit, when they triggered this RG marker, when they generated this NGR. Type 1 (overwrite) loses this history; Type 2 (versioned rows) preserves it. Most mature operators use Type 2 for VIP tiers and similar evolving customer segmentation.
Yes, but with significant effort. Changing from Type 1 to Type 2 requires source-data history that may not exist (you can't reconstruct history not previously captured). Changing from Type 2 to Type 1 loses history. Both directions require careful migration and rebuilding of downstream analytics. Decisions should be made carefully upfront.
Significantly. ML training data needs point-in-time correctness: a model predicting churn at time T should be trained on features as they existed at time T, not current state. Type 1 dimensions break this; Type 2 dimensions support it. ML teams generally prefer Type 2 for feature engineering source data.
No. Some attributes don't need history (stable identifiers, display labels, descriptions). Applying Type 2 universally produces unnecessary complexity and storage cost without proportional benefit. The choice should be made per attribute based on whether historical accuracy matters.
Type 2 dimensions incorporate history into the main analytical query path; queries join facts to the appropriate historical version automatically. Change logs (Type 4) store history separately, requiring explicit lookup. Both preserve history but with different query patterns. Type 2 is typically more analyst-friendly; Type 4 is sometimes preferred for very large dimensions or specific audit use cases.
Further reading
Found a mistake or want a term added to the iGaming Glossary? Let us know.
Browse the complete glossary or see how governed definitions work across dashboards, reports, alerts and AI answers.