Late arriving dimensions

What are late arriving dimensions?

A late arriving dimension is a dimension row that arrives after a fact row that needs it. The fact is ready to load, but the descriptive context does not exist yet.

Example: a sale arrives with customer number C-8842. The sale should be loaded into the sales fact table now, but customer C-8842 has not yet arrived in the customer dimension because the CRM sync runs later.

From the fact-table point of view, the same problem is often called an early arriving fact. The fact is early, or the dimension is late. The modelling problem is the same: the fact needs a valid dimension key.

Why this is a problem

A fact table in a star schema normally stores surrogate keys to its dimensions. The sales fact row points to a customer key, product key, date key, store key, and so on.

If the customer dimension row does not exist yet, the ETL process cannot look up the customer surrogate key. You have three bad options if you do nothing:

• drop the fact and lose the sale

• delay the fact and lose freshness

• attach it permanently to an unknown customer and lose the later link

Late arriving dimensions become more common in event-driven and near-real-time pipelines, where facts may arrive from one system before master data arrives from another.

The Kimball pattern: inferred members

Kimball's standard pattern is to create an inferred member, sometimes called an inferred dimension row or placeholder.

When the fact load sees an unknown natural key, the ETL creates a new dimension row immediately. That row contains the natural key from the fact, a new surrogate key, placeholder values such as Unknown, and a flag that says the row is inferred.

The fact then loads with a real surrogate key. Later, when the proper dimension data arrives, the ETL finds the inferred row by natural key and fills in the missing attributes. The surrogate key stays the same, so the already-loaded facts do not need to be rewritten.

Step by step

1. Fact arrives. The incoming fact contains a natural key, such as customer number C-8842.

2. Lookup fails. The customer dimension has no row for C-8842 yet.

3. Create inferred member. Insert a customer dimension row with C-8842, a new surrogate key, Unknown descriptive values, and an inferred flag.

4. Load the fact. The sales row points to that surrogate key.

5. Real dimension arrives. The CRM sync later brings name, city, segment, and other attributes.

6. Fill the placeholder. Update the inferred member with the real attributes and clear the inferred flag.

The key point is stability. The surrogate key does not change, so reporting fixes itself as the dimension catches up.

Late arriving dimension versus late arriving fact

A late arriving fact is different. In that case, the fact itself arrives days or weeks late, while the dimension already exists and may have changed in the meantime.

With a late arriving fact, the job is to find the dimension version that was active when the event happened. In an SCD Type 2 dimension, that means searching by natural key and the event date, then choosing the row whose start and end dates cover that event date.

Example: a sale from March arrives in April, but the customer moved from Antwerp to Ghent in between. The sale should link to the March version of the customer, not the newest one.

Late arriving dimensions need inferred members. Late arriving facts need date-effective lookup against the right dimension version.

What to watch out for

Do not leave placeholders forever
An inferred member that stays Unknown for months is a data quality issue. Monitor open inferred rows.

Avoid duplicate inferred rows
If two facts for the same new customer arrive close together, create one inferred row, not two. Put a uniqueness rule on the natural key where possible.

Decide Type 1 versus Type 2 behaviour
Filling an inferred row for the first time is normally a Type 1 update. Later real changes may follow your normal SCD policy. Document that distinction.

Tell the business what Unknown means
Users may see revenue under Unknown customer until the dimension catches up. That can be expected, not a broken report.

Keep the natural key
The natural key is how the real dimension row finds the placeholder later. If you lose it, the inferred member cannot be resolved cleanly.