A short guide to a data warehouse
The advice here is unglamorous, which is probably why it gets skipped. Everything we would tell a client about a data warehouse in the time it takes to drink a coffee.
Numbers get quoted in meetings long after anyone remembers how they were calculated. Budget a little time for it every quarter and it never becomes a project of its own.
Why this earns attention
Reporting queries and application queries want different shapes. It is worth being explicit about, because assumptions differ quietly. Anything you cannot measure here, you are deciding by taste, which is fine as long as everyone knows it.
What good looks like
Separating them stops reports taking the product down. The teams that handle this well are rarely the ones with the biggest budgets. Assume whoever inherits this will have half your context and none of your patience.
Where it usually goes wrong
Start with the questions, not with the schema. Getting it slightly wrong is survivable. Ignoring it entirely is not. The practical test is whether someone new to the project could tell, in a minute, that it had been handled.
The short version
Most data problems are ownership problems that turned into technical ones. Three things worth confirming about a data warehouse before you move on:
- Someone can say what the current setup is without going to look
- Start with the questions, not with the schema — and you know whether that is true here
- There is a way to tell whether the last change to this helped
If any of that sounds like a description of your current setup, it is fixable.