Databases
Denormalization: interview questions and how to answer them
Denormalization deliberately duplicates or precomputes data to make reads cheaper, and the price is an ongoing obligation to keep the copies in agreement.
Written and reviewed by Sahil Srivastav
What it actually is
Denormalization is the deliberate reintroduction of redundancy into a normalized schema: storing a computed total alongside the rows it summarises, copying a customer name onto an order, keeping a maintained read model shaped exactly like a screen. The read becomes one cheap access instead of a join or an aggregation.
What distinguishes it from a design mistake is intent and ownership. The question that separates the two is "what keeps these in agreement, and how would you know if they stopped?" A denormalization with a clear answer is engineering. One without is a data-quality incident scheduled for later.
It is worth being precise about what is actually being bought, because it is frequently not what people claim. Denormalizing removes join or aggregation work at read time. It does not remove a missing index, it does not fix an N+1 pattern, and it does not help if the real cost was transferring too many rows. Those three account for most "we need to denormalize" conversations and none of them are solved by it.
Why it matters in production
Because the asymmetry between reads and writes is real. A product listing read thousands of times per minute that aggregates across three tables is doing the same work repeatedly to produce the same answer. Precomputing it converts a recurring cost into a one-off cost paid at write time, and when the read-to-write ratio is large that is straightforwardly correct.
And because the obligation it creates is where systems actually fail. The duplicate starts correct and drifts: a write path added later updates the source and forgets the copy, a backfill touches rows directly, a bug fix corrects history in one place. The failure is silent, discovered through a customer complaint, and expensive to reconcile. Interviewers probe for whether you treat that drift as inevitable and plan for it, or as something careful coding prevents.
How it works
Synchronous update in the same transaction
Update the source and the copy together, atomically. They can never diverge within the database, at the cost of making every write slower and coupling the write path to every denormalized consumer. Correct for a small number of derived values on a hot read path; it scales badly as the number of copies grows.
Database-maintained: triggers, generated columns, materialized views
Push the consistency obligation into the engine. A generated column is computed from the same row and cannot drift. A trigger maintains a cross-row aggregate transactionally. A materialized view precomputes a whole query and is refreshed on a schedule — accepting a staleness window in exchange for cheap reads. Each moves the work away from application code, which is where forgetting happens.
Asynchronous via events, with a reconciliation job
The source emits a change, a consumer updates the read model. This scales and decouples, and it is eventually consistent by construction — so a staleness window is part of the contract rather than a bug. It also needs idempotent consumption and a periodic reconciliation pass, because a dropped or misordered event otherwise leaves a divergence nobody detects.
Cache, which is denormalization with an expiry
A cache is a duplicate with a time bound on how wrong it can be. That bound is what makes it easier to reason about than a persisted copy: staleness is capped by TTL rather than unbounded. The trade-off is that it provides no help for the first reader and nothing to reconcile against.
Detecting drift is part of the design
Whichever mechanism you choose, something should periodically compare the copy against the source and alarm on mismatch. Without it, "they are kept in sync" is an assumption rather than a fact, and the first evidence of divergence will come from a user.
Implementing it
Measure before restructuring. Confirm the cost is genuinely the join or the aggregation, and not a missing index, an N+1 pattern, or an unbounded result set — denormalizing fixes none of those and makes the schema harder to reason about.
Pick the mechanism from the staleness your product can tolerate. Zero staleness means same-transaction or database-maintained; seconds of staleness opens up async read models and materialized views, which scale far better.
Write down which copy is authoritative. Every reconciliation question becomes answerable once that is explicit, and unanswerable when it is not.
Ship the drift detector with the denormalization, not after the first incident. A nightly job comparing counts or checksums is usually enough and is the difference between discovering drift yourself and hearing about it from a customer.
-- Generated column: derived from the same row, cannot drift, no maintenance.
ALTER TABLE order_items
ADD COLUMN line_total_minor integer
GENERATED ALWAYS AS (quantity * unit_price_minor) STORED;
-- Cross-row aggregate: needs a maintenance mechanism and a drift check.
ALTER TABLE orders ADD COLUMN item_count integer NOT NULL DEFAULT 0;
-- The reconciliation that makes the above trustworthy. Run it on a schedule
-- and alarm on any row returned.
SELECT o.id, o.item_count, count(i.id) AS actual
FROM orders o LEFT JOIN order_items i ON i.order_id = o.id
GROUP BY o.id, o.item_count
HAVING o.item_count <> count(i.id);Interview questions and how to answer them
When would you denormalize?
When the read-to-write ratio is high, the cost is demonstrably the join or aggregation, and the product can tolerate the staleness the chosen mechanism implies. The answer should always include the second half: which mechanism keeps the copy in agreement, and how drift is detected. "For performance" alone is half an answer and interviewers wait for the rest.
How do you keep the duplicate consistent?
Four options with different costs. Same transaction: never diverges, slows every write. Database-maintained via generated columns, triggers or materialized views: moves the obligation into the engine, where it cannot be forgotten. Async via events: scales, requires idempotent consumers and accepts a staleness window. Cache with a TTL: bounds how wrong it can be. Pick from the staleness tolerance, then add a reconciliation job regardless.
What is the risk that actually materialises?
Silent drift. The copy starts correct and diverges when a write path added six months later updates the source and not the copy, or a backfill writes directly. Nothing errors. The first signal is usually a customer noticing two numbers disagree, at which point reconstructing which was right is manual and expensive.
Is a materialized view denormalization?
Yes — precomputed, duplicated data with a refresh policy. Its advantage is that the duplication is declarative and the engine owns the refresh, so there is no application code path to forget. Its cost is the staleness between refreshes, and that a non-concurrent refresh takes a lock that blocks readers.
Your reads are slow. Why might denormalizing not help?
Because the cost is frequently not the join. A missing index, an N+1 access pattern, or an unbounded result set all produce slow reads and all survive denormalization unchanged — you end up with a more complex schema and the same latency. Read the execution plan before restructuring.
Answers that lose the round
- Denormalizing before confirming the cost is actually the join or aggregation
- No stated answer to "what keeps these in agreement?"
- No drift detection, so divergence is reported by a customer rather than a job
- Copying a value that is genuinely volatile, so the copy is wrong most of the time
- Maintaining the copy only in the write path you remembered, missing backfills and admin tools
- Describing an eventually-consistent read model as though it were transactional
- Treating denormalization as a fix for N+1 queries or a missing index
FAQ
Does denormalization break normalization "rules"?
It is a deliberate trade against them, which is different from violating them accidentally. The normal forms describe where redundancy causes anomalies; denormalizing means accepting those anomalies as possible and engineering against them. The decision is only defensible when you can state what you accepted.
Is a read model in CQRS the same thing?
Structurally yes — a denormalized projection shaped for queries, maintained from the write side, eventually consistent. CQRS formalises it into an architecture with explicit separation; a derived column is the same idea at the smallest possible scale.
How stale is too stale?
A product question, not a technical one. Seconds are usually fine for counts and listings; a payment balance or stock availability frequently is not. Decide the tolerance explicitly and pick the mechanism to match, rather than discovering the tolerance from a complaint.
Should the copy be in the same database?
Keeping it in the same database allows transactional maintenance, which is the strongest guarantee available. Moving it elsewhere — a search index, a cache, a separate read store — buys scale and query capability and makes the consistency obligation asynchronous and your responsibility.