Data Modelling

The fuller explanation

Before anything else, a table has a grain: the thing exactly one row represents. One order. One order line. One order line per day. Stating that sentence out loud is the whole of data modelling's first discipline, because every later argument about the table traces back to it. A table whose grain nobody can state is a table where sums double-count, and the bug is not in the query.

From there the shape is usually dimensional. Facts are the measurements at a chosen grain, long and narrow and always growing: what happened, when, how much. Dimensions are the things you slice by, wide and comparatively small: the customer, the product, the shop. Analysts ask their questions in exactly that language, a measure grouped by some attributes, which is why the split survives every change of fashion in storage technology.

The detail that separates a model that lasts from one that quietly lies is history. A customer moves city, a product changes category, and if you simply update the row then last year's revenue reports change too, silently, as though the sale had always been in the new city. Deciding which attributes must keep their history, and versioning those rows instead of overwriting them, is the hard part. Getting it wrong is not noticed until someone asks why a closed quarter no longer matches.