Question about Dimensions with SKeys to join with other dimensions.

Hello All:

We are faced with a design decision and I want to make sure that we are following best practices. In our datamart we have some dimensions which have skeys from other dimensions for which history is tracked using type 2 SCD processing. Is it a good practice to join dimensions (snowflaking) using surrogate keys or should these be used to join only to fact tables?

We are finding that everytime there is a change to one dimension attribute, it is creating new records duplicating all of the data from the old surrogate key value into ones containing the new skey value.

Should the ETL create new dimension surrogate keys on just the changed dimension rows from current dimension?


Thanks in Advance!

You are doing it correctly. And now you know why snowflaking is a bad idea. So no, it is not a good practice to snowflake dimensions, especially type 2 SCD's.

Thank You for your response BoxesAndLines.


Should I join dimensions (snowflaking) using surrogate keys or should these be used to join only to fact tables? Is it a better idea to put the natural key in the dimension when snowflaking?

In the Dimensional Modeling Technique relating to snowflakes, it mentions that an attribute key be used to perform the join between dimensions is this be the surrogate key or the natural (business id) key?

KimballGroup wrote:When a hierarchical relationship in a dimension table is normalized, low-cardinality attributes appear as secondary tables connected to the base dimension table by an attribute key. When this process is repeated with all the dimension table’s hierarchies, a characteristic multilevel structure is created that is called a snowflake. Although the snowflake represents hierarchical data accurately, you should avoid snowflakes because it is difficult for business users to understand and navigate snowflakes. They can also negatively impact query performance. A flattened denormalized dimension table contains exactly the same information as a snowflaked dimension.