A mini dimension splits the fast-changing attributes out of a large dimension into a separate small dimension that the fact references directly. It exists to stop a big dimension - customer, with millions of rows - from exploding under SCD type-2 versioning every time a volatile attribute like an income band or a credit score changes. The volatile attributes move to their own compact table; the fact carries a key to both the main dimension and the mini dimension.

The SCD type-2 explosion it prevents

Suppose dim_customer has 5 million rows and you track change with type-2 history. Mixed into the stable attributes (name, signup date) are volatile ones: age band, income band, credit score band, marital status. If those change a few times a year per customer, type-2 forces a new full customer row on every change - 5 million customers churning into tens of millions of versioned rows, most of which differ only in one demographic band. The dimension balloons, and queries over it slow down for no good reason. The mini dimension breaks the link: stable attributes stay in dim_customer and version rarely; volatile ones live in a small dim_customer_demographics table whose rows are the distinct combinations of bands, not the customers.

dim_customer_demographics (
  demographics_key  INT,       -- surrogate, one row per band combination
  age_band          VARCHAR,   -- '25-34'
  income_band       VARCHAR,   -- '$50k-75k'
  credit_band       VARCHAR,   -- '700-749'
  marital_status    VARCHAR
)
-- a few thousand band combinations cover every customer

fact_account_txn (
  date_key, customer_key, demographics_key, amount, ...
)

Banding and the key on the fact

Two details make the pattern work. First, band the continuous attributes - store an income range, not a raw dollar figure - so the number of distinct combinations stays in the thousands rather than the millions. Second, put the demographics_key on the fact, not on the customer dimension. The fact is recorded at a point in time, so the demographics key it carries captures the customer's profile as of that event automatically - no type-2 versioning needed to know what band a customer was in when a transaction happened. When you need the current profile rather than the historical one, add an outrigger reference from dim_customer to the current demographics row, or keep a "current demographics key" column on the customer dimension.

Frequently asked questions

What does a mini dimension solve? It keeps a large dimension from exploding under SCD type-2 by moving rapidly-changing attributes into a small separate dimension, so the big dimension versions rarely and the volatile profile is tracked compactly.

Why band the attributes? Banding (age range, income range, score range) collapses millions of distinct raw values into a few thousand combinations, which is what keeps the mini dimension small enough to be worth having.

Why does the key go on the fact? Because the fact is timestamped, the demographics key it carries records the customer's profile as of that event for free - capturing change history without versioning the customer dimension.

How do I get the current profile? Keep a current-demographics key on the customer dimension (or reference the demographics table as an outrigger), so you can read the latest band without scanning the fact.