How can you handle slowly changing dimensions with frequent updates?

1 What are slowly changing dimensions?

Slowly changing dimensions (SCDs) are dimensions that have attributes that change over time, but not frequently or rapidly. For example, a customer may change their name, address, or phone number once in a while, but not every day. A product may change its price, description, or category occasionally, but not every hour. SCDs are important for data warehousing because they provide historical and current information about the data and enable accurate analysis and reporting.

2 Why are frequent updates a problem?

Frequent updates are a problem for SCDs because they can affect the performance, storage, and complexity of the data warehouse. If every change in an attribute is recorded in the dimension table, the table will grow very large and require more space and resources. If only the latest value of an attribute is stored in the dimension table, the historical information will be lost and the analysis and reporting will be inaccurate. Therefore, you need to choose a suitable technique to handle SCDs with frequent updates depending on your business requirements and data characteristics.

3 What are the common techniques for SCDs?

There are several techniques for handling SCDs with frequent updates, each with its own advantages and disadvantages. The most popular options include overwriting the old value with the new value, creating a new row for the new value and marking the old row as inactive, and adding a new column for the new value while keeping the old value in the original column. Overwriting is the simplest and fastest technique, but it does not preserve any history and may cause inconsistency and errors in the data warehouse. Creating a new row preserves full history of changes, but increases size and complexity of the dimension table. Adding a new column limits number of changes tracked and may cause confusion and redundancy in the dimension table. Ultimately, reporting and documentation of these techniques will help inform stakeholders and improve cloud security posture.

4 How to choose the best technique for your scenario?

When deciding which technique to use for handling SCDs with frequent updates, you should consider the frequency and number of changes in the attributes, the importance of historical information for analysis and reporting, the storage space and performance impact, and the complexity of your data model. For instance, if you have a customer dimension that changes very often and you need to track all of those changes, then type 2 may be the best option. On the other hand, if you have a product dimension that changes infrequently and you only need to track the latest value for your analysis and reporting, type 1 could be more suitable.

5 How to implement the technique using SQL?

The implementation of the technique for handling SCDs with frequent updates using SQL depends on the type of technique and data source. You need to write SQL statements to extract the data from the source system and load it into a staging area, compare the data in the staging area with the data in the dimension table to identify changes, apply the appropriate technique to update the dimension table, and clean up the staging area and log the results. For example, if you use the type 2 technique, you may write SQL statements such as inserting into a customer staging table, selecting from customer staging and customer dimension tables, updating customer dimension table, inserting into customer dimension table, and deleting from customer staging table. Additionally, you may log the results with an etl_log.

6 Here’s what else to consider

This is a space to share examples, stories, or insights that don’t fit into any of the previous sections. What else would you like to add?