SCD2 vs Bitemporal Modeling
When should you use SCD2 and when should you use bitemporal modeling?
Use SCD2 when you only need business-valid history.
Use bitemporal modeling when you must preserve both business-valid history and the history of when information became visible to the platform.
SCD2 answers what was valid. Bitemporal modeling also answers what was visible.
Slowly Changing Dimension Type 2 is one of the most common ways to model changing attributes over time. It creates a new row when a relevant attribute changes and uses validity intervals to resolve the correct version for a reporting date.
Bitemporal modeling goes one step further. It separates the time when something was valid in the business from the time when the data platform could see it. This becomes important when history can be corrected, backdated or arrive late.
In March, the platform shows Standard. In May, a correction says March should have been Premium.
The business-valid date stays March. What changes is the knowledge date: do you query what is corrected today, or what was known back then?
In March, the platform only knew the customer segment as Standard. The correction to Premium had not arrived yet.
SCD2 can tell you the corrected valid-time truth. Bitemporal modeling can additionally reproduce what the platform knew at the time the report was created.
The practical difference between SCD2 and bitemporal modeling.
What was true in the business timeline?
What was true, and what was visible at the time?
Usually valid_from and valid_to
Valid time plus visible/system/transaction time
Often overwrite or create a corrected valid-time version
Preserve when the correction became visible
Historical attributes and point-in-time reporting
Auditability, reproducibility and corrected history
Use SCD2 when you only need business-valid history.
SCD2 is often sufficient when past reports are allowed to use the latest corrected version of history, or when the source system does not provide meaningful information about when a correction became visible.
Use bitemporal modeling when corrected or late-arriving history matters.
Bitemporal modeling is useful when users need to reproduce a historical report as it was known at the time, not just as it would be calculated using today's corrected history.
A model can be valid as SCD2 and still fail reproducibility.
A corrected customer segment may be valid for March, but the correction might only have arrived in May. A March report rebuilt in June may therefore show a different result than the report originally published in March.
This is not necessarily an SCD2 bug. It is a missing visible time decision. Bitemporal modeling makes that decision explicit.
Validate both valid-time behavior and correction behavior.
SCD2 and bitemporal modeling connect to several historical modeling patterns.
Explore the Advisor for your own model.
Use the advisor when you are unsure whether your historical model only needs valid-time history or must also preserve what was known at reporting time.
Explore the Advisor →