When does a mini-dimension beat keeping fast-changing attributes on a customer dimension?

## The problem it solves Dimensions are usually wide and slow: a customer dimension has dozens of attributes and a given customer changes rarely. That assumption is what makes row-versioned history affordable. It breaks when a handful of attributes on a very large dimension churn on a schedule — income band reassessed quarterly, credit-score band monthly, marketing segment on every campaign scoring run, life-stage annually. Do the arithmetic. Forty million customers, three volatile attributes, quarterly reassessment: keeping history in the base dimension adds up to 160 million wide rows a year, each duplicating name, address, contact details and every other attribute, to preserve three short values. The dimension stops being a dimension and becomes a change log. ## The mini-dimension Extract the volatile cluster into a separate small dimension. Its rows are the distinct **combinations** of the banded values, exactly as a junk dimension's are — but here the columns are demographic or behavioural profile attributes rather than transaction flags, and the motive is churn rather than fact-row width. ```sql CREATE TABLE dim_customer_profile ( customer_profile_key INTEGER PRIMARY KEY, income_band VARCHAR(20) NOT NULL, -- '0-25K', '25-50K', ... credit_score_band VARCHAR(20) NOT NULL, -- 'Poor', 'Fair', 'Good', ... life_stage VARCHAR(20) NOT NULL, tenure_band VARCHAR(20) NOT NULL ); ``` The fact table then carries two dimension keys where it previously carried one: ```sql CREATE TABLE fact_transaction ( date_key INTEGER NOT NULL, customer_key INTEGER NOT NULL, customer_profile_key INTEGER NOT NULL, amount DECIMAL(18,2) NOT NULL ); ``` The base customer dimension keeps the stable identity attributes and stops churning. The mini-dimension is small — typically thousands of rows, because it holds combinations of a handful of banded values, not one row per customer. And crucially, each fact row is stamped with the profile that was in force at the moment of the event, which is precisely the analytical question people ask: what kind of customer was this at the time they transacted? This is the construct Kimball numbers as SCD Type 4 — history moved out of the base dimension into a separate table — but the design decision here is about which attributes to split and how, not about history-tracking mechanics. ## Banding is not optional A mini-dimension only works if the combination count stays bounded, and continuous values are unbounded by construction. Raw annual income would create a row per distinct income value per combination; a raw credit score of 300–850 multiplies by 550. So continuous attributes are banded into a modest number of ranges before they enter: income into bands, score into rating buckets, tenure into year buckets. Four attributes with 8, 5, 6 and 5 bands give 1,200 possible combinations, of which perhaps a few hundred occur. That is a table you can read. The banding is itself a modelling commitment: the bands become the vocabulary every report speaks, and rebanding later invalidates comparisons across time. Choose bands with the business, and write them down. ## What you give up The profile is no longer an attribute of the customer row; it is an attribute of the event. That changes three things: - **"What band is this customer in today?"** no longer has a direct answer from the dimension. The usual remedy is to carry a current-profile key on the base customer dimension, updated in place, so the current profile is one join away for customer-list queries and the fact-level key remains the historical truth. The two coexist deliberately and must never be confused in a report. - **Counting customers by profile** must go through a fact table, so customers with no activity in the period do not appear. That is sometimes correct and sometimes a nasty surprise; a periodic snapshot of customers by profile is the fix if the business really wants inactive customers counted. - **Cross-attribute correlation queries** now need the mini-dimension join, which is cheap, but analysts must be told the attributes moved. ## When not to do it If the dimension is small, do nothing — versioning a hundred thousand rows costs nothing and keeping attributes together is simpler. If the volatile attributes are high cardinality and cannot be sensibly banded, a mini-dimension is the wrong tool. If the attributes are genuinely stable and the churn came from a broken source feed sending cosmetic changes, fix the feed rather than restructure the model — spurious churn from whitespace or casing differences is a common false trigger. ## What an interviewer is listening for The arithmetic (why row-versioning a huge dimension with volatile attributes is unaffordable), the banding requirement, the fact carrying both keys, and an honest account of the cost — that profile becomes event-scoped and "current profile" needs a separate mechanism. Candidates who name the pattern but cannot say what it costs have read the term rather than used it.