Relational and dimensional modelling

The relational model was proposed to separate what users know about data from how a machine stores it, and that separation is still the reason the model has outlasted every storage technology it has run on.

Normalisation organises a schema so that each fact is stored in exactly one place. Take a single table holding one row per order line, with columns for order id, line number, customer id, customer name, customer postcode, customer city, product id, product name, quantity and unit price. It works until something changes.

• First normal formrequires each cell to hold a single value. A column holding "blue, large, cotton" or a repeating group of columns named product_1, product_2, product_3 breaks it, and every query against such a column has to parse text rather than compare values.
• Second normal form requires every non-key column to depend on the whole primary key rather than on part of it. In our table the key is order id plus line number, and customer_id is fixed by the order id alone: every line of the same order repeats it, and the customer name, postcode and city travel with it. That is a partial dependency, so the customer columns move to an Order table keyed by order id, and the order line keeps only what genuinely varies line by line.
• Third normal form requires that no non-key column is determined by another non-key column. Product id is fixed by the whole key, so it survives second normal form, but product_name is fixed by product id rather than by the order line. That is a transitive dependency, and product_name moves to a Product table with the order line holding the foreign key. The same test applied to the Order table catches city, which is determined by postcode rather than by the order, so the address detail moves out in turn.

What that buys is specific and worth naming, because "less duplication" is only the surface. It removes three classes of anomaly. An update anomaly is a customer changing city and the change landing on some of their order lines but not all, leaving two answers to one question. An insertion anomaly is being unable to register a new product until somebody orders it, because product details only exist on an order line. A deletion anomaly is losing the last record of a product when the final order referencing it is removed. Each of those is a case of one fact stored in many rows, and third normal form is the point at which the common ones disappear.

It also buys write efficiency and enforceable constraints. A change to a customer address touches one row. A foreign key can be declared and the database can refuse an order line pointing at a product that does not exist. This is why OLTP systems, which take payments and update records all day, are normalised as a matter of course.

What it costs is joins. A question such as sales by city by product category now crosses four or five tables, and the query planner has to be right about all of them. On a transactional workload that is a fair trade, because the reads are small and keyed. On an analytical workload that scans hundreds of millions of rows, it stops being fair.

OLAP workloads scan and aggregate large numbers of rows to answer questions about patterns: sales by region by month, demand by substation by season, claims by policy type by quarter. They touch few columns and enormous numbers of rows, which is the mirror image of the transactional pattern.

Three consequences follow. Column-oriented storage wins, because a query reading three columns should not pay to read the other forty. Joins become the expensive part rather than the cheap part, so the model tolerates duplication that a transactional schema would forbid. And the design has to be legible to the people writing the queries, who are analysts rather than application developers.

Query-first modelling is not unique to warehouses. Distributed stores make the same trade openly, and their documentation says so.

Dimensional modelling splits analytical data into two kinds of table. A fact table holds the measurements produced by a business event, one row per event, alongside foreign keys. A dimension table holds the descriptive context: who, what, where, when and how. Drawn out, one fact table sits in the middle with dimensions around it, which is why the arrangement is called a star schema. The primary figure above shows the geometry, and it is deliberate that nothing joins dimension to dimension.

The first decision is the grain: a written statement of exactly what one row of the fact table represents. One row per order line per day. One meter reading per meter per half hour. One claim payment per claim per event. The grain is declared before any column is chosen, and it is written on the face of the model rather than buried in a wiki, because it settles two things that nothing else can. It decides which measures may be added up, and it decides which dimensions can join the fact at all.

The retailer in the opening lost a fortnight to an undeclared grain. Order-level discount cannot be added up across a table at order-line grain, because it is recorded once per order and would be counted once per line. Stating the grain makes that visible at design time as an obvious mismatch rather than at month twelve as a four per cent gap.

Here is a worked design at that grain, with one fact and three dimensions.

