ModelingAdvanced
When is denormalization the right call?
“Give an example of duplicating data on purpose and how you keep it correct.”
What this tests
- Read/write tradeoff
- Maintenance obligations of denormalized data
Answers by level
Read the beginner answer first and notice what is missing.
When a read is far more frequent than the writes that would invalidate it and a well-indexed query is still too slow. A follower count read on every profile view versus counting millions of follow rows is the classic case.
It comes with three obligations: a write path that updates the copy in the same transaction (a trigger is safest), a drift-detection query on a schedule, and a repair query — because it will drift.
Green flags · Red flags
Strong green flag · Insists every copy has a documented maintainer.
Green flags
- Names the write-path / drift-check / repair trio
- Distinguishes snapshot from redundancy
- Aware of hot-row counters
Red flags
- "Joins are slow so avoid them" as a blanket rule
- No plan for keeping the copy correct
Follow-up questions
F1
How do you detect that a denormalized counter has drifted?
Scenario
A profile page joins six tables and takes 400 ms. What would you denormalize and how would you maintain it?