Why does an SCD Type 4 mini-dimension help when a dimension attribute changes weekly?

## The problem Type 4 solves Slowly Changing Dimension Type 2 is priced per change: each change to any tracked attribute inserts a new row for that member. That price is fine for attributes that move once every few years — a relocation, a category reassignment. It is ruinous for attributes that move constantly. Take a 40-million-row customer dimension with a behavioural score that is recalculated weekly. Under Type 2 that is up to 52 new rows per customer per year, each one a full copy of the customer's name, address, segment and every other column, and none of the copies differ in anything anyone browses the dimension by. Within two years the "dimension" has more rows than the fact table it decorates, loads take hours, and every distinct-count query is a trap. The insight behind Type 4 is that the volatile attributes are not really describing the same slowly-changing thing. They are describing a **state the member was in at a point in time** — which is much closer to something a fact should reference directly. ## The mini-dimension form The volatile attributes are removed from the base dimension and placed in their own table with one row per **distinct combination** of their values: ```sql CREATE TABLE dim_customer_profile ( profile_sk INTEGER PRIMARY KEY, age_band VARCHAR(10), income_band VARCHAR(10), score_band VARCHAR(10), UNIQUE (age_band, income_band, score_band) ); ``` Crucially the rows are combinations, not members: if there are 8 age bands, 10 income bands and 5 score bands, the table has at most 400 rows *no matter how many customers you have or how often they move between bands*. Movement between states costs nothing, because every state already exists as a row. The fact table then carries two dimension keys — one for the stable customer, one for the profile in force at event time: ```sql CREATE TABLE fact_sales ( sale_sk BIGINT PRIMARY KEY, date_sk INTEGER, cust_sk INTEGER, -- stable customer dimension profile_sk INTEGER, -- mini-dimension state at the time of sale amount DECIMAL(12,2) ); ``` A query can now slice sales by income band at time of purchase without the customer dimension ever having versioned. **Banding is what makes it work.** Raw continuous values — an exact age, an exact score, a balance to the cent — would produce a combination row per distinct value tuple and reintroduce the explosion. Type 4 requires you to agree with the business on bands, which is itself a modelling decision with reporting consequences: the bands become the vocabulary of every future analysis, and changing them later restates history. ## The history-table form The same type number is also used for a second, simpler split: keep only the **current** version of each member in the main dimension and push retired versions into a parallel history table with the same shape. Everyday current-state queries hit a small, fast, one-row-per-member table and need no current-row filter; the rarer as-was analyses join to the history table. It is the same idea — segregate the part that grows from the part that is browsed — applied to whole rows rather than to volatile columns. ## What you give up - **State is only recorded where facts exist.** The mini-dimension key is stamped onto facts. If a customer's band changes during a quiet month with no transactions, nothing in the model remembers it. If the business needs a continuous record of band membership, you need a periodic snapshot fact, not a mini-dimension. - **Two joins, and a rule.** "Income band" is now reachable two ways in a naive design, and analysts must know they should use the profile attached to the fact, not the customer's current profile. Some designs additionally carry the *current* profile key on the base dimension row for current-state browsing; that convenience is exactly where analysts confuse as-was with as-is, so it must be named clearly. - **The bands are a commitment.** They are visible in every report, and redefining them is a breaking change to a published model. ## Deciding when to reach for it The honest signals are: the attribute changes on a cadence measured in days or weeks; the dimension is large; the attribute is analytical rather than identifying (nobody looks a customer up by it); and its values either are, or can be, a small closed set. When those hold, Type 4 converts an unbounded row explosion into a bounded lookup. When they do not — the attribute changes twice a year, or the dimension has fifty thousand rows — plain Type 2 is simpler and the explosion never materialises. Reaching for a mini-dimension on a small, stable dimension is over-engineering, and interviewers notice.