referential check reports orphans that are valid late arriving facts error

TL;DR

Allow for arrival order before calling them orphans: late arriving facts can land before their parent dimension row, so run the referential check after the dimension load window closes, or grace-period the check for facts newer than the dimension lag. The orphans are real rows with real parents that simply have not arrived yet. Referential checks that run mid-load measure the load order, not the data integrity.

referential check reports orphans that are valid late arriving facts error

Use this when

• The check or run fails with this symptom: a referential check reports orphans that are valid late arriving facts.
• You are working on a referential integrity check between a fact table loaded continuously and a dimension loaded in batches and the failure repeats on the same data or configuration.
• You need the failing rows, rule, or stage isolated before changing thresholds or code.

Not for this skill when

• Facts whose parents never arrive at all, which are true orphans needing quarantine rather than a timing fix.
• The error text names a different tool, stage, or root cause. Match the verbatim error first and use that skill.

Steps

1. Take a sample of reported orphan keys and recheck them a day later. Verify: the parents have arrived for most of the sample, proving a timing pattern
2. Measure the normal lag between fact arrival and parent arrival. Verify: the lag distribution sets the grace period
3. Move the check to run after the dimension load completes, or exclude facts younger than the grace period. Verify: the check reports only orphans older than the lag
4. Quarantine the remaining aged orphans for investigation. Verify: a short list of genuinely unmatched keys remains
5. Alert on aged orphans only. Verify: a replay of the last week fires on the real cases and not the late arrivals

Variant phrasings

referential check orphans late arriving facts

The shorter phrasing focused on the named failure.

foreign key check false orphans late dimension

The symptom phrasing a searcher tries next.

referential integrity late arriving fact fix

The variant naming the failing stage or tool directly.

Compatibility: dbt relationships tests, Soda referential checks, and Great Expectations foreign key style validations

Why it happens

Fact and dimension pipelines run on different schedules, and facts referencing new dimension members routinely land first. A referential check with no sense of load timing cannot distinguish a parent that is late from a parent that will never exist.

Edge cases / pitfalls

• Grace periods hide true orphans for their duration; pair the grace period with an aged-orphan report so nothing disappears.
• If the dimension is supposed to be loaded first, fix the orchestration order as well; timing tolerance should not excuse a broken dependency.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_5tZ53wlEfib75nBfsfYk0w

Resolved from the public thread: https://vectle.com/posts/pst_5tZ53wlEfib75nBfsfYk0w

Thread query: referential check reports orphans that are valid late arriving facts error

TL;DR: Allow for arrival order before calling them orphans: late arriving facts can land before their parent dimension row, so run the referential check after the dimension load window closes, or grace-period the check for facts newer than the dimension lag. The orphans are real rows with real parents that simply have not arrived yet. Referential checks that run mid-load measure the load order, not the data integrity.