What is Denormalization in Database?
Understand database denormalization, how it differs from normalization, its trade-offs, and when to use it for read-heavy workloads.
Expected Interview Answer
Denormalization is the deliberate process of combining data from multiple related tables back into fewer, wider tables to reduce joins and speed up read queries, at the cost of some data redundancy.
While normalization splits data to eliminate redundancy, denormalization reverses parts of that for performance: it duplicates or pre-aggregates data so a query can read what it needs from one table instead of joining several. This trades storage space and write complexity, since duplicated data must be kept in sync, for faster reads. It is commonly applied to reporting tables, dashboards, and read-heavy systems where join cost outweighs the risk of redundancy.
- Faster reads by avoiding expensive joins
- Simpler queries for reporting and analytics
- Better performance for read-heavy workloads
- Can reduce load on the query planner for complex joins
AI Mentor Explanation
A broadcaster does not want the commentator flipping between five separate reference sheets for player stats, team form, and venue history during a live over; instead, they prepare one combined cheat sheet with everything pre-merged for instant reading. That merged sheet is denormalization: data that lives in separate normalized tables is deliberately duplicated into one wide table so it can be read instantly without joining sources live.
Step-by-Step Explanation
Step 1
Start from a normalized schema
Identify tables that require frequent, expensive joins for common read queries.
Step 2
Identify the read-heavy path
Pinpoint the specific query pattern, like a dashboard or report, that suffers from join cost.
Step 3
Duplicate or pre-aggregate data
Merge related columns into a wider table or materialized view for that read path.
Step 4
Manage sync on writes
Add logic, like triggers or scheduled jobs, to keep the duplicated data consistent with the source.
What Interviewer Expects
- Clear contrast with normalization and its trade-offs
- Understanding of when denormalization is appropriate (read-heavy workloads)
- Awareness of the cost: redundancy and write complexity
- Examples like reporting tables or materialized views
Common Mistakes
- Presenting denormalization as always bad practice
- Not mentioning the trade-off between read speed and write complexity
- Confusing denormalization with simply skipping normalization altogether
- Failing to give a concrete use case like analytics or dashboards
Best Answer (HR Friendly)
“Denormalization means intentionally combining data that would normally be split across multiple related tables back into fewer, wider tables to make reading faster. It is a trade-off: queries become simpler and quicker because there is less joining, but the database has some duplicated data that needs to be kept in sync when the source changes.”
Code Example
-- Normalized: requires a join every time
SELECT o.order_id, c.customer_name, o.amount
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
WHERE o.order_date > '2026-01-01';
-- Denormalized: customer_name duplicated onto orders
CREATE TABLE orders_denormalized (
order_id INT,
customer_id INT,
customer_name VARCHAR(100), -- duplicated from customers
amount NUMERIC,
order_date DATE
);
-- No join needed for the same read
SELECT order_id, customer_name, amount
FROM orders_denormalized
WHERE order_date > '2026-01-01';Follow-up Questions
- When would you choose denormalization over a normalized schema?
- How do you keep denormalized data in sync with its source tables?
- What is the difference between denormalization and a materialized view?
- What risks does denormalization introduce for data consistency?
MCQ Practice
1. Denormalization primarily optimizes for?
Denormalization pre-combines data to speed up reads, accepting redundancy and extra write complexity in exchange.
2. A key cost of denormalization is?
Because data is duplicated, updates must propagate to every copy, adding write-side complexity.
3. Denormalization is most commonly applied to?
Reporting and analytics workloads read far more than they write, making denormalization’s trade-off worthwhile.
Flash Cards
What is denormalization? — Deliberately combining normalized tables into fewer, wider tables to speed up reads, at the cost of redundancy.
What is the main trade-off of denormalization? — Faster reads in exchange for data redundancy and more complex writes to keep copies in sync.
When is denormalization appropriate? — In read-heavy systems like reporting dashboards and analytics where join cost outweighs redundancy risk.
How is denormalized data kept consistent? — Via triggers, scheduled jobs, or application logic that propagate updates to all duplicated copies.