Why is an analytics model deliberately denormalized when its OLTP source schema is normalized?

## The short version Normalization and dimensional denormalization are not competing theories about the same problem. They are answers to two different questions: *how do I keep data correct while many writers change it a row at a time?* and *how do I let many readers scan enormous amounts of it and agree on the answer?* A schema that is excellent at one is usually mediocre at the other, which is why the same business data ends up in two shapes. ## What normalization is protecting in the source system The guarantee a normalized transactional schema gives you is that each non-key fact is stored once, in the table it belongs to. (The formal machinery behind that — the normal forms and functional dependencies — is the OLTP schema-design topic; here it only matters as the thing being traded away.) Because a customer's region name lives in exactly one row of one table, an application can rename that region with one `UPDATE`, and no other row in the database can disagree with it. Foreign keys, unique constraints and `NOT NULL` enforce that structure continuously, against dozens of independent code paths written by different teams over years, running concurrently. That guarantee is expensive on the read side and worth every penny on the write side. An order-entry system does thousands of small writes per second and its correctness is existential. ## Why the warehouse can spend that protection The analytics copy has a completely different writer profile: **one process, on a schedule, writing in bulk, deriving everything from a source of record it does not own.** Nothing else updates it. That changes the calculus in three ways: - Redundancy is no longer dangerous, because no ad-hoc statement can update one copy and miss another. The copies are all produced by the same transformation and can be re-derived by re-running it. - Constraints are less useful, because the load already knows the shape of what it writes; correctness is enforced by the transformation logic and by assertions run after it, not by the engine rejecting a bad row. - Write cost stops mattering. Nobody is trying to make a single-row update cheap in a table that is only ever rebuilt or merged in batches. ## What flattening actually buys the reader Consider a source where `customer -> city -> region -> country` is a four-table chain. Every question about sales by region requires an analyst to reconstruct that chain. In a dimensional model, `dim_customer` carries `city_name`, `region_name` and `country_name` as plain columns, so the same question is one join from the fact table. The gains compound: - **Fewer joins per query.** Join count is the dominant complexity in analytic SQL, and each one is a chance to fan out rows and inflate a `SUM`. - **Fewer chances to get the business logic wrong.** If every analyst re-derives "active customer" from three status columns, they will not all derive it identically. Flattening it into one modelled column makes the definition a property of the model rather than of whoever wrote the query. - **A learnable surface.** A star of a few tables with descriptive column names is something a business user can navigate; a 200-table normalized ERD is not. - **Stability.** The model is a published contract that survives source refactors, because the load absorbs the change. ## What it costs It is a trade, not a free win: - **Storage and load work.** The flattened attributes are computed and rewritten on every load. - **No engine-enforced integrity.** Nothing stops the load from writing a `region_name` that does not exist in the source. That is why grain-uniqueness and referential assertions become part of the pipeline. - **Change propagation is a job, not a statement.** Renaming a region is one `UPDATE` in the source and a re-derivation across many rows in the warehouse. - **Some history questions get harder or easier depending on choices you now have to make explicitly** — whether a changed attribute overwrites or creates a new version becomes a modelling decision rather than an accident. ## Where the line actually sits "Denormalized" is not a synonym for "one table". The consumption layer flattens; the staging and intermediate layers of an ELT pipeline usually stay close to the source shape on purpose, because a mechanical mirror of the source is easier to debug and re-extract. So the honest description is: the *presentation* layer is shaped for readers, and the layers behind it are shaped for the load. ## Answering this in an interview Say the two workloads out loud, name the guarantee normalization gives, say why one controlled writer makes that guarantee affordable to trade, then name the cost. Candidates who only say "warehouses denormalize because joins are slow" have half the answer; the other half is why it is *safe* to do so.