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 a claimant dimension. If the initial value at insertion was Single, the attribute value is updated to Married when the person gets married.
- Type 2: This keeps history (versioning). A bunch of system columns...
