The Example

We will apply SCD’s to maintain the history of Product dimension, specifically the history of changes of Product’s Product Group. The PRODUCT_SK column is the surrogate key of the Product dimension table.

SCD Type 1

We will apply SCD Type 1 to the PENCIL product in the Product dimension table.

Let’s say PENCIL changes its product group into 4. Effecting this change by applying SCD Type 1 just updates the existing row of PENCIL on its product group. We do not have record of its previous product group; in other words, we do not maintain its product group history.

The updated PENCIL’s product group is shown highlighted in blue.

SCD Type 2

SCD Type 2 is essentially the opposite of Type 1. When we apply SCD Type 2, we never update or delete any existing product group.

To apply SCD Type 2 we need an effective date and an expiry date. Effective date 31-Dec-99 means the row is not expired. It is the most current version of the product.

Assuming the product group change of PENCIL is effective 1 April 2010, we update the expiry date of its existing row to 31 March 2010, one day before the effective date of the effective date of the change, and insert a new row that represents its new, current version.

SCD Type 3

With SCD Type 3 we maintain history but in one record only. We have one column for each version of the product group. You need to have as many columns as the number of versions you want to keep.

One of the most common SCD Type 3 applications is to maintain two versions of product group: the original version and the current version. When there is no product group change yet, the current product group is the same as the original product group.

When the pencil’s product group changes, let’s say on 1 April 2010, we expire its original product group by changing the expiry date to a day earlier (30 March 2010), and replace its current product group to the new product group.

When its product group changes again in the future, we will replace just the current product group with the new product group. The expiry date does not change. It gets updated once only the first time its product group changes.

SCD Type 6

SCD Type 6 combines the three basic types. Type 6 is particularly applicable if you want to maintain complete history and would also like have an easy way to effect on current version. Let’s apply Type 6 instead of Type 3 only.

We have applied Type 3 by having two versions of product group. When the pencil’s product group changes we update its existing current product group (that is Type 1 update). We also apply Type 2 by adding a new row.

On the next pencil’s product group change (1 July 2010), we will again apply all three SCD types.

QUERY

Let’s next see how our Type 6 in the Product dimension works on a sales fact. (In a real sales fact data you will have some other dimensions, meaning the fact table will have more surrogate key columns than just the product surrogate key)

If our interest is in the current version, our SQL query will use the current product group column. An example SQL query will look like:

SELECT current_product_group_name, SUM(sales_amt)
FROM sales_fact s, product_dim p
WHERE
s.product_sk = p.product_sk
AND product_name = ‘PENCIL’
GROUP BY current_product_group_code

The output of the query will be:

The reason of applying SCD Type 2 is to have a complete history that tracks changes. SQL queries that take into account dimension history use the product group column:

SELECT product_group_name, SUM(sales_amt)
FROM sales_fact s, product_dim p, date_dim d
WHERE
s.product_sk = p.product_sk
AND product_name = ‘PENCIL’
GROUP BY product_group_code

The output of the query will be:

SUMMARY

This article discusses what SCD Type 6 is, when to apply it, and how it works. The name Type 6 comes from the ‘sum’ of the three basic SCD types (6 = 1 + 2 + 3).