Late-Arriving Dimensions and Inferred Members

A nightly sales extract contains an order for a customer whose record has not yet reached the customer dimension. The order’s foreign key has nowhere to point. Three responses are available: drop the fact, abort the load, or invent a dimension row. The first loses revenue, the second stops the business from seeing yesterday’s sales, and the third is what every practical warehouse does — with consequences that outlive the load.

Why the dimension is behind the fact

The dimension and the fact come from different systems with different latencies. A customer master maintained by a back-office team may refresh weekly; an order stream may arrive within minutes. A new customer registered through a self-service portal exists in the order system before the master data process has created the corresponding record.

The gap is structural rather than accidental, and it has three common causes:

Ordering within the same source. The order row and the customer row are written by the same application, but the extract reads them at different times or from different tables with different replication lag. The customer row may be committed after the order in the same transaction, but the extract of the customer table may run before the transaction commits.

A source that has no dimension record at all. Guest checkouts, anonymous sessions, and imported historical data produce facts about entities that were never created in the master system. There is no future load that will supply them.

A hierarchy that arrives bottom-up. A new product is sold before its category assignment is decided. The fact references a product that exists in the source but has no attributes yet, and the dimension cannot be populated until merchandising finishes the classification.

The first two are timing problems and the third is a completeness problem, and they call for different handling even though both produce a null key.

The inferred member

An inferred member is a dimension row created to satisfy the reference, carrying the natural key and a placeholder for every other attribute. The surrogate key is assigned normally; the attributes are set to a sentinel such as “Unknown” or left null; and the row is marked so that a later load can recognise and update it rather than insert a duplicate.

The marker is the essential part. Without it, the arrival of the real customer record looks like a new entity and produces a second dimension row with a second surrogate key, while the facts already loaded point at the first. The two rows then both claim the same natural key, and any query that groups by natural key double-counts.

A minimal marker set is a boolean is_inferred column, or a pair of audit columns recording the load that created the row and the load that first supplied real attributes. The boolean is simpler and sufficient if the only question is whether the row is still a placeholder.

The update path then becomes a merge on the natural key that fills the attributes of an inferred row in place, keeping its surrogate key:

MERGE INTO dim_customer t
USING staged_customers s
   ON t.customer_id = s.customer_id
WHEN MATCHED AND t.is_inferred
  THEN UPDATE SET full_name = s.full_name,
                  region    = s.region,
                  is_inferred = FALSE
WHEN MATCHED AND NOT t.is_inferred AND t.region <> s.region
  THEN UPDATE SET region = s.region   -- or close and version, per SCD policy
WHEN NOT MATCHED
  THEN INSERT (customer_id, full_name, region, is_inferred)
       VALUES (s.customer_id, s.full_name, s.region, FALSE);

Keeping the surrogate key is what makes the fill-in place safe. The facts already loaded keep pointing at the same row, and they gain the attributes retroactively — which is correct for attributes that were true at the time of the fact (the customer’s region on the order date) and wrong for attributes that only became true later (a lifecycle stage assigned at onboarding). That distinction is a per-attribute question, and it is the same one that type 2 versioning forces: an inferred row has no valid-from timestamp for its real attributes, so the as-of reading of a filled-in inferred row is unknowable.

The alternative is to close the inferred row and insert a new version, leaving the facts pointing at the placeholder. Then the historical facts report “Unknown” for the customer’s region forever, which is honest but useless. Most implementations choose the in-place fill and accept that the first facts about a late-arriving entity carry the entity’s later attributes — a small, documented distortion concentrated in the earliest rows.

The placeholder that never gets filled

The in-place fill assumes a future load will supply the attributes. For guest checkouts and imported history, no such load exists, and the inferred row is permanent.

Permanent placeholders are not a failure of the pattern; they are the pattern working. The dimension needs a small set of standing members that represent classes of unknown rather than individual unknowns: an “unknown customer” member, an “anonymous” member, a “not applicable” member. These are inserted once, deliberately, with reserved surrogate keys — conventionally negative or very large — and facts with no identifiable entity point at them.

The distinction between a standing unknown member and an inferred row matters for the row count. One unknown member per class is stable; one inferred row per anonymous order makes the dimension grow with the fact table, and the surrogate key stops being a compact reference. The rule is to use the standing member when there is no natural key at all, and an inferred row when there is a natural key that will eventually resolve.

A third case sits between them: a natural key that is known to be invalid. A test order placed by the operations team, a data-entry row with a customer id of zero. Those should resolve to the standing unknown member rather than creating inferred rows, and the decision is a rule in the load rather than a judgement at query time.

The referential integrity question

Warehouse fact tables commonly declare foreign keys to their dimensions, and just as commonly the constraints are declared but not enforced. Where they are enforced, an inferred member is what allows the load to proceed without violating them — which is the pattern’s administrative benefit, not just its analytical one.

Where they are not enforced, the null foreign key is a silent alternative that is strictly worse. A null key does not fail the load, does not fail the constraint, and does not appear in any inner join. The fact row exists, contributes to COUNT(*), and disappears from every report that joins to the dimension. The row count in the fact table and the row count after the join differ, and nothing in the pipeline reports it.

That asymmetry is the argument for preferring an inferred member over a null: the inferred member is visible. It appears in the dimension as a row with placeholder attributes, so a report grouped by customer shows a bucket labelled unknown, and the size of that bucket is the size of the problem. A null is invisible.

The check that makes the problem measurable is a comparison per load between the count of distinct natural keys in the staged facts and the count of those keys present in the dimension. The difference is the number of inferred members the load will create, and a sudden rise in that number is a signal about the source rather than about the warehouse.

Repairing history after the fill

The in-place fill keeps keys stable and attributes correct going forward, but it leaves two artefacts.

The first is that facts loaded before the fill and facts loaded after it can disagree about the same entity’s attributes for the same period. A fact from January, loaded in January against an inferred row, shows the customer’s region as it was filled in March. A fact from February, loaded in February, shows the region as it was in February. If the region changed between January and March, the January fact carries the March value and the February fact carries the February value, and the two are inconsistent in a way that no timestamp on the fact explains.

The second is that a type 2 dimension with an inferred member has no valid-from for the inferred version. When the real attributes arrive, the fill-in place has to decide what valid_from the row gets: the date the entity first appeared in facts, the date the attributes became known, or the date of the master record’s creation. The first makes the history look complete and overstates what was known; the second is honest and leaves a period with placeholder attributes; the third requires the source to supply a creation date that it may not have.

The usual resolution is to record both, with the fill setting valid_from to the earliest fact date and an audit column recording the load date, and to document that the interval’s start is inferred rather than observed. Reports that care about the difference filter on the audit column.

None of these repairs is free, and none is complete. The pattern’s justification is comparative: an inferred member that is later filled produces history that is approximately right and visibly marked, while dropping the fact produces history that is exactly wrong and invisible. The cost is paid in a small set of facts whose attributes are anachronistic, and the mitigation is that those facts are identifiable by the audit columns rather than indistinguishable from correct ones.

The pattern also interacts with the fact table’s grain. An inferred member created at the natural key’s granularity is fine for a dimension keyed that way, but a dimension keyed at a finer grain — a customer per account rather than per person — cannot infer from a fact that supplies only the person. The inference has to happen at the level the fact can identify, which sometimes means the fact carries a second key or a degenerate dimension that later resolves into the finer one. Where that resolution is impossible, the fact is attached to the standing unknown member and the finer history is simply not available.