Denormalisation

What is denormalisation?

Denormalisation is the deliberate practice of storing data redundantly, in wider tables or with repeated descriptive values, so that reading it needs fewer joins. It is the reverse of database normalisation, which splits data across related tables so each fact is stored once.

The line between the two is simple. A sales table that stores only keys, such as a product id, is normalised. A sales table that also carries the product name and category next to that key is denormalised. Those extra columns repeat on every row, which is the repetition normalisation exists to remove.

The difference is that here you choose the repetition. In a reporting table, keeping the customer region and product category next to each order line lets a report read from one place instead of stitching several tables together first.

The trade you are making

Denormalisation is a trade. You accept two costs to buy one benefit.

The benefit is read speed and simplicity. Every join a query performs adds work: the database has to fetch each table and assemble the rows before it can answer. Take the joins out by keeping the values on one wide table, and an analytical query that groups millions of rows does far less work. The queries also get easier to write, and columnar stores compress the repeated values well.

The first cost is storage. Repeated values take more room than a single key. On a large table that adds up, though the descriptive tables are usually small next to the events they describe.

The second cost is update anomalies. When the same value lives in many rows, changing it means changing every copy. Miss a few and the data disagrees with itself. Normalisation exists precisely to prevent this, so denormalisation hands the problem back to you. The usual answer is to never edit the redundant copy by hand, and to rebuild it from the source instead.

Denormalisation in analytics models

Analytics is where denormalisation becomes the default rather than the exception, and the reason is the workload. Operational systems handle constant small writes and stay normalised so each update touches one place. Reporting scans and groups large volumes and writes nothing back. This split between OLTP and OLAP is why reporting data is usually copied into a separate data warehouse and reshaped for reading.

The standard shape for that copy is a star schema: one central fact table of events surrounded by dimension tables that describe them. Those dimension tables are denormalised on purpose. A product dimension flattens the whole category and subcategory hierarchy into one wide table, so a report or semantic model can group by category without another join. Accepting that redundancy is the core idea of dimensional modelling.

A snowflake schema is the same star with its dimensions normalised back into separate tables, for example splitting product into product, subcategory, and category. It saves some storage and can suit very large dimensions, but it adds joins and makes the model harder to read. Most teams denormalise to a plain star unless they have a specific reason not to.

A worked example: sales by region

Suppose you want total sales by region. In a normalised model the amount sits on the order line, the customer on the order, and the region on the customer. The query has to join four tables to connect an amount to a region:

SELECT r.region, SUM(ol.amount)
FROM order_line ol
JOIN orders   o ON o.order_id = ol.order_id
JOIN customer c ON c.customer_id = o.customer_id
JOIN region   r ON r.region_id = c.region_id
GROUP BY r.region;

A denormalised reporting table copies the region onto every sales row when it is built. The same answer then reads from a single table with no joins, and an analyst never has to know how the four source tables fit together. The price is that the region value now repeats across many rows, so the pipeline that builds the table has to fill it, and nobody edits it by hand.

What to watch out for with denormalisation

• Build it from the source, never by hand. A denormalised layer should come out of a pipeline that rebuilds it from the system of record. Hand edits to redundant columns are how the copies start to disagree.

• Decide how history behaves. If a customer changes region, decide whether past sales keep the old region or take the new one. Slowly changing dimensions exist to make that choice explicit.

• Denormalise where data is read, not where it is written. Keep the operational database normalised for safe writes and denormalise the reporting copy. Reporting straight off the live system reintroduces the joins and competes with it for resources.

• Pair it with agreed definitions. Copying a value everywhere does not settle what it means. What counts as an active customer or a cancelled order still needs an owner, or you just publish the disagreement faster.