Data Modeling
Intermediate 13 min read Data Modeling

Normalization vs Denormalization

Write-optimized systems vs read-optimized models

OLTP normalizes to avoid write anomalies; BI denormalizes to read fast. The two layers optimize different things — understand the split so you model each correctly.

normalizationdenormalizationOLTPOLAPstar schema3NF
Prerequisites: Data Modeling Overview
Before we start

What you will learn

  • Explain why OLTP normalizes and why BI denormalizes
  • Recognize 3NF vs star-schema trade-offs
  • Decide where each approach belongs in the stack
Introduction

What is this?

Normalization (3NF and friends) breaks tables into keys and dependency-free attributes to prevent update anomalies and keep writes atomic. Denormalization (stars, wide tables) duplicates attributes on purpose so analytic queries scan fewer joins and read fast. BI models denormalize dims and keep facts at a clear grain as a deliberate trade.

Motivation

Why do you need it?

  • Analytic queries are read-heavy; denormalization cuts join cost.
  • Normalized source tables are slow and awkward as direct BI inputs.
  • Understanding the split explains why 'the warehouse is not the OLTP db'.
  • You'll defend this trade constantly in model reviews.
Think about it

Mental model

Imagine

An inventory ledger vs a menu board

Think of

Normalized vs denormalized

The ledger records each part's supplier once (normalized — no contradictions). The kitchen menu writes the dish's price onto every table's board (denormalized — repeat but fast). One keeps truth; the other serves diners quickly. BI is the menu; the ledger stays upstream.

Mechanics

How it works

  • Normalization splits attributes across tables joined by keys; uniqueness enforced.
  • Denormalization repeats attributes (e.g., category on every product row) to avoid joins.
  • Stars are semi-denormalized: dims fully denormalized, facts kept clean at grain.
  • Kimball-style dims are flat attribute bags; snowflakes re-normalize dims a bit (rarely worth it).
  • The layer decides: OLTP normalizes, warehouses conform, reporting stars denormalize.

OLTP → warehouse → denormalized BI.

OLTP writes
ELT
Warehouse
Star model
Reports
Hands-on

Examples

The same business, two shapes

The same business, two shapes
-- Normalized (OLTP):
product (prod_id PK, name)
category (cat_id PK, name)
product_category (prod_id, cat_id)  -- FK pairs
 
-- Denormalized (BI dim):
dim_product (prod_id PK, product_name, category_name,
             subcategory_name, brand, margin_bucket)
 
Trade: some duplication (category repeated) buys zero-join
filtering and one-view dimension descriptions.

The dim repeats category name instead of joining category tables — a deliberate read-speed trade the warehouse accepts.

Real world

In production

  • A sales model keeps dim_product flat, so 'filter by category' scans one column.
  • An ops model joins margins into the order fact directly, skipping margin-join at query time.
  • A source ERP stays strictly 3NF; the BI layer denormalizes only downstream of it.
Guidelines

Best practices

  • Return by layer identity: normalize only where writes dominate (OLTP).
  • Denormalize dims; keep facts clean and grain-precise.
  • Document repeated attributes so consumers know why duplication exists.
  • Test read performance before deeply denormalizing a wholesale.
Watch out

Common mistakes

  • Shipping normalized ERP tables straight to dashboards (join hell).
  • Fully denormalizing facts into 'one giant table' everyone fears.
  • Snowflake-ing dims to 'be clean', losing the read advantage.
  • Vague explanations for duplication — the model becomes a guessing game.
When things break

Debugging tips

  • Slow cross-filtering → likely missing denormalization or a snowflake dim.
  • Write anomalies (same product in two categories) → normalization intent violated upstream.
  • Update lags → denormalized copies must be rebuilt on source change.
Interviews

Interview checkpoint

Test yourself

Quick quiz

0/2
1

Normalization primarily optimizes which kind of workload?

2

Denormalization is acceptable in BI dimensions to cut query-time joins.

Go deeper

Official documentation

Kimball - rapid star schema design
docs