Denormalization Trade-offs

Normalization is a trade-off, not a virtue

Normalization is not a moral good. It's a trade-off. It optimizes for safe writes: each fact stored once means no update anomalies. But it pays for that with joins on every read, and joins are the expensive part of analytics. The two worlds:

• OLTP (transactional apps): many small writes, correctness-critical → normalize (3NF).
• OLAP (analytics/BI): few huge reads, join-heavy → denormalize (flatten so a report is one scan, not a six-table join).

Denormalization deliberately reintroduces redundancy (copying product_name, category, customer_country into the fact/reporting table) so the read needs no joins. The cost is that if product_name changes you must update it in many places; in analytics that's fine because these tables are rebuilt by the loader, not hand-edited.

Worked example. From the 3NF schema, build one wide, denormalized reporting table, a real, physical table that stores its own rows (not a view):

CREATE TABLE rpt_sales AS
SELECT
    oi.order_id, oi.product_id, oi.qty,
    p.product_name, p.category,          -- copied from products
    o.order_date,
    c.email, c.country_code              -- copied from customers
FROM order_items oi
JOIN orders    o ON o.order_id   = oi.order_id
JOIN products  p ON p.product_id = oi.product_id
JOIN customers c ON c.customer_id = o.customer_id;

A BI query against rpt_sales ("revenue by category by country") now touches one table. The four-way join happened once, at build time, not on every dashboard load. CREATE TABLE … AS SELECT (CTAS) runs a query and stores its result set as a brand-new, physical table: real rows on disk, not a view. (A true view, by contrast, is just a saved SELECT that stores no data of its own and re-runs its query on every read, so it always reflects the latest source rows but pays its full query cost on every read. rpt_sales here is a materialized table, which is exactly why its join cost is paid once at build time and never again on read.)

In the warehouse this differs. This is the warehouse's whole point. Columnar warehouses (Snowflake/BigQuery/Redshift) are built to scan wide denormalized tables fast, and storage is cheap, so the redundancy barely costs anything. CREATE TABLE … AS SELECT (CTAS) is the standard build verb there too. The star schema (Module 3.5) is the disciplined middle ground between full 3NF and a fully-flat "one big table."

Keep it readable / common pitfall. Denormalize the mart, never the source of truth. If you denormalize your write-path OLTP tables you'll corrupt data via update anomalies. Pitfall: denormalizing too early or everything. Keep normalized staging/intermediate layers and denormalize only the final reporting layer, rebuilt each run.

Recap: normalization favors safe writes, denormalization favors fast reads; flatten the analytics/mart layer (redundancy is fine because it's rebuilt) while keeping the source-of-truth normalized.

Execution mode: you write a multi-statement script that builds a denormalized table with CREATE TABLE … AS SELECT. It runs against a fresh in-memory SQLite DB, then hidden assertion queries check row counts, copied-in columns, and computed revenue.