Slowly Changing Dimensions
1. Slowly Changing Dimensions
In this video, we’ll talk about slowly changing dimensions, a key design consideration for data warehouses.2. How often does a dimension change?
Some dimension attributes do not change over time. For example, the birth date or the birth city of a person is not expected to ever change; they are immutable facts.3. How often does a dimension change?
In contrast, some dimension attributes might change frequently. These are called rapidly changing dimensions. For example, the credit score of a bank customer might change every month.4. How often does a dimension change?
Most dimension attributes are generally stable, but might change sporadically over time. For example, a person's phone number or email address. These are called Slowly Changing Dimensions, or SCD for short.5. Slowly Changing Dimensions
There are several types of slowly changing dimensions, based on the different approaches to managing data change. These approaches include ignoring changes, overwriting old records, or keeping a history of all changes. These types are labelled from 0 to 7.6. SCD Type 0: No change
SCD Type 0 refers to dimensions that never change. If a dimension attribute is expected to never change, any data changes are discarded. For example, a table that stores personal data might reject any subsequent changes to date of birth once the initial data has been recorded.7. SCD Type 1: Overwrite
On SCD Type 1, the existing record is overwritten with new data. Data always reflect the latest values, and history of older values is not kept. Type 1 is used for supplementary values that do not require a record of history. For example, a person's phone number; if the person changes phone number, only the new number is relevant and we do not need to keep the old phone in the database.8. SCD Type 2: Keep all history
On SCD Type 2, the existing record is kept to preserve history, and a new record is created with the same natural key preserved. SCD Type 2 is used when a record of history is required. For example, if a salesperson is assigned to another region, overwriting the record would distort the sales figures of the original region. To track changes over time, SCD Type 2 dimensions have extra columns to indicate if the record is current and the dates range of validity for the row.9. SCD Type 3: Keep some history
On SCD Type 3, limited changes are recorded, typically only keeping the most recent changes to certain columns. No extra rows are required; instead, more columns are added to the table to keep track of previous and current versions of specific attributes.10. Other SCD types
There are other SCD types from 4 to 7. They are mostly variations on the core types 1 to 3, and are not very popular due to increased complexity. Whenever possible, use one of the two most common types. If keeping history is not required, use SCD Type 1. If keeping history is required, use SCD Type 2.11. Let's practice!
Now, let's do a couple of exercises to put this in practice.Create Your Free Account
or
By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.