Using a mini dimension for contact attributes that change every edition

The contact dimension started at 60,000 rows. Somebody sensibly decided that seniority band, buying role and company size band were worth keeping history on, so they were tracked with a new row each time they changed, and eighteen months later the table has doubled. Distinct counts of people now need a durable key and a subquery, and the person who wrote the audience report last year has stopped trusting it.

A mini dimension for contact attributes fixes this specific failure. The volatile banded columns come out of the contact dimension and go into a small table of profiles, keyed from the fact, and the contact dimension goes back to growing at the rate of actual job changes.

What a mini dimension holds

The Kimball Group's technique pages, drawn from the third edition of The Data Warehouse Toolkit by Ralph Kimball and Margy Ross (Wiley, 2013), call this type 4 and describe it in three sentences.

The first sets the condition: "slowly changing dimension type 4 is used when a group of attributes in a dimension rapidly changes and is split off to a mini-dimension. This situation is sometimes called a rapidly changing monster dimension." The second widens it, and the widening matters for event data: "frequently used attributes in multimillion-row dimension tables are mini-dimension design candidates, even if they don't frequently change." The third states the mechanics: "the type 4 mini-dimension requires its own unique primary key; the primary keys of both the base dimension and mini-dimension are captured in the associated fact tables."

Margy Ross of the Kimball Group made the same point in her design tip of 5 February 2013, where the technique "is used when a group of dimension attributes are split off into a separate mini-dimension" and is "useful when dimension attribute values are relatively volatile".

The word doing the work in both is volatile. A mini dimension is a treatment for churn in the values, and the contact record at an exhibition organiser churns hard because the show asks the same qualification questions again every edition and gets different answers.

Which contact attributes belong in the mini dimension?

Three properties qualify a column, and all three have to hold.

The value has to be banded or otherwise low cardinality. Seniority sorted into five levels qualifies. A free text job title does not, because a mini dimension of every distinct job title string is just a second contact dimension with worse ergonomics.

The value has to change on a cycle shorter than the person's career. Company size band moves when the employer grows through a threshold. Buying role moves when a project ends. Seniority band moves on promotion, which for the audience an exhibition sells to happens more often than a change of employer.

And the value has to be used mainly for grouping. Reports that say how many senior buyers attended, or how the mix of buying roles shifted across editions, read these columns as filters and group-bys. They rarely read them one contact at a time.

Country and industry sector fail the second test at most shows and belong on the contact or company dimension. Registration ticket type fails the third, because it is an attribute of the transaction and belongs on the registration fact. Job title fails the first and stays on the contact dimension, where the type 2 or type 1 decision per column governs it.

Working the size: five by six by five

Take five seniority bands, six buying roles and five company size bands. Multiply them: 5 times 6 is 30, and 30 times 5 is 150. That is the entire mini dimension, forever, no matter how many contacts you have or how many editions you run. In practice a portfolio populates fewer, because combinations such as a graduate level contact holding a final approval role at a 10,000 employee firm may never occur, and a real table often sits somewhere near 110 to 130 populated rows.

Compare that with tracking the same three columns as type 2 on the contact dimension. Take a dimension already at 78,000 rows after five editions of job title and employer changes. Bands move more often than employers do, so assume 22 per cent of the 60,000 underlying contacts see at least one of the three bands change between editions. That is 13,200 extra rows each cycle, 52,800 across four transitions, and the dimension lands near 130,800 rows.

Divide 52,800 by 150 and the mini dimension carries the same information in about one three hundred and fiftieth of the rows. That ratio holds because the mini dimension stores combinations while the type 2 approach stores combinations multiplied by people.

The 22 per cent is a working assumption and yours will differ. It is measurable in an afternoon: take two consecutive editions of one show, join on a durable person key, and count how many people answered a qualification question differently the second time.

What changes in the fact table

Every fact that needs the profile carries the mini dimension key next to the contact key.

