Rapidly changing attributes

Why does an attribute that changes constantly have to be moved out of the table describing the thing it belongs to?

You have a customer table. It holds a name, a birth date, a home city — and also a credit-score band, an activity tier, and a churn-risk decile, all recomputed monthly. Every one of those columns genuinely describes the customer. By any ordinary reading of "put an entity's attributes in the entity's table", they belong exactly where they are.

And yet a data modeller will tell you to pull the last three out into a separate table, joined only through the fact rows. Why should where an attribute lives depend on how often it changes, when it describes the same thing either way? The attribute did not become less true. Something else must be doing the work in that argument.

The something else is history. Ask what a dimension table is actually for. It is not merely a lookup; it is meant to let you ask "what was this customer like at the time of the purchase?" — and that question only has an answer if you kept the old values. The standard machinery for that is the type-2 slowly changing dimension: when an attribute changes, you do not overwrite the row, you close it off with an end date and insert a new version with a new surrogate key. Facts loaded afterwards point at the new version; facts loaded before still point at the old one. History preserved.

Now make that machinery meet a volatile column, and count. Suppose a million customers, each with fifteen stable attributes and five that are recomputed every month. Under type 2, each recomputation that changes a value spawns a whole new row — all twenty columns, including the name and birth date that did not move. After a year, a dimension of one million rows has grown toward twelve million, and eleven-twelfths of what you stored is a re-copy of attributes that never changed. The volatility of five columns has been paid for by all twenty.

Notice what kind of problem that is. It is not incorrect — every row is right, every query returns the truth. It is a scale effect: a design that is sound at one rate of change becomes untenable at another, with no single moment where it stops working. That is why the rule sounds arbitrary when stated as a rule; it is the summary of an arithmetic you have not run.

Row count is the visible cost; the sharper one is cardinality. Every version row needs its own surrogate key, so the dimension stops being a small, cacheable table a query engine can hold and hash easily and becomes something on the order of the fact table itself — at which point the join it was supposed to make cheap is no longer cheap.

Here is the observation that unlocks the fix. Those volatile attributes are volatile, but they are not various. A credit band takes maybe eight values, an activity tier five, a churn decile ten — so their combinations number in the hundreds, and crucially that number does not grow with your customer count.

So instead of storing each customer's current profile inside their row, enumerate the profiles once, in their own small table — a mini-dimension — and let the fact row carry a key to whichever profile applied at the moment the fact occurred. The customer dimension keeps only the stable attributes and stops versioning. The mini-dimension is written once and thereafter mostly read. And the history you wanted is now recorded exactly where it belongs: in the facts, which are the things that have moments attached to them.

Does anything get lost? Yes, and it is worth naming honestly. Once the profile hangs off the fact rather than the customer, a customer who transacted nothing this month has no recorded profile for this month, so "the risk band of every customer as at 31 March" is unanswerable from facts alone. Kimball's answer is a periodic snapshot bridge — a small table of customer key, profile key, and effective date range — which restores it at the cost of a table you must maintain. You are choosing which table pays, not avoiding the bill.

A caveat about the boundary: "rapidly changing" has no threshold. The test is comparative — does versioning this attribute multiply rows faster than the dimension can bear, and is its value space small enough to enumerate? An attribute that changes constantly and takes millions of distinct values, a running lifetime-spend total say, fits neither table and belongs in the facts as a measure.

The misconception to clear away is that this is a normalisation argument. Dimensional models deliberately denormalise; nobody is splitting the table to eliminate redundancy for its own sake. The split is driven by rate of change interacting with row count — a physical concern, not a logical one, which is why the same attribute can correctly sit in the dimension for one organisation and in a mini-dimension for another with a hundred times the customers.

Two refinements connect the reasoning. First, the mini-dimension works only because volatile attributes are usually banded. Modellers often band them on purpose — storing a credit score as one of eight ranges rather than a number from 300 to 850 — precisely to keep the combination space enumerable. That banding is a real loss of precision, adopted knowingly.

Second, there is a middle option people forget: a type-1 overwrite, updating the value in place and keeping no history. If nobody will ever ask what the value used to be, that is the correct design and costs nothing — the mini-dimension apparatus exists only for attributes that are both volatile and historically interesting.

• Junk dimensions, which apply the same combination-enumeration trick to low-cardinality flags that are not volatile at all.

Every term the collection defines is gathered in the glossary.