Dimension Snowflaking

Hi

I have a Member Dimension which has a number of date fields (Join Date etc), is it best to use a date key (snowflake to reference a date dimension) or keep the column a date type

Regards

Tim

Just keep as a date column. You can always join to the date dim on the date if you need to leverage the date dimension.

One thing to consider is that datetime fields are twice the size as integers. Date dimensions tend to be small so joins to the the date dimension don't usually take a long time and joins on integers will be faster than joins on datetimes. Member Dimensions can be big so if you have a lot dates in the dimension, making them integers can have a dramatic effect on total size.

I would not snowflake any dimension by another dimension unless it's necessary. Very likely a date attribute in a dimension may work perfectly without the need for other calendar attributes in date dimension. Storing the straight attribute value instead of a FK in a dimension is a general guideline for dimensional modelling, and the date attribute should be no exception.

Enven if the Member dimension is a monster dimension (>1 million), I don't think storing an integer date key has a significant performance advantage, or storage saving for that matter, than the straight date value. Since the date dimension is small, joining by natural date should be just as fast as joining by integer date key as B&L suggested. For monster dimension, the focus should be on those highly repeated and lengthy textual attributes, as having those attributes as outriggers can indeed reduce the size of monster dimension dramatically.