For a registration fact of 12,400 rows an edition, that is one more four byte integer per row, so 49,600 bytes, under 50 kilobytes an edition. Across a portfolio of eight shows and five years the cost of carrying the profile on every registration is under two megabytes.

The value is that the profile is recorded on the event. A registration row from the 2024 edition points at the profile the person had in 2024, and it keeps pointing there when the person is promoted in 2025. A question about the seniority mix at the 2024 edition is answered by joining the 2024 fact rows to the mini dimension, with no effective dating, no expiry columns and no as-of logic anywhere in the query.

There is a design consequence worth stating. The profile is now an attribute of the event, so it has to be captured at load time from what the source said then. If your loader resolves the profile key by looking up the contact's current band, you have built a type 1 attribute with extra steps, and the whole exercise buys you nothing. The lookup has to run against the qualification answers that arrived with that registration, which means those answers have to survive into the atomic rows you keep.

Why does a profile row with no owner feel wrong?

Because it inverts the habit of thinking about dimensions as descriptions of things.

A contact dimension row describes a person. A mini dimension row describes nobody. It is a combination that some number of people happened to match at some number of moments, and you cannot look at it and say whose it is. The first time a team meets this, somebody asks how to find a given contact's profile history, and the answer is that you read it from the fact rows, in date order, which feels like the wrong way round.

It stops feeling wrong when you notice what it makes cheap. Asking how the buying role mix moved between the 2024 and 2026 editions is a group-by on two integer keys over the registration fact. The equivalent question on a type 2 contact dimension needs the row that was current at each edition, which means date range predicates on both sides and a distinct count on the durable key to avoid double counting anyone who changed twice.

I would take the mini dimension for banded qualification data on any show that reruns its registration form annually, and I would keep the main contact dimension free of anything that a registration form can change. The single strongest argument is that it removes date range logic from the most commonly written query in an event warehouse.

The type 5 outrigger, and when to bother

There is a variant, and it answers the obvious complaint.

The Kimball Group documents type 5 as a way "to accurately preserve historical attribute values, plus report historical facts according to current attribute values", achieved by "embedding a current type 1 reference to the mini-dimension in the base dimension". The contact dimension gains one column holding the key of the profile that applies now, so a browse of contacts can show current seniority without touching a fact table.

The cost is a loader obligation. In the published wording, the process must "overwrite this type 1 mini-dimension reference whenever the current mini-dimension assignment changes", and for an annual show that means one more update pass after each edition loads.

Whether it is worth it depends on who browses the dimension. If your stewardship team works a contact list in a tool that reads the dimension directly, add the outrigger. If every consumer reaches contacts through a fact, skip it, because the column will drift the first time an edition loads out of order and nobody will notice for a year.

Where this stops

A mini dimension freezes your banding, and the bands are a judgement call.

Decide on five seniority levels and you have decided that the difference between a director and a vice president matters and the difference between two kinds of manager does not. Change the bands in 2027 and every profile row before that date means something slightly different from every row after it, with no marker in the data saying so. The honest fix is a band version attribute on the mini dimension and a note in the metric definition, and the honest admission is that year on year comparisons across a band change need a caveat you cannot compute away.

The technique also throws away the unbanded value. If seniority band is derived from a job title string and only the band is stored on the profile, you cannot later re-band the history without going back to the raw registration extracts. That is one more reason the raw files matter, and it is a decision that has to be made before the extracts age out.

It does nothing for cardinality either. Four bands of ten values each is 10,000 profile rows, which is still small, but the same design with eight such columns is a combinatorial table nobody can read. Two or three banded columns is the working range, and past that the right answer is usually a second mini dimension keyed separately from the same fact rather than one wide one.

Take your last edition's registration extract and count the distinct combinations of the qualification answers you actually report on. If that count is in the low hundreds while your contact dimension is in the tens of thousands of rows, you have the shape a mini dimension is for, and the next step is deciding which fact tables carry the second key and which edition each profile was observed at. Both belong in the same data platform conventions document as the rest of your dimension rules.