Denormalization

Introduction

Denormalization deliberately introduces redundancy to make reads cheaper. Used in analytics warehouses, reporting tables, and hot read paths in OLTP systems.

Understanding the topic

Core concepts to understand:

• Trade write-cost & complexity for read-speed.
• Common patterns: cached aggregates, copied lookups, JSON blobs.
• Keep the source-of-truth in normalized form, derive denormalized data.
• Use triggers, materialized views or background jobs to refresh.
• Always document why a denormalization exists.

Syntax reference

Visual workflow / architecture:

OLTP (3NF)            Reporting (denormalized)
users                 user_summary
orders         ──ETL──▶  user_id, total_orders, ltv,
order_items                last_order_at, country, ...

Read 1 row instead of joining 4 tables.

Real-world use

Snowflake/BigQuery dashboards read denormalized fact + dimension tables. Many dashboards on Postgres use materialized views or cached counters for hot pages.

Best practices

• Denormalize behind a clear interface (view, MV, derived table).
• Refresh strategy must be explicit and monitored.
• Never denormalize PII or money source-of-truth.

Hands-on exercise

Interview preparation — practice these questions:

• Q1. Why denormalize?
• Q2. Three techniques to denormalize.
• Q3. Risks of denormalization.
• Q4. Materialized views — pros/cons.
• Q5. When NEVER to denormalize.