State Modeling
Represents the state of a business entity over a valid time interval.
CustomerContractPolicyProduct
← Back to Workbench
PATTERN CATALOG
Historical Modeling Pattern Catalog
A practical vocabulary for common problems in SCD2 dimensions, snapshots, temporal joins, late-arriving data and historical reporting.
Foundations
Basic building blocks of historical data models.
Event Modeling
Represents discrete business events that happened at a point in time.
Order createdClaim filedPayment received
Bitemporal Modeling
Separates business-valid time from system-visible time.
Corrected historyAudit reportingAs-known reporting
Alignment Patterns
Patterns for connecting histories across sources.
State ↔ State Alignment
Joins two historized state sources across overlapping time intervals.
Contract ↔ CustomerProduct ↔ Price
State ↔ Event Alignment
Maps events to the state that was valid when the event happened.
Claim ↔ PolicyMutation ↔ Contract
Temporal Conformance
Aligns competing timelines from multiple source systems.
CRM + ERPPolicy system + billing system
Dimension Patterns
Patterns for historized dimensions and relationships.
Dimension Completion
Ensures dimension history covers all required fact periods.
Late arriving dimensionsMissing foreign keys
Relationship History
Models relationships that change over time.
Customer ↔ AdvisorPolicy ↔ Broker
Identity Resolution
Resolves multiple identifiers for the same business entity.
Customer mergeContract migration
Reporting Patterns
Patterns for reproducible historical reporting.
Snapshot Reproducibility
Ensures reports can be reproduced for the same reporting date.
Month-end snapshotsAudit reports
Historical Backfill
Reconstructs history after data already exists.
CDC replayHistorical reload
Historical Correction
Preserves corrected history without losing what was known before.
Backdated changesRestatements
Data Quality Patterns
Patterns for detecting historical modeling risks.
Historical Match Ambiguity
Occurs when multiple historical records satisfy the same join.
Duplicate fact rowsJoin explosion
Historical Coverage Gap
Occurs when required history is missing for a reporting period.
Missing dimension rowUncovered fact interval
Historical Overlap
Occurs when multiple records are active at the same time.
Overlapping SCD2 rowsAmbiguous current state