Data Engineering Technical Blog

In Type 3 Slowly Changing Dimension, there will be two columns to indicate the particular attribute of interest, one indicating the original value, and one indicating the current value. There will also be a column that indicates when the current value becomes active. In our example, recall we originally have the following table: Customer Key Name State 1001 Guru Jharkhand To accommodate Type 3 Slowly Changing Dimension, we will now have the following columns: ·         Customer Key ·         Name ·         Original State ·         Current State ·         Effective Date After Guru moved from Jharkhand to Karnataka, the original information gets updated, and we have the following table (assuming the effective date of change is Janu...

In Type 2 Slowly Changing Dimension, a new record is added to the table to represent the new information. Therefore, both the original and the new record will be present. The new record gets its own primary key. In our example, recall we originally have the following table: Customer Key Name State 1001 Guru Jharkhand After Guru moved from Illinois to California, we add the new information as a new row into the table: Customer Key Name State Active 1001 Guru Jharkhand 0 1005 Guru Karnataka 1 Advantages : - This allows us to accurately keep all historical information. Disadvantages : - The table size may increase due to historical data. When to use Type 2 : Type 2 slowly changing dimension should be used when it is necessary for the data warehouse to track historical changes.

In Type 1 Slowly Changing Dimension, the new information simply overwrites the original information. In other words, no history is kept. In our example, recall we originally have the following table: Customer Key Name State 1001 Guru Jharkhand After Guru moved from Jharkhand to Karnataka, the new information replaces the new record, and we have the following table: Customer Key Name State 1001 Guru Karnataka Advantages : - This is the easiest way to handle the Slowly Changing Dimension problem, since there is no need to keep track of the old information. Disadvantages : - All history is lost. By applying this methodology, it is not possible to trace back in history. For example, in this case, the company would not be able to know that Christina lived in Illinois before.  When to use Type 1 : Type 1 slowly changing dimension should be used when it is not necessary for the data warehouse to keep track of historical changes. ...

What is a Slowly Changing Dimension? A Slowly Changing Dimension (SCD) is a dimension that stores and manages both current and historical data over time in a data warehouse. It is considered and implemented as one of the most critical ETL tasks in tracking the history of dimension records. There are three types of SCDs and you can use Warehouse Builder to define, deploy, and load all three types of SCDs. What are the three types of SCDs? The three types of SCDs are: Type 1 SCDs - Overwriting In a Type 1 SCD the new data overwrites the existing data. Thus the existing data is lost as it is not stored anywhere else. This is the default type of dimension you create. You do not need to specify any additional information to create a Type 1 SCD. Type 2 SCDs - Creating another dimension record A Type 2 SCD retains the full history of values. When the value of a chosen attribute changes, the current record is closed. A new record is creat...