Mini-Dimensions and Junk Dimensions: Narrowing a Wide Fact Table

Two problems look different and have the same solution shape. The first is a dimension whose attributes change too often to version: a customer’s credit band, risk score band, and lifecycle stage can move every month, and a type 2 dimension would multiply the entity count by twelve per year. The second is a fact table carrying six columns that each hold a small set of values — order channel, payment method, delivery type, gift flag — none of which deserves a dimension table of its own, all of which are filtered and grouped on.

Both are solved by recognising that a dimension’s cost is driven by the number of distinct combinations, not by the number of entities or the number of columns.

Why versioning a fast attribute explodes

A type 2 dimension’s row count is the number of versions, and the number of versions is the number of change events. For a stable attribute like a customer’s region, changes are rare and the multiplier is small. For an attribute that changes monthly, the multiplier is the number of months the entity has existed.

The arithmetic is unforgiving because it compounds across attributes. If credit band, risk band and lifecycle stage each change monthly and independently, the combination changes up to thirty-six times a year, not twelve. Each combination that actually occurs becomes a row, and every fact load must resolve a natural key to one of them through a range lookup. The dimension stops being a small table that fits in memory and becomes a large one that has to be joined.

The problem is not the storage. It is that the versioned dimension is carrying attributes with different change rates, and the slow ones are being versioned at the fast ones’ rate. A customer’s name is duplicated once per credit-band change, which is pure waste and, worse, makes the as-of join expensive for reasons unrelated to the question being asked.

Splitting by change rate

The mini-dimension is a separate table holding only the fast-changing attributes, with a surrogate key per distinct combination rather than per entity. The main dimension keeps the slowly changing attributes and is versioned normally. The fact table carries two keys: one to the customer dimension, one to the mini-dimension.

The key property is that the mini-dimension has no natural key. It is keyed by the combination of attribute values, so two customers in the same credit band, risk band and lifecycle stage share a row. The row count is bounded by the product of the attributes’ cardinalities, not by the entity count — a few hundred rows for three attributes with a dozen values each, rather than a million.

That changes the fact table’s key resolution. The load looks up the combination, inserting a new mini-dimension row if the combination has not been seen. The insert is the only write, and it happens at most once per distinct combination ever, so the table converges quickly and then stops growing.

-- resolve a combination, creating it only if unseen
INSERT INTO dim_customer_band (credit_band, risk_band, lifecycle_stage)
SELECT :credit_band, :risk_band, :lifecycle_stage
WHERE NOT EXISTS (
  SELECT 1 FROM dim_customer_band
  WHERE credit_band = :credit_band
    AND risk_band   = :risk_band
    AND lifecycle_stage = :lifecycle_stage
);

The WHERE NOT EXISTS form is not safe under concurrent inserts in every engine — two sessions can both find nothing and both insert — so the table needs a unique constraint on the attribute triple and the loader needs to handle the duplicate-key error by re-reading. The alternative is a serialized load, which is acceptable because the insert volume is tiny.

The fact table’s join to the mini-dimension is a plain equi-join on a small table, which is the cheapest possible join. This is the payoff: the as-of question about credit band is answered by the fact’s own key, with no range predicate and no version interval. The fact row records the band that was in force when the event occurred, because that is what the load resolved at the time.

The loss is that the mini-dimension cannot answer “which customers were in band A on any given date” without going through the facts. It has no entity identity, so it cannot enumerate members. It also cannot be joined to another fact table at entity grain, which means it is not a conformed dimension in the sense that a bus matrix requires. It is an attribute bundle attached to facts, not a subject.

A middle form exists for attributes that change at a moderate rate and need entity-level history: keep them in the main dimension but version them, and accept the duplication of the slow attributes. The choice between the two is a comparison of version counts — if the fast attribute’s change rate is more than a small multiple of the slow ones’, the split pays.

Collapsing flags into one key

