System Design · medium
Asked in Data Engineer interviews, in the System Design round.
Denormalize when read patterns are stable and join cost dominates — wide fact tables or pre-joined marts, so analysts are not writing six joins for every question. You trade storage, which is cheap, for write complexity, which is not: one source change now has to update several places, so consistency becomes your problem instead of the database's. Normalize what changes often, denormalize what is read often.
Denormalise when the read pattern is stable and the join cost is what analysts pay every time: a wide fact table with the common dimension attributes already on it, or a pre-joined mart for a dashboard that asks the same question daily.
What you get: simpler queries, faster dashboards, fewer ways to write the join wrong. What you give up:
The rule: normalise what changes often, denormalise what is read often. In practice that means a clean star schema as the source of truth and denormalised marts derived from it on a schedule, so the flattened tables are disposable and can always be rebuilt from the model.
Worked line: "The order dashboard joined six tables and took 40 seconds. A daily mart with the six attributes it actually uses, rebuilt from the star each night, loads in two. The star schema is unchanged, so nothing else was affected."