Denormalization Patterns

Section 3 decided when to denormalize. Each shape below comes with the query that builds it and the event that breaks it.

Pattern 1: Copy a column across the join

One attribute from a parent table is copied onto each child row. The region example is this pattern: region_name lands on orders. The build is a query the pipeline runs on every refresh.

CREATE TABLE orders_with_region AS
SELECT
    o.order_id,
    o.customer_id,
    o.order_date,
    o.order_total,
    r.region_name
FROM orders o
JOIN customers c
    ON o.customer_id = c.customer_id
JOIN cities ci
    ON c.city_id = ci.city_id
JOIN regions r
    ON ci.region_id = r.region_id

It breaks when the parent value changes and the job doesn't rerun. Pick columns that change rarely: names of regions, product categories, plan tiers.

Pattern 2: The pre-joined wide table

When several dashboards each need a different handful of columns from the same 4 tables, copying them 1 at a time produces 4 pipelines. The wide table joins once and carries every attribute analysts ask for, 1 row per order.

A query touches 1 table. The cost is width: 40 columns, most of them repeated text, across 2 billion rows. Columnar storage reads only the columns a query names, so unused width costs disk rather than query time, which is why wide tables are affordable in a warehouse and painful in a transactional database. The wide table also freezes the join at build time: the value on a row is the value at the last refresh, a feature for reporting and a bug when someone expects the current value.

Pattern 3: The stored derived value

A number a query would compute from many rows is computed once and stored on the parent. order_total on orders is the sum of its lines. order_count on customers counts that customer's orders. Every read of the number skips the aggregation, and the pipeline overwrites the stored column from a query like this one.

SELECT
    customer_id,
    COUNT(*) AS order_count,
    MAX(order_date) AS last_order_date
FROM orders
GROUP BY customer_id

It breaks the moment a new order lands and the stored count lags by 1. The refresh cadence of the pipeline is the staleness of the number, and the documentation must say so, or a support agent reads 11 orders on the customer page while the order list shows 12.

About this lesson

This section is part of the Beyond 3NF lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.