While fact tables are changing constantly recording entries every minute, dimensions are seen as somewhat static. However, dimensions are not fully static, people change addresses, phone numbers are updated, and product descriptions are modified and so on. So it is important to be able to record these changes when they happen. The ways we handle a slowly changing dimensions (SCD) are broken down into multiple “types”. It is important to note that SCD types are specific to the attributes themselves, not the entire Table. For example, address could be a type 2 SCD while the Birth Date would be Type 1. We will review Type 0, Type 1, and Type 2. There are a number of other SCD types, most of which are combinations of these three types. However, they are outside the scope of this training and will not be addressed here.
In these types of attributes never change. Examples of attributes in this type of dimension are Birth Date and Original Credit Score etc.
A type 1 SCD does support updating of values, however, it overwrites the values and does not track the change. In other words, once a change is made, the old value is lost forever.
A type 2 SCD tracks historical value changes in dimensions through a number of ways. First, a number of additional fields are added to the table, most notably "SCD From DateTime", “SCD To DateTime”, and "SCD Is Current". Second, changes are tracked by creating an additional row in the table with the unchanged values remaining the same, and the changed values with the updated data. The old row’s “SCD To DateTime” is updated to the date prior to the change and the "SCD Is Current" changed to 0. While the new rows “SCD From DateTime” is update with the date the change was made and the "SCD Is Current Field" us updated to 1. For Example, The Customer table has the state attribute tracked as a Type 2 SCD. Customer John Doe moves from Oregon to Idaho . A new instance of John Doe is created in the table so the table now has two John Doe’s with identical information SS number and Customer ID etc. The first instance would have Oregon as the value in the State field, the "SCD To DateTime" would be updated to Yesterday, and the "SCD Is Current" field is updated to 0. The second instance would have Idaho under the State attribute, update "SCD From DateTime" to Today’s date, and update "SCD Is Current" to 1. This way the business users can track table attribute changes over time.