Why must a monthly aggregate fact table join to a shrunken date dimension derived from the day-level one?

## What a shrunken dimension is When you build an aggregate fact table at a coarser grain, its foreign keys can no longer point at the base dimensions. A fact row that means "this product, this store, this **month**" cannot carry a day-level `date_key`. It needs a dimension whose rows *are* months. A **shrunken dimension** (also called a rolled-up dimension) is that table: a conformed dimension reduced to the aggregate's grain, containing a strict subset of the base dimension's attributes with exactly the same values. `dim_month` is `dim_date` shrunk; `dim_product_category` is `dim_product` shrunk. **Conformed** means the two tables agree: every attribute that appears in both carries identical content and identical labels, so a filter or a grouping means the same thing on either. ## Derive it, never build it beside The correct construction is a projection of the base dimension: ```sql CREATE TABLE dim_month AS SELECT DISTINCT month_key, -- e.g. 202401 month_name, -- 'January 2024' calendar_quarter, calendar_year, fiscal_month_number, fiscal_quarter, fiscal_year FROM dim_date; ``` Every attribute in `dim_month` came from `dim_date`. There is no second definition of what fiscal Q1 is, no second spelling of the month label, no second opinion about which months belong to which year. The tempting alternative — generating a month table from scratch, or deriving month strings from the aggregate's own data — creates a parallel source for the same attributes. Parallel sources drift. Concretely: - Finance changes the fiscal calendar. Someone updates `dim_date`; the standalone month table keeps last year's boundaries, and monthly fiscal reports diverge from daily ones. - The month label is formatted `Jan-24` in one table and `January 2024` in the other. A drill-across report placing both on one row shows two labels for the same period, and any join or union on the label silently drops rows. - A product category is renamed. The product dimension picks it up; the hand-built category table does not, so category revenue no longer ties out to the sum of its products. None of these produce an error. They produce two credible numbers, which is worse. ## Only attributes that survive the rollup A shrunken dimension may keep only the attributes that are single-valued *at its grain*. `month_name`, `calendar_quarter` and `fiscal_year` are constant within a month, so they belong. `day_of_week`, `is_holiday` and the specific `date_key` are not constant within a month and must be dropped — including one of them would force an arbitrary choice (the first day? the last?) and any filter on it would return a nonsensical subset of months. The same test governs a product rollup: `category_name` is constant within a category and belongs; `unit_price` and `sku` vary within it and do not. Applying the test is precisely the discipline that makes the shrunken dimension trustworthy, and it is the reason candidates who say "just copy the dimension and group it" get pushed on in interviews. ## The key strategy The shrunken dimension needs its own key at its own grain: a `month_key` that already exists as an attribute of the date dimension, or a surrogate assigned to each category. What it must **not** do is reuse a base-dimension row's key to stand for the whole group — pointing the month aggregate at the first day of the month, for instance. That works until someone filters on a day-level attribute and gets "all months whose first day was a Monday", or until a report joins the aggregate to the day dimension and each month row matches exactly one day rather than the period it represents. ## Why this makes aggregate navigation possible A BI or semantic layer can only redirect a monthly query to the aggregate if grouping by `month_name` there yields the same labels and the same row set as grouping by `month_name` on the star. Conformance is the precondition; the shrunken dimension is how you obtain it. Without it, substitution is unsafe and the aggregate must be queried explicitly by people who know its quirks — which is how organizations end up with two revenue numbers. ## Regenerate it with the base Because it is derived, the shrunken dimension has to be rebuilt or merged whenever the base dimension changes. Treat that as part of the dimension's own load, not as a separate job someone might forget: the moment the two are refreshed on independent schedules, they can disagree for a whole day at a time, and a report run in that window is unreproducible. ## What interviewers listen for The words *conformed* and *derived*, the test that an attribute must be single-valued at the aggregate's grain, and a concrete drift example. Candidates who treat the month table as a trivial calendar utility usually have not been on the receiving end of two fiscal calendars.