Designing patterns to load dimensions of a data warehouse
The difference between these patterns is the way historical data is stored in the dimensions. We call them Slowly Changing Dimensions (SCD). The following points give an overview of various SCD types:
- Type 0: This retains the original. This means that any changes to a specific member of the dimension will result in a new member inserted with new values. As opposed to SCD type 2, there's no concept of the current version or start and end date of a row. This SCD type is rarely used.
- Type 1: This overwrites changes, no history is kept. For example, let's say we have a person's
marital status
attribute in aclaimant
dimension. If the initial value at insertion wasSingle
, the attribute value is updated toMarried
when the person gets married. - Type 2: This keeps history (versioning). A bunch of system columns are added to the dimension:
- The start and end date of the dimension member (row). Usually, the start date equals the date when the...