The junk dimension solves a different problem. A fact table accumulates small foreign keys: order channel, payment method, delivery type, promotion response, gift flag. Each is a tiny dimension with three to twenty members, each requires its own table, its own load, and its own join. Six of them add six joins to every query and six foreign keys to every fact row.

The junk dimension combines them into one table whose rows are the distinct combinations that actually occur. Six attributes with ten values each have a theoretical million combinations; in practice the observed set is far smaller, because many combinations never happen — a gift order is never paid by invoice, a wholesale order never uses the consumer channel.

-- one row per combination that has ever appeared
CREATE TABLE dim_order_flags (
  order_flags_sk BIGINT PRIMARY KEY,
  order_channel  VARCHAR(32)  NOT NULL,
  payment_method VARCHAR(32)  NOT NULL,
  delivery_type  VARCHAR(32)  NOT NULL,
  gift_flag      BOOLEAN      NOT NULL,
  UNIQUE (order_channel, payment_method, delivery_type, gift_flag)
);

The fact table carries one key instead of four, and queries that filter on any of the attributes join one small table. The table’s row count is the number of distinct combinations, which grows with observation rather than with theory and then plateaus.

Two rules keep a junk dimension from becoming a dumping ground. The first is that its members must have no attributes worth describing — if payment_method needs a settlement_days column, it is a real dimension and belongs in its own table. The second is that its members must be genuinely low-cardinality. A column with ten thousand distinct values inside a junk dimension produces a table as large as a real dimension but without a natural key or a subject, which is the worst of both.

The boundary between the two patterns is the presence of a natural key. A junk dimension’s combinations are anonymous: no source system emits “channel=web, payment=card, delivery=standard” as an entity with an identity. A mini-dimension’s combinations are equally anonymous, but they are attached to a specific entity through the main dimension’s key, which is why the fact carries both keys. The two are often used together on the same fact table — a customer dimension with a mini-dimension for bands, and a junk dimension for order flags.

Degenerate dimensions, which are neither

The third way to keep a fact table narrow is to leave an identifier in place. An order number, an invoice number, a ticket number: each has no attributes of its own, is not a dimension table, and is nonetheless needed in the fact table for drill-through to the source system and for counting distinct orders.

This is a degenerate dimension, and it is not a compromise. Putting the order number in a junk dimension would make the junk table as large as the fact table and would destroy the ability to count distinct orders without a COUNT(DISTINCT). Leaving it as a fact column is correct, and it costs the storage of one identifier per row.

The reason it belongs in the discussion of narrowing is that it competes with the alternatives: a modeller trying to avoid wide fact tables may be tempted to push the order number into a dimension, which is exactly backwards. The rule, stated in the grain declaration, is that a key with no attributes stays in the fact table.

Reading the combination counts

The decision to use either pattern starts with a count, not a judgement. For a candidate mini-dimension, the count is the number of distinct attribute combinations observed in a representative period, compared to the number of entities. For a candidate junk dimension, it is the number of distinct combinations across the flag set.

Both counts are cheap to obtain from the source before any table is built, and both change the design when they come back wrong. A mini-dimension candidate whose combinations number in the millions is not a mini-dimension; it is a real dimension with a fast-changing attribute that needs a different strategy. A junk-dimension candidate whose combinations number in the hundreds of thousands will produce a table larger than the fact table it serves.

The counts also have to be re-measured after launch. A junk dimension’s combination count grows as new channel and payment combinations appear, and a table that was four hundred rows in its first month can be four thousand a year later. That growth is manageable, but it is not zero, and a dimension that is assumed static while it grows is a join whose cost is assumed constant while it is not.

The common failure in both patterns is the same: they are introduced as an optimisation without the counts, and the resulting table is either so large that it defeats its purpose or so specific to the observed combinations that a new combination at load time has no row to point at. The second failure is the more dangerous one, because it produces a null foreign key rather than a slow query, and null keys are how fact rows disappear from reports without an error.