What is a shrunken conformed dimension, and what rule must it obey?

## Why a coarser dimension is ever needed A conformed dimension is usually shared as-is: two fact tables both reference `dim_product`, both at product grain. But not every business process produces facts at the atomic grain of the dimension. Budgets are set at brand and month. Forecasts are made at region and quarter. Aggregate fact tables deliberately summarise to a coarser level. A fact row at brand-month grain cannot carry a product key or a day key, because it does not correspond to one product or one day. The answer is a **shrunken conformed dimension**: a dimension rolled up to the coarser level, with its own key, fewer rows and fewer attributes than the base dimension it derives from. ```text dim_date (day grain) -> dim_month (shrunken) date_key month_key full_date year_month year_month calendar_quarter calendar_quarter calendar_year calendar_year is_fiscal_close day_of_week is_weekend ``` The shrunken `dim_month` keeps only attributes that are *well defined at month grain*. Day of week and weekend flag are dropped because they have no month-level value; year, quarter and fiscal-close flag survive because every day in a month agrees on them. ## The conformance rule The rule is strict subsetting, and it has three parts: 1. **Attribute subset.** Every attribute of the shrunken dimension must also exist in the base dimension, under the **same name**. No locally invented columns. 2. **Value identity.** For any base row, the value of a shared attribute must be identical to the value on the shrunken row it rolls up to. If `dim_date` says a date is in `2024-Q1`, the month row containing it must also say `2024-Q1`. 3. **Row rollup.** The shrunken dimension's rows must be exactly the distinct rollup of the base rows - no extra members, no members that combine base rows the base dimension does not group together. If all three hold, the shrunken dimension is conformed to the base, and facts keyed by either can be drilled across on the shared attributes. ## Why the rule is not merely pedantic Breaking it produces the same silent wrongness as any conformance failure, but harder to spot because the shrunken dimension is small and looks obviously correct. Two concrete failures: **A locally invented attribute.** Someone adds `season` to `dim_month` because the forecast team wants it, and never adds it to `dim_date`. Now a forecast can be reported by season and actuals cannot, so the comparison the whole design existed to enable is impossible for that attribute - and worse, if `season` is later added to `dim_date` with a different boundary rule, the two disagree. **A divergent classification.** The base product dimension assigns a product to brand `Atlas`; a separately built brand dimension, loaded from a different source, assigns the brand to a different parent group. Forecast at brand level and actuals rolled up from product level then disagree on totals, and the difference looks like a forecasting error rather than a modelling error. ## How to build it so it stays conformed The safest construction is to derive the shrunken dimension **from** the base dimension rather than loading it independently: ```sql CREATE TABLE dim_month AS SELECT DISTINCT year_month AS month_key, year_month, calendar_quarter, calendar_year, is_fiscal_close FROM dim_date; ``` Deriving guarantees value identity and row rollup by construction, and it makes the subset relationship literal - the column list *is* the subset. Loading the coarse dimension from a separate source is where divergence enters, and it is worth resisting even when a source system offers a convenient month or brand table. A continuous test is cheap and worth having: for each shared attribute, check that no base row disagrees with the shrunken row it rolls up to. ```sql -- Must return zero rows SELECT d.date_key FROM dim_date d JOIN dim_month m ON m.month_key = d.year_month WHERE m.calendar_quarter <> d.calendar_quarter OR m.calendar_year <> d.calendar_year; ``` ## Drilling across the grain gap Once conformed, a fact at month grain and a fact at day grain combine the normal way: aggregate the daily fact up to the coarser attributes both dimensions carry, aggregate the monthly fact by the same attributes, and align the two summaries. The join surface is precisely the intersection of the attribute sets - which, because of the subsetting rule, is just the shrunken dimension's attribute list. That is the practical reason the rule matters: the shrunken dimension's columns *are* the vocabulary the two processes can talk about together, so anything it invents privately is vocabulary no one can answer in.