SCD Type 4 handles changing dimension attributes by splitting history into a separate table. The main dimension holds only current values, kept small and fast, while a companion history table records every past version. It's the technique of choice when an attribute changes often enough that storing its history inline (as Type 2/6 would) bloats the main dimension and slows the queries that only need the current value.

Two tables: current and history

The current dimension carries one row per entity with today's values. A parallel history table carries one row per version, with effective-date ranges, so the full timeline is recoverable without weighing down the main table.

-- current values only (one row per customer)
dim_customer (customer_key, name, tier, current_address)

-- full history (one row per version)
dim_customer_history (
  customer_key, address, tier,
  valid_from, valid_to
)

Day-to-day queries join facts to the slim current dimension; the rare "what was it back then" question reaches into the history table. The hot path stays narrow.

The mini-dimension variant

The best-known form of Type 4 is the mini-dimension, used for rapidly changing attributes - things like age band, income bracket, or a credit-score range that would generate enormous numbers of Type 2 versions if kept in the main dimension. Those volatile attributes are pulled out into their own small dimension of all possible combinations, and the fact table carries a foreign key to it. The customer dimension stays stable; the mini-dimension key on the fact captures which band applied at the time of each event. See mini-dimensions for the full pattern.

When to choose Type 4

Reach for Type 4 when an attribute changes frequently, when the main dimension is large and most queries want only current values, or when volatile attributes would otherwise explode a Type 2 dimension's row count. The cost is an extra table and the join logic to assemble a historical view. If changes are infrequent and history is queried as often as current values, a straightforward Type 2 is simpler.

Frequently asked questions

What is SCD Type 4? A technique that keeps current values in the main dimension and stores historical versions in a separate history table, so the main dimension stays small and fast.

What is a mini-dimension? The Type 4 variant for rapidly-changing attributes: volatile fields are moved into a small separate dimension of value combinations, and the fact table references it directly to capture which combination applied at event time.

How is Type 4 different from Type 2? Type 2 stores history as extra versioned rows inside the same dimension; Type 4 moves history to a separate table, keeping the primary dimension current-only.

When should I avoid Type 4? When changes are rare and historical queries are as common as current ones - the extra table and join overhead aren't worth it, and Type 2 is simpler.