Slowly Changing Dimensions - Correction

About two years ago, I wrote this post on slowly changing dimensions.  Several months later I learned in a Kimball class that my understanding of a Type 3 Slowly Changing Dimension (SCD) was incorrect.  Allow me to use this post to right the ship.

My original explanation (incorrect):

A type 3 SCD is essentially a hybrid of a type 1 and type 2 SCD.  In this scenario as history changes, rows are added into the dimension table, consistent with a type 2 SCD.  Each row contains a column with the value that pertains to that moment in time, as does a type 2 SCD.  Each row also contains a column with the current value.  So, continuing with the example above (from the other post), while you will be able to see that the older fact rows pertain to a time in which the employee was a Junior Analyst, you will also be able to see (while looking at this same historical dataset) that the employee is now a Senior Analyst.

My updated explanation (correct):

A type 3 SCD is essentially a hybrid of a type 1 and type 2 SCD.  In this scenario as history changes, rows are added into the dimension table, consistent with a type 2 SCD.  Each row contains a column with the value that pertains to that moment in time, as does a type 2 SCD.  Each row also contains a column with the prior value.  So, continuing with the example above, while you will be able to see that the newer fact rows describe this employee as a Senior Analyst, you will be able to see that his/her prior position was Junior Analyst.

So, a type 3 SCD will look something like this:

key_employee	employee_first_name	employee_last_name	position	prior_position
1	John	Smith	Junior Analyst	(null)
2	John	Smith	Senior Analyst	Junior Analyst

My original explanation above describes a type 6 slowly changing dimension, which will look something like this:

key_employee	employee_first_name	employee_last_name	position	current_position
1	John	Smith	Junior Analyst	Senior Analyst
2	John	Smith	Senior Analyst	Senior Analyst

These links to Ralph Kimball's website provide some great information on slowly changing dimensions:

1.) Type 1
2.) Types 2 and 3
3.) Types 0, 4, 5, 6, and 7

Image courtesy of adamr / FreeDigitalPhotos.net