Type 4: Add Mini-Dimension / Current + History

The "Type 4" label has two meanings in practice. In Ralph Kimball's methodology, Type 4 means using a Mini-Dimension for rapidly changing attributes. In some data engineering teams, "Type 4" is instead used to describe separating current data from historical data into two tables.

The Kimball Definition (Mini-Dimension)

This design addresses the "rapidly changing monster dimension" problem, where frequently changing attributes such as credit scores can cause explosive row growth and degraded query performance when Type 2 history is maintained in a large dimension.

Type 4 separates relatively stable attributes from rapidly changing attributes into two dimensions that both connect directly to the Fact table:

• Base Dimension: Holds relatively stable descriptive attributes, such as names, dates of birth or other attributes that change infrequently. Its primary key identifies the individual entity.
• Mini-Dimension: Holds rapidly changing attributes, often represented as discrete bands or groups. Examples include age group, income bracket, credit-score band, customer tier or engagement level. Because thousands of customers share these exact same profile combinations, the Mini-Dimension contains a relatively small number of rows and uses its own primary key. It does not need to identify individual entities.

When a business event occurs, the Fact table records the Base Dimension key to identify the entity and the Mini-Dimension key to capture its rapidly changing profile at the time of the event.

Attributes Suitable for a Mini-Dimension

Here are common attributes well-suited for a mini-dimension:

Attribute	Example Values	Reason for Frequent Change
Age Group	20–29, 30–39, 40–49	Customers naturally age into new brackets
Income Bracket	Low, Medium, High	Salaries and household wealth shift over time
Credit Score Band	Poor, Fair, Good, Excellent	Scores update regularly based on financial behavior
Customer Tier	Free, Premium, Ultra	Users frequently upgrade or downgrade subscriptions
Engagement Level	Low, Medium, High	User activity and platform usage fluctuate rapidly

Example (Type 4: Kimball Mini-Dimension)

The following example shows a fact table that stores both Base Dimension Key and Mini-Dimension Key:

fact_sales_id	base_customer_key	mini_demographic_key	amount
9901	101	Profile X (Age 20-29, High Income)	$50.00
9902	101	Profile Y (Age 30-39, High Income)	$75.00

The Industry Definition (Current + History Tables)

Some data teams use "Type 4" for a different pattern: separating the latest version of a record from its historical versions.

The Current Table functions as a true Type 1 dimension. It holds only the latest version and uses the entity's durable key as its primary key. This enforces exactly one row per entity, preventing duplicate rows that could fan out joins and silently inflate fact sums. Meanwhile, the History Table preserves all previous versions and uses proper surrogate keys. While this layout keeps current-state queries fast, it adds pipeline complexity because both tables must remain synchronized.

NOTE

A temporal column is simply a column that stores dates or timestamps to track when a record was valid, active or occurred.

The History table must have at least two temporal columns to track the period during which each version was effective:

• Valid From (e.g., valid_from or effective_date): The date/timestamp when this specific version became effective.
• Valid To (e.g., valid_to or expiration_date): The date/timestamp when this specific version stopped being effective.

NOTE

As with Type 2's row-expiration column, whether valid_to is inclusive or exclusive depends on the column's data type and the convention adopted by the data model.

Example (Type 4: Current + History)

Alice moves from New York (NY) to California (CA) on May 10, 2024.

Current Table:

customer_id	name	state
C-101	Alice	CA

The Current table uses the durable customer_id as the primary key. If Alice moves again, this row is simply overwritten in place.

History Table:

customer_key	customer_id	name	state	valid_from	valid_to
101	C-101	Alice	NY	2023-01-15	2024-05-09

The History table uses a generated surrogate key (customer_key) as its primary key to allow multiple historical rows for the same customer_id.