SQL, data modeling, data quality, EDA, visualization · insight-1051

Move fast-changing attributes into a mini-dimension instead of versioning the whole dimension

Situation
A customer dimension with millions of rows has a loyalty tier, risk band and credit-limit band that change monthly, and every change makes a type 2 copy of the whole row.
Action
Split the volatile, banded attributes into a small mini-dimension with its own key, and carry both keys in the fact table. Band continuous values (for example credit limit in ranges) so the mini-dimension stays small and analysis by band remains possible.
Reason
The history of the volatile attributes is recorded in the facts through the mini-dimension key, not by duplicating the large dimension.
Boundary
Large customer or member dimensions with frequently updated profile attributes.

Symptom or risk

The dimension grows by rows per change, joins slow down, and attributes that rarely change are copied again and again.

Small dimensions, or attributes that must be versioned together with the rest of the row.

An extra join, and queries by exact value instead of band are lost.

Banding so finely that the mini-dimension grows as large as the original.

Kimball's type 4 technique places rapidly changing attributes in a separate mini-dimension with its own key, and fact tables carry the keys of both the base dimension and the mini-dimension.
Kimball Dimensional Modeling Techniques: Type 4 (Add Mini-Dimension)