• fact_sales_line. Grain: one row per order line. Foreign keys to date, product and customer. Measures: quantity, net sales value, line discount amount, cost of goods. Every measure is additive at this grain, which is the test the design must pass.
• dim_date. One row per calendar day, holding day, week, month, quarter, year, financial period, public holiday flag and season. A date dimension is worth building even though a database has date functions, because financial calendars and trading definitions live in it and stop every analyst reimplementing them.
• dim_product. One row per product, holding name, category, sub-category, brand, pack size and unit of measure. The category hierarchy is flattened into columns rather than split into further tables, which is the deliberate denormalisation that keeps the query one join deep.
• dim_customer. One row per customer, holding segment, region, channel of acquisition and signed-up date. Personal identifiers are held under access control rather than in the analytical dimension, because a dimension is read widely by design.

The result is one join hop from any measure to any label. Sales by brand by financial period is a single query that an analyst can write, read and check, and the model can absorb a fourth dimension later without reshaping anything that already exists.

A slowly changing dimension is one whose descriptive attributes change: a customer moves address, a product is reassigned to a different category, a site transfers to another region. The design choice is whether to overwrite the old value or keep it.

Overwriting is simple and destroys history. Every past sale to that customer is now reported against the new region, so last quarter's regional report no longer reproduces. Keeping history means adding a new dimension row when an attribute changes, with a validity period, and pointing new facts at the new row. Past facts keep pointing at the old row, so the regional report reproduces exactly as it was published.

Neither is correct in the abstract. The question is whether the business analyses history as it was or as it is now. Regulatory reporting and financial restatement usually need as it was. Current-state operational reporting usually wants as it is now. Many models carry both, with a current attribute alongside the versioned one. What is never acceptable is leaving the choice unmade, because the model will then behave one way in some places and another way elsewhere, and nobody will be able to say which report is right.

One big table takes the star and folds it flat: the fact is pre-joined to every dimension, so a query reads a single wide table and performs no joins at all. Every label a reader might filter on is already a column on the row.

The case for it has strengthened. Columnar engines only read the columns a query touches, so a table three hundred columns wide costs a three-column query almost nothing to scan. Compression handles the repetition well, because a column holding the same region string a million times compresses hard. Storage is cheap relative to analyst time. And self-service tools and language models both do noticeably better against one flat table than against a join graph they have to navigate correctly.

The case against it has not gone away, and it is a change cost rather than a query cost. The support figure above places the four styles on the two axes that actually trade against each other: how much of the model survives a change, and how many joins the query author has to carry. One big table sits in the easy-to-query, expensive-to-change corner. Adding an attribute means rebuilding the whole set. Correcting a dimension value means rewriting every row that carried it. There is no shared definition of product for a second subject area to conform to, so the second team builds their own wide table and the organisation is back to two answers.

The position that holds up in practice is not a choice between the two, it is a layering. Keep a modelled core with declared grain and conformed dimensions as the place where meaning is defined, and materialise wide flat tables from it for specific consumption: a dashboard, a feature set, a retrieval index. The wide table is then a derived serving artefact that can be dropped and rebuilt, rather than the only record of what the business means. Building the wide table first and treating it as the source of truth is what makes it expensive, not the shape itself.

Two practical points support that layering. Modern open table formats make rebuilding a derived table routine, with atomic commits and schema evolution rather than a weekend outage. And transformation tools have made derived tables cheap to define and re-derive, so a serving table stops being a permanent commitment.

A data vault splits the warehouse into hubs holding business keys, links holding the relationships between those keys, and satellites holding descriptive attributes with their history and their source. Nothing is overwritten; new records are appended with a load timestamp and the system they came from.

The property that decision rests on is this: a new source can be attached to an existing hub by adding a satellite, without reshaping anything already in place, and every attribute stays traceable to the system and load that produced it. That is what the top-left corner of the support figure is recording. The same decomposition is why an analyst never queries the vault directly. Answering an ordinary business question from raw hubs, links and satellites takes many joins, so a vault is normally loaded into a star-shaped presentation layer for consumption.

It earns its complexity when several conditions hold together. Many source systems describe the same entities and are expected to keep changing or multiplying. The organisation must be able to show which source and which load produced any attribute value, typically because of regulatory or merger-integration pressure. Full history must be retained, including corrections, rather than the current state only. And there is a team with the capacity to run a modelled core and a presentation layer.

It does not earn its complexity on a single well-governed source, on a small team, or where the driver is that the pattern is fashionable. The cost is real: two layers to build, two layers to test, and a shape that no analyst can read. A warehouse that loads three stable sources and serves one department will move faster with a normalised staging layer and a data warehouse star schema over it.