Data modeling · Foundations

Normalized vs denormalized

EasynormalizationdenormalizationOLTPanalytics

Interview question

What is the difference between normalized and denormalized data, and when do you use each?

How to answer

The short answer

splits data into related tables so each fact is stored once. That reduces update anomalies: change a customer city in one place, not in every order row.

Denormalization intentionally duplicates or pre-joins attributes into wider tables so reads are simpler and faster.

Output
Normalized (OLTP-ish):
  customers >< orders >< order_items >< products

Denormalized BI mart:
  fct_order_items already includes product_name, category

Why OLTP likes normalize

If product_name lived on every order line and marketing renamed a SKU, you would update thousands of historical rows just to fix a label. Separate products table = one update.

Why analytics likes denormalize carefully

BI tools and analysts want fewer joins. and gold marts often flatten selected attributes (or pre-join them) so dashboards stay fast.

Caution

If you denormalize a product name onto facts, decide whether the fact keeps the name *at order time* (usual for history) or always shows the *latest* name.

Interview tip

> "Normalize for writes and correctness; denormalize carefully for reads. In warehouses, star schemas are a controlled form of denormalization around a clear grain."