Assuming my dimDate has a surrogate key. Should all the date columns in the dimensional tables (not the fact tables) store the surrogate key of date dimension? Or just plain date?

For example, in dimensional table dimCustomer, there may be birthday, join date, graduation date, .... Etc.

  • Check community.idera.com/blog/b/community_blog/posts/… it will give you some pointers about why it might be helpful to actually have the date dimension. Whether you use a surrogate key or no won't really be relevant, except (maybe) if uses less storage space than an actual date.
    – joanolo
    Jun 5, 2017 at 20:25
  • 1
    I meant the date columns in the dimensional tables instead of the fact tables. At least it will not be a pure star schema if using the dimensional tables are needed to join the date dimensional table to get the dates?
    – u23432534
    Jun 5, 2017 at 20:29
  • I have seen examples of where the key of the date dimension table is the integer representation of the date itself. May be faster to convert the fact table's reference ID to a date format as needed unless you are requiring more attributes from the date dimension table
    – Jeff A
    Jun 5, 2017 at 23:30
  • Why not both? Are these actual business dates in the dimension table or are the SCD / metadata dates? If they are business dates, then consider using DimDate, but also consider that in a star schema, dimensions usually don't point at other dimensions
    – Nick.Mc
    Jun 5, 2017 at 23:40

3 Answers 3


I would model this by storing both the date value and the SK back to the date dimension. Here is why storing that actual state value allows you to have ms level precision and can be useful when someone wants to see exactly when something happened. however for aggregates the SK can be a computed column if you are worried about the two values going out of sync

ID               1
Name             John Smith
BirthdateValue   2018-07-04 04:20:69.007
BirthdateSK      20180704

Repeat the pattern as needed for other dates.

  • 1
    Times don't go in date dimensions, just dates Dec 8, 2018 at 23:02
  • 1
    This is not a date dim it's a customer dim the point I'm trying to illustrate is that I can store the full datetime but also add the surogate key to a conformed date dimension .. this is commonly know as the outrigger pattern in the kimball methodology Dec 8, 2018 at 23:09

You should use a surrogate INT key, but make it a smart key. Smart keys are a bad idea, except in this case

What I mean is:


date_key    the_date
20181208    2018-12-08 ...

This lets you use fast int keys, that a user can also recognize as a date. And also allows for the "Not Applicable" date row that you need in a date dimension

  • Can I use DATE datatype instead of INT? Apr 23, 2020 at 14:54
  • You can but ints are faster Apr 23, 2020 at 15:09

You can - but there are a few things that you may want to bear in mind.

Firstly, these attributes are actually facts. For the most part, anything relating to a date is a fact as it is time-sensitive. Therefore, wherever possible, I like to push these down into a fact table. This could be as simple as Fact.Customer or Fact.AccountHistory for example, but it really depends on how you will be analysing the data. Sometimes you may even want to push them down into your main fact table such as Fact.Sales or something similar.

Secondly, it makes maintenance of your data warehouse troublesome. All of those foreign keys cause additional overhead during your ETL process, even if you disable them when doing the load, they have to be rechecked once they are enabled again.

Finally, if you do wish to link the dimensional attributes to a date dimension, then I find it is better to create separate date dimension tables for each one of your attributes. I know initially this sounds counter-intuitive but when you look at the range of queries that it opens up it makes a lot of sense.

If you link all date attributes to the same dimension table, then it makes it difficult when you need to apply multiple independent filters. What if I only want to see customers who were born in December but I also want to aggregate the sales by the month they were purchased? Using a single date dimension means that this query is a lot harder than it needs to be. If I have separate date dimensions it is pretty easy to do.

This technique is called role-playing dimensions. Here is the description from the Kimble website:

A single physical dimension can be referenced multiple times in a fact table, with each reference linking to a logically distinct role for the dimension. For instance, a fact table can have several dates, each of which is represented by a foreign key to the date dimension. It is essential that each foreign key refers to a separate view of the date dimension so that the references are independent. These separate dimension views (with unique attribute column names) are called roles.

If you have a lot of date dimensions it can cause your model/warehouse to bloat so a technique that is used commonly is to have multiple views of the date dimension exposed out for each of the attributes that reference it. If I'm not adding too many references to the date dimension, I have no problem having a second physical copy of the table such as Dimension.GraduationDate which contains only the properties that I know I am going to aggregate/filter by.

By cherry picking what attributes you are interested in you can reduce the overall storage requirements of your warehouse - and most modern database vendors also provide some type of compression that can be applied as well, so this can drastically reduce the overhead of creating multiple date dimensions should you need to.

Ultimately, when building your warehouse, you should know in advance the questions that you are providing answers to. These questions, and the answers required will drive your overall design. Try out multiple approaches and decide which will fit your needs, you may even decide to implement a hybrid approach whereby some dimensions reference the main date table and others reference role-playing dimensions.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.