The first on a occasional series of questions I might ask in an interview – or perhaps would like to be asked if someone were to interview me…

How do you design a data warehouse?

Now that is a vague question – am I looking for an insight to the analysis and design process (requirements gathering and other user facing skills) or a statement of the design principles to be followed? In reality tackling either facet would score good marks from me providing the case is well argued. I am also of the opinion that more than one valid solution is possible – I really don’t mind if the candidate offers an answer that is not my preferred way of doing things providing it well reasoned.

But that said, a candidate that correctly associates the purpose of a data warehouse with the design is going to be scoring highly. Data warehouses are about data, without that they are nothing. The fundamentals are:

• protecting the asset – the data must be recoverable when something goes wrong (when and not if)
• guaranteeing the quality of the data – or else we will not be trusted
• acquiring the data and assimilating it in a timely fashion – batch versus trickle-feed
• and being able to play it back to users in an acceptable timeframe.

You will notice that none of this gets into design wars between 3NF and star.

I will go into some specifics next time