A practical checklist for a data warehouse

The version of this that works is simpler than the version most people imagine. Run through this the next time a data warehouse comes up.

Data outlives the applications built on top of it, which is why the model deserves more thought than the screens. The failure mode is not doing it wrong, it is doing it once and assuming it stays done.

The checklist

• Reporting queries and application queries want different shapes
• Separating them stops reports taking the product down
• Start with the questions, not with the schema
• Someone is named as the owner
• There is a date to review it again

The reason this keeps coming up

Reporting queries and application queries want different shapes. Getting it slightly wrong is survivable. Ignoring it entirely is not. The failure mode is not doing it wrong, it is doing it once and assuming it stays done.

The short version

Numbers get quoted in meetings long after anyone remembers how they were calculated. Three things worth confirming about a data warehouse before you move on:

• Someone can say what the current setup is without going to look
• Separating them stops reports taking the product down — and you know whether that is true here
• There is a way to tell whether the last change to this helped

If you are not sure where your systems currently stand on this, it takes us about an hour to find out.