Inferring Late Dimension Members

Concepts covered: Late-Arriving Data

A new member creates a profile at 21:13 and presses play at 21:14. The playback event reaches the fact load that night. The profile row reaches dim_profile through a separate feed that runs hourly and had a 3-hour outage. The fact join finds no profile. A late dimension member is a fact that knows more than the dimension does, and the job has 3 choices at the moment of the join.

Choice	What the fact table holds	What the morning report shows	Repair later
Drop the row	Nothing	Undercounts plays, silently	Needs a reload from raw
Park it in a suspense table	Nothing until the profile arrives	Undercounts plays, visibly, with a count of parked rows	A retry job re-joins and loads
Insert an inferred member	The play, keyed to a placeholder profile	Correct totals, unknown attributes for a few rows	Overwrite the placeholder in place

Dropping is a defect. Suspense is the right choice when the attribute matters more than the count, for instance a billing fact that must never post against an unknown account. The inferred member is the right choice for a volume fact like playback, where hours watched are what the report sums and the profile's country is a slice of it. The job inserts a dimension row that says: this profile exists, we know its natural key, and nothing else yet.

INSERT INTO dim_profile
SELECT
  f.profile_id,
  'unknown' AS country,
  'unknown' AS plan_tier,
  true AS is_inferred,
  timestamp '1900-01-01 00:00:00' AS valid_from,
  timestamp '9999-12-31 00:00:00' AS valid_to
FROM (
  SELECT DISTINCT
    profile_id
  FROM stg_playback
) f
LEFT JOIN dim_profile d
  ON d.profile_id = f.profile_id
WHERE d.profile_id IS NULL

If the dimension carries a surrogate key column, the inferred row must receive the same key the real row would have received, which is the deterministic key argument of the final section.

The repair: overwrite, do not version

Here dim_profile is a slowly changing dimension of type 2: each profile has one row per version. valid_from and valid_to bound each version, and a fact joins to the version in effect at its event time. When the real profile record arrives 3 hours later, the tempting move is the standard one: close the inferred row and open a new version carrying the real country and plan. That's wrong. The inferred row recorded the pipeline's ignorance, and a new version would leave the first 3 hours of plays attached to 'unknown' forever. Overwrite the inferred row's attributes in place and flip is_inferred to false: the facts keyed to it resolve untouched, and its 1900 valid_from floor still catches a late play with any event time.

This is the one place in a type 2 dimension where an in-place update is correct, and interviewers probe it: 'doesn't that break your history?' The answer is that there was no history to break, only a placeholder. The first genuine change to the profile, a plan upgrade next month, opens a version in the normal way, and from then on the row behaves like every other member.

The harder case: the change itself arrives late

Profile 88213 upgraded from standard to premium on 1 October. The subscription system's export failed for 2 weeks, and the dimension feed delivers the change on 15 October, correctly stamped with the 1 October effective date. The loader does the right thing and opens a premium version with valid_from of 1 October. Now look at the facts. Every play from 1 to 15 October was loaded while the standard version was current, and if the fact stored a version surrogate key, 2 weeks of plays point at the standard row. The dimension is right and the fact is wrong, and the plays-by-plan report has misattributed premium hours for 14 days without a single failed job.

This case has 2 workable designs. The first stores only the natural key profile_id on the fact and resolves the version at query time with a range join on event time. Nothing needs re-keying, and the price is a join predicate on every query plus a dimension that must stay small enough to broadcast. The second stores the version key for speed and accepts a re-keying job. Whenever a dimension version arrives with an effective date in the past, that job rewrites every fact partition from the backdated valid_from forward, which in this case means 15 partitions and 150 GB.

SELECT
  d.plan_tier,
  count(*) AS plays
FROM fact_playback f
JOIN dim_profile d
  ON d.profile_id = f.profile_id
  AND f.event_ts >= d.valid_from
  AND f.event_ts < d.valid_to
WHERE f.event_date = date '2026-10-05'
GROUP BY d.plan_tier

About this lesson

This section is part of the Late-Arriving Data: Advanced lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.