System Design · medium
When would you denormalize a warehouse table, and what do you give up?
Asked in Data Engineer interviews, in the System Design round.
Short answer
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.
How to answer it
Definition
Denormalising means storing data redundantly, usually by folding dimension attributes into a fact table, so queries need fewer joins. You trade storage and update complexity for read speed and simplicity.
When it is worth it
- The join is on the hot path of a dashboard everyone loads, and it is measurably slow.
- The dimension is small, stable and rarely changes: country name, category label.
- Analysts keep getting the join wrong, and a wide table removes the chance to do so.
- The BI tool cannot express the join well, or charges per query.
What you give up
Storage more, though usually cheap on a columnar warehouse
Update cost a category rename now rewrites millions of fact rows
instead of one dimension row
Consistency risk the same attribute in three tables can disagree, and
eventually will
History a denormalised current attribute silently rewrites the
past, so last year's revenue moves into the new category
Flexibility the wide table answers the questions it was shaped for,
and answers the rest awkwardlyThe history point is the one worth leading with, because it is the least obvious and the most damaging: folding a customer's current segment into a fact table means every historical order appears to have been placed by someone in that segment.
The middle ground
On a modern warehouse the honest answer is usually to keep the star schema and materialise a wide table for the specific dashboard that needs it. That gives the read performance without giving up the modeled layer, and the wide table can be rebuilt whenever definitions change.
Key points
- Denormalise for a measured problem, not a suspected one. Check the query plan first.
- The real cost is update complexity and consistency, not storage.
- Folding in a mutable attribute rewrites history unless the dimension is Type 2.
- A materialised wide table on top of a star gets most of the benefit with none of the loss.
- Columnar storage and predicate pushdown make joins cheaper than they used to be, so the case for denormalising is weaker than the folklore suggests.
FAQ
- Is One Big Table just denormalisation? Yes, taken to its conclusion. Fine for one product surface, painful across many.
- What about pre-aggregation? A different tool for the same goal, and it loses detail rather than duplicating it.
- Does this apply to Postgres too? More so. Row storage makes wide tables costlier to scan and updates more expensive.
The 30-second answer
I would denormalise when there is a measured performance problem on a hot path, and when the attribute being folded in is small and stable, like a country name or a category label.
What I give up is mostly not storage, which is cheap. It is update cost, since renaming a category now rewrites millions of fact rows rather than one dimension row, and consistency, because the same attribute in several places eventually disagrees.
The one I would lead with is history. Folding a customer's current segment into the fact table silently rewrites the past, so last year's orders look as though they came from whatever segment that customer is in today. Unless the dimension is Type 2, that is a real loss. Usually I would keep the star schema and materialise one wide table for the dashboard that needs it.
In the interview
- What they are checking: that you can name the consistency cost and that the denormalized table is derived, not primary.
- Common mistake: denormalizing the source of truth itself, so the only copy of history is the flattened one.