Daily vs Monthly Partitions in DW fact tables

• DominantDBA

  SSC Eights!

  Points: 862

  More actions

  November 11, 2011 at 6:33 am

  #259846

  Hi

  Im trying to find out recommendations for deciding whether to partition by day or month in our warehouse

  My boss has decided that if the incremental load/day is greater than 5 million rows =>Daily

  Anything under 2 million => Monthly

  Thinking about the range based queries than are more likely to be based on Month more often is it therefore not better to have data in a monthly partition even if the partitions are bigger?

  Maybe I shouldnt worry? Just pick a starting point and once data is loaded we can experiment based on the query load?

  Any advice would be greatly appreciated

  thanks

  Chris

• Koen Verbeeck

  SSC Guru

  Points: 259105

  More actions

  November 14, 2011 at 12:13 am

  #1406928

  It depends 🙂

  Daily partitions will allow you with faster processing speed, as you can only process the current (or past) day.

  But as you said, if most of the queries are on the month level, partitioning on the month level makes sense to me.

  Need an answer? No, you need a question
  My blog at https://sqlkover.com.
  MCSE Business Intelligence - Microsoft Data Platform MVP