EdVassie (4/2/2013)
If you are designing a multi-TB database and do not understand all the design issues you need to pay to outside help. Period.When processing a database of this size, a 2% difference in performance can result in extra hours of processing. Unless you really know how to fine tune your design to get the last 1% or 2% out of it, you almost certainly will end up with something that is either very slow or is using a few $100k more kit than it needs.
IMHO you should build a business case for your employer to engage an external consultant to help you with these issues. There are a number of high-reputation organisations in the market that can do this work, but if you are really stuck for names ask your Microsoft Account Manager for advice.
The loads will be, by his word, very infrequent. It sounds like he has the luxury of extra hours of processing. I'm a huge proponent of outside consulting, but for all we know the OP has the skills to successfully implement a coherent performance strategy. Why spend money on consultants when the benefits won't be noticed due to the infrequency of loads and flat file extracts? I don't see the cost outweighing the benefits in this particular case.