When do you deliberately keep tables in an analytics layer normalized instead of flattening them?

## "The warehouse is denormalized" is a statement about one layer The contrast drilled in every interview — normalized source, flattened analytics model — describes the **presentation layer**, the tables a BI tool and an analyst actually query. A real platform has several layers behind that one, and most of them look much more like the source than like the mart. Knowing where the flattening starts is what separates someone who has built a pipeline from someone who has read about star schemas. ## Case 1: the layers behind the mart A staging layer that mirrors extracted source tables is not laziness; it is a design choice with three payoffs. First, extraction stays mechanical — the transform between source and staging is renaming and type casting, so an ingestion failure is easy to localize. Second, it is diffable: when a number changes, you can compare staged rows to the source without re-running business logic. Third, it makes re-extraction and backfills cheap, because the raw shape is already there and only the transformations downstream need to re-run. Intermediate models sit between staging and the mart, holding logic that several published tables share. They are shaped for reuse, not for readability by analysts, and they frequently stay narrow and keyed rather than wide. If you flatten at ingestion, you lose all three properties: every debugging session has to run the whole transformation to find out what the source actually said. ## Case 2: relationships that are genuinely one-to-many This is the case that most often gets modelled badly. Suppose a customer can belong to several industries. There are only three ways to represent that: 1. **Numbered columns** — `industry_1` through `industry_5`. The schema now encodes an arbitrary limit, and the sixth industry forces a schema change. Filtering means `WHERE industry_1 = 'Retail' OR industry_2 = 'Retail' OR …`, which is error-prone and unindexable in any useful sense. Counting distinct industries requires unpivoting them again. 2. **Repeat the customer row, once per industry.** Now the "dimension" has several rows per customer, so joining it to a fact multiplies fact rows and every `SUM` over that join **double-counts**. This is the single most common cause of an inflated revenue number in a mart. 3. **A separate table holding the customer-to-industry pairs**, joined only when the question needs it, with an explicit decision about how measures are allocated when they are. Only the third is honest. The rule generalizes: **flattening is safe when the relationship is many-to-one from the row you are flattening onto.** A customer has one home country, so `country_name` flattens cleanly. A customer has many industries, so it does not. Interviewers probe this because the failure is invisible — the query runs and the total is simply too big. ## Case 3: attributes with a single point of maintenance Some reference data is owned by a process rather than by a source table: a regulatory code list, a finance-approved account mapping, a currency conversion table. Holding it once and resolving it at load or query time is attractive when the value changes on a governance cadence, is corrected retroactively, or must be auditable as a single row that someone signed off on. Copying it across millions of rows is possible — the load can re-derive it — but it costs a full rewrite each time it changes and makes the audit story "which rows carry which version?" instead of "here is the row". A related case: an attribute that changes far more often than it is queried. If a status flips several times a day and only a small fraction of reports use it, carrying it flattened means constant rewriting for little read benefit. ## The cases that are *not* good reasons Be as clear about the anti-reasons, because an interviewer will push: - "Normalization is best practice" — it is best practice for a workload this one is not. - "It saves storage" — storage is rarely the binding constraint in a mart, and analyst time and correctness usually are. - "The source is normalized so the mart should be" — the source's shape is evidence about the business, not a template for the model. - "We might need it later" — flattening is a load-time decision that can be revisited by re-running a transformation. ## How to decide, in one pass Ask three questions of every attribute you are thinking of flattening onto a table: is the relationship many-to-one from this row? Is the value stable enough that re-deriving it on each load is acceptable? Is the table the one consumers actually query, or a layer behind it? Flatten when all three say yes; keep the separate table when any says no. ## Answering this in an interview Lead with "denormalized describes the consumption layer", then give the three exceptions with the double-counting risk as the sharp one, and close with the anti-reasons. Naming the numbered-columns smell and the row-multiplication failure by name is what makes the answer sound like experience rather than theory.