Designing Data Models People Can Actually Query

Performance problems that are not performance problems

When a report takes ninety seconds, the first instinct is to add hardware or an index. Sometimes that helps. More often the query is slow because it is doing work the model should have done in advance: joining eight tables to answer a question the business asks every day.

A data model is a set of decisions about which questions will be cheap and which will be expensive. Get that alignment right and most performance tuning becomes unnecessary.

Model for the question, not the source

Operational databases are designed to record transactions safely, which means normalising aggressively to avoid duplication. That is correct for writing and hostile to reading.

An analytical model has the opposite priority. It accepts controlled duplication so that the common question can be answered without reconstructing context from a dozen tables. The shape follows how the business thinks: a measurable event at the centre, and the descriptive attributes people filter and group by around it.

Grain: the decision everything else depends on

Grain is what one row represents. It sounds trivial and it determines whether the model is usable.

Mixing grain is the most common serious modelling error we see. A table where some rows are orders and some are line items will produce results that are correct for one question and quietly wrong for another. Nothing errors: the totals are simply inflated in ways nobody notices until a reconciliation.

Decide the grain explicitly, document it, and enforce it with a uniqueness test.

Naming as an interface

A model is an interface that non-engineers will use. Names are the documentation most people read.

• Say what it is. order_total_gbp beats amt2.
• Be consistent. If one table uses customer_id, none should use cust_no.
• Encode the unit. Currency, timezone and grain in the name prevent an entire class of mistake.
• Avoid ambiguity. date is not a column name in a table with four dates.

Where indexes and caching actually belong

Once the model matches the questions, targeted optimisation becomes effective rather than speculative. Index the columns that filters and joins actually use, which you can observe, rather than guess. Pre-aggregate the summaries that are read constantly and recomputed identically each time. Partition large tables on the dimension that queries filter by, usually date.

Applied in that order (model, then measure, then optimise), these deliver large gains. Applied first, they are a way of making a badly shaped model slightly less slow.

Key takeaways

• Slow queries are usually a modelling problem, not a hardware one.
• Model for the questions asked, not the shape of the source system.
• Decide grain explicitly and enforce it with a test.
• Names are the interface most consumers will read.
• Optimise after the model fits, and only where measurement points.

Frequently asked questions

What is grain in a data model?

Grain is what a single row represents: one order, one line item, one day per customer. Mixing grain in the same table produces totals that are correct for some questions and silently wrong for others.

Should analytical tables be normalised?

Generally less than operational ones. Normalisation protects write integrity; analytical models accept controlled duplication so the common question can be answered without reconstructing context across many joins.