Dimension Completion
Resolve missing historical dimension coverage before it breaks temporal joins, snapshots and reporting.
Dimension completion gives every required fact period a deliberate dimensional context. It prevents valid facts from losing attributes or disappearing because the related dimension history starts later, ends earlier or contains gaps.
A fact exists, but no valid dimension row covers the same period.
Historical fact and dimension models often begin at different points in time. A contract, transaction or monthly snapshot can exist before the related customer, product, hierarchy or ownership history was captured.
The fact row can be valid and the dimension can also be technically valid. The problem is that their temporal coverage does not align.
A temporal join then returns no match. Depending on the query, this can create missing attributes, unknown values or complete loss of the fact row.
The contract exists from January, but the customer assignment starts in April.
SCD2 preserves captured history, but does not guarantee required reporting coverage.
A Slowly Changing Dimension Type 2 model records the versions that were captured. It does not automatically guarantee that every fact date has one matching dimension version.
Historical reporting normally depends on two separate guarantees:
A dimension may satisfy the first guarantee and still fail the second. Dimension completion addresses that missing coverage.
Compare required fact periods with the resulting dimension coverage.
Dimension completion cannot be proven from one target table alone. The required reporting periods must be compared with the dimension result produced for those periods.
Both examples below use the same required monthly contract periods. The difference is whether every period receives one deliberate and explainable customer result.
These are the reporting periods for which the contract must resolve to an intended customer dimension state.
contract_id,snapshot_date C-1001,2024-01-31 C-1001,2024-02-29 C-1001,2024-03-31 C-1001,2024-04-30 C-1001,2024-05-31 C-1001,2024-06-30
January through March are not directly observed, but the earliest known customer assignment is deliberately completed backward and its provenance remains visible.
contract_id,customer_key,snapshot_date,completion_method C-1001,Customer A,2024-01-31,earliest_known_value_backfill C-1001,Customer A,2024-02-29,earliest_known_value_backfill C-1001,Customer A,2024-03-31,earliest_known_value_backfill C-1001,Customer A,2024-04-30,observed C-1001,Customer A,2024-05-31,observed C-1001,Customer A,2024-06-30,observed
January through March are required by the fact history, but no customer assignment has been resolved for those periods.
contract_id,customer_key,snapshot_date,completion_method C-1001,,2024-01-31,missing_dimension_match C-1001,,2024-02-29,missing_dimension_match C-1001,,2024-03-31,missing_dimension_match C-1001,Customer A,2024-04-30,observed C-1001,Customer A,2024-05-31,observed C-1001,Customer A,2024-06-30,observed
Fact and dimension histories often come from different systems and processes.
Dimension completion is common in cross-system historical models.
A policy exists before reliable policyholder or ownership history becomes available.
Revenue history exists before territory, account or sales hierarchy history was tracked.
Sales facts exist before product categories, classifications or risk attributes were historized.
A new historical model requires dimension coverage that was never fully stored by the source.
Choose a deliberate rule for every uncovered period.
There is no universal completion strategy. The correct approach depends on what the missing period means and what historical evidence is available.
Extend the earliest observed value backward when evidence supports that it already applied.
Continue the last known value when the state remains valid until explicitly replaced.
Preserve the fact without claiming a historical value that cannot be supported.
Reconstruct missing intervals from events, snapshots, audits or other historical evidence.
Keep the period uncovered when unknown or unassigned is the correct business truth.
Stop publication when complete dimension coverage is a required model invariant.
Complete or classify the dimension gap before joining it to the fact model.
Every required fact period should resolve predictably.
Completion logic can create false history when used without semantic controls.
Completeness, historical accuracy and reproducibility are separate goals.
A fully populated report may appear more complete while being less historically accurate. A preserved gap may be accurate but harder for downstream users to consume.
Published reporting adds another constraint. A correction introduced today may change what an old snapshot shows unless the model also preserves what was known at the original reporting time.
A correct fact table can still produce unreliable historical reporting.
Without dimension completion, snapshot facts may be technically correct but analytically unusable. Reports can lose attributes, exclude valid facts or change after rebuilds.
Dimension completion makes the missing coverage decision explicit. The model reconstructs the state, uses a controlled fallback, preserves the gap or rejects the output.
The goal is not to populate every field. The goal is to give every reporting period one deliberate, explainable and reproducible outcome.
Review a model for missing dimension coverage.
Describe the fact and dimension timelines. The assistant will help identify uncovered periods, evaluate completion strategies and make the assumptions behind the historical join explicit.
Open Historical Data Assistant