Late-arriving dimensions (inferred members) for dimensional fact loads

[mattcasters]

Problem

Kimball marts assume dimensions load before facts. In real feeds, a fact often carries a natural key that does not yet exist in the dimension (“late-arriving dimension” / “early-arriving fact”).

Without support, generated fact pipelines either:
• fail the lookup,
• write null / 0 / “unknown” FKs without a real dim row, or
• force custom hand-built Hop graphs.

Users need a model-driven, GUI-configured way to keep facts loadable while dimensions catch up.

Proposed solution (MVP)

Support inferred dimension members on selected fact→dimension roles in the dimensional modeler (.hdm) and in pipelines generated by Dimensional Update.

When a fact role’s natural-key lookup misses:

1. Insert a dimension row with:
   • natural key(s) from the fact stream
   • technical columns as usual (dim_key / surrogate strategy, optional version / dates if SCD2)
   • descriptive attributes set to configurable placeholders (null, empty, or literals like N/A)
   • optional inferred / is_inferred flag (column name configurable; default on when feature enabled)
2. Use the new surrogate key as the fact FK (same as a successful lookup).
3. On a later dimension load, when the real member arrives:
   • match on natural key
   • replace placeholders (SCD1 update or first real SCD2 image)
   • clear the inferred flag
   • do not allocate a second surrogate for the same natural key (unless true SCD2 history requires a new version after real attributes exist — document the chosen rule)

Scope

In scope (MVP)
• Per dimension and/or per fact dimension role: policy
STRICT (current: no inferred insert) | INFERRED (create placeholder)
• GUI: dimension dialog + fact role UI (no file-only config)
• Generated pipelines: fact path uses Dimension Lookup / equivalent with create-on-miss (or explicit “insert inferred then lookup”) consistent with existing Hop warehouse transforms
• Dimension load path: promote inferred → real (clear flag, fill attributes)
• DDL: optional inferred boolean (or equivalent) on dimensions that enable the feature
• Model validation: natural keys required on roles that allow inferred; surrogate strategy must allow insert on miss (auto-inc / sequence / hash — document what works)
• Unit tests + at least one integration fixture (e.g. fact before dim, then dim catch-up)
• Docs under dimensional modeler / update action

Explicitly out of scope for MVP (follow-ups)
• Full park-and-retry landing tables and automatic redrive
• Late-arriving SCD2 (fact dated in the past requiring dimension version backdating / punch-through of historical FKs)
• Changing historical fact FKs when an inferred member is later split or corrected
• Junk / bridge / date-generator dimensions (unless trivial); start with normal DmDimension roles on transaction facts

Configuration sketch (model metadata)

On DmDimension or DmFactDimensionRole:
• lateArrivingPolicy: STRICT | INFERRED
• inferredFlagField: e.g. is_inferred (optional; default when INFERRED)
• inferredAttributeDefaults: null vs literal placeholders for Type 1/2 attributes

Acceptance criteria

• With policy INFERRED, a fact whose natural key is missing still loads with a valid FK to a new dim row marked inferred.
• A subsequent dimension update for that natural key fills attributes and clears inferred; surrogate key is stable for SCD1 (and documented for SCD2).
• With policy STRICT, behavior matches today (no inferred insert).
• Everything is configurable in Hop GUI on the .hdm canvas dialogs and survives save/reload.
• Dimensional Update generates pipelines that implement the policy without hand-editing.
• Docs explain the pattern and limitations (esp. SCD2).

References

• Original note: inferred SK on dim + fact when dim data is expected later
• https://medium.com/dataseries/resolving-late-arriving-dimensions-c0ebc9f818c3
• Current fact path: Dimension Lookup update=false per role (docs/dimensional-modeler-overview.adoc)

Open design questions

*. Default policy global on dimensional config vs only per role?

• SCD2 dimensions: inferred row as open current version only, or also support effective-dated backfill later?
• Prefer extending Hop Dimension Lookup create-on-miss vs a small pre-step that inserts inferred rows?
• Shared “Unknown” member (dim_key = 0/-1) as alternative to per-natural-key inferred rows?