Normalization is the process of structuring relational tables to eliminate redundancy, so each fact is stored in exactly one place. The "normal forms" are a ladder of increasingly strict rules - 1NF, 2NF, 3NF, BCNF - each removing a specific kind of anomaly that redundancy causes. Understanding them matters in warehouse design precisely because dimensional modeling deliberately breaks some of them for query speed, and you should know what you're trading away.

The ladder of normal forms

First normal form (1NF): every column holds a single atomic value - no repeating groups, no comma-packed lists in one cell. Second normal form (2NF): 1NF plus every non-key column depends on the whole primary key, not just part of a composite key. Third normal form (3NF): 2NF plus no non-key column depends on another non-key column (no transitive dependencies). Boyce-Codd normal form (BCNF): a stricter 3NF where every determinant is a candidate key. In practice, 3NF is the working target for transactional schemas; the higher forms (4NF, 5NF) address multi-valued and join dependencies that rarely bite day to day.

Why normalize

Redundancy causes update, insertion, and deletion anomalies. If a customer's address is copied onto every order row, changing the address means updating thousands of rows and risking inconsistency if one is missed; you can't record a customer with no orders yet; and deleting their last order erases the address entirely. Normalizing to 3NF - customer attributes in one table, orders referencing it by key - makes each fact live once, so an update touches one row and integrity is structural rather than hoped-for. This is why OLTP systems, where writes dominate and correctness is paramount, are normalized.

-- denormalized (redundant): address repeats per order
orders(order_id, customer_name, customer_address, product, amount)

-- 3NF: each fact stored once
customers(customer_id, name, address)
orders(order_id, customer_id, product, amount)

The warehouse exception: deliberate denormalization

Analytical warehouses optimize for reads, not writes, and joins are expensive at scale - so dimensional modeling intentionally denormalizes. A star schema flattens related attributes into wide dimension tables (a star), accepting redundancy to avoid join chains; the more-normalized snowflake schema splits dimensions back out into related tables, trading some query simplicity for less redundancy. Inmon's enterprise warehouse, by contrast, keeps a normalized (3NF) core and denormalizes only in downstream marts. The point isn't that normalization is wrong in warehouses - it's that the read-heavy workload changes the trade-off.

Frequently asked questions

What are the main normal forms? 1NF (atomic values), 2NF (full dependency on the whole key), 3NF (no transitive dependencies), and BCNF (every determinant is a candidate key). 3NF is the usual practical target.

Why do data warehouses denormalize? Analytical queries are read-heavy and join-heavy; flattening attributes into wide dimension tables avoids costly joins and speeds queries, at the cost of controlled redundancy that the ETL keeps consistent.

Star vs. snowflake schema? A star schema denormalizes dimensions into single wide tables; a snowflake schema normalizes them into related sub-tables. Star favors query simplicity and speed; snowflake favors storage and maintainability.

Is denormalization just "bad design"? No - it's a deliberate trade for read performance in a context where writes are controlled by ETL. Uncontrolled redundancy in a write-heavy OLTP system is the bad case normalization prevents.