Question

What is the primary difference between a Type 2 and a Type 6 Slowly Changing Dimension (SCD) in terms of how historical data is preserved in a dimension table?

Accepted Answer

A Type 2 Slowly Changing Dimension preserves history by creating a new row in the dimension table every time an attribute of an entity changes, assigning the new row a unique primary key while keeping the original historical record intact with an expired end date. This allows you to report on data as it existed at any point in time by filtering for the record that was active during that specific period. A Type 6 Slowly Changing Dimension, often called a hybrid approach, combines Type 1, Type 2, and Type 3 methods to capture history while also maintaining the current value across all historical records for an entity. In a Type 6 approach, when an attribute changes, a new row is created just like in Type 2, but the system also updates all previous versions of that entity&#x27;s records to reflect the most current attribute value. This allows users to write simple queries to see the current attribute value alongside historical facts without needing complex logic to determine which row was active at a specific time. While Type 2 isolates each version of the entity as a distinct snapshot, Type 6 creates a bridge that links historical data to the most recent version of an attribute.

Home → All Courses → Engineering and Technology Courses → Data Engineering → Flashcard

What is the primary difference between a Type 2 and a Type 6 Slowly Changing Dimension (SCD) in terms of how historical data is preserved in a dimension table?