Introduction
Slowly Changing Dimensions, or SCD is a data management concept that determines how tables handle data which change over time.
For example, whether you want to overwrite values in the table or maybe retain their history. This can be determined by the SCD type to implement.
There are three types of SCD which are relatively easy to implement in the Lakehouse.
Type 0, Type 1 and Type 2.
Type 0 SCD
In a type 0 SCD table, no change is allowed. Tables of this type are either static or append-only tables. For example, static lookup tables.
Type 1 SCD
In a type 1 SCD table. The new data overwrite the existing one. Thus, the existing data is lost as it is not stored anywhere else.
This type is useful only when you care about the current values rather than historic comparisons.
Type 2 SCD
While in a type 2 SCD table, a new record is added with the changed data values.
This new record becomes the current active record, while the old record is marked as no longer active. So, type 2 SCD retains the full history of values.
For example, a products table that track prices changes over time.
Deciding on which of a slowly changing dimension pattern to implement will vary based on your business requirements.
In a Type 1 SCD table, no history retained. When new data arrives, the old attributes values in the table’s rows are overwritten with the new values.
While in SCD Type 2, table full changes history is retained.
In order to support SCD Type 2 changes, we need to add three columns to our table.
- a Current column, which is a flag that indicates whether the record is the current version or not.
- Effective or Start Date: the date from which the record version is active.
- And lastly, the End Date: the date to which the record version was active.
When new data arrives, a new record is added to the table with the updated attributes values.
Here.
For example, the new record with the Product ID No. 1 becomes the current valid record, while the old record gets marked as no longer valid. And we set its End Date to the Start Date of the new record.
These effective and end dates help in calculating which record was valid during a particular time frame.
SCD Type 2 Vs. Delta Time Travel
Now, you may think, why not using Delta Time Travel feature to access the historical versions of the data that changes?
In fact, Delta time travel does not scale well in cost and latency to provide a long-term versioning solution
And remember, running a vacuum command will cause the table historical versions to be deleted.