referential check reports orphans that are valid late arriving facts error

Not specified by the original author.

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.

Not specified by the original author.

TL;DR

Fix this in synthetic data generator by acting on the exact message first: filter child rows to keys that exist in the generated parent output, regenerate or drop the orphans the check names, and add the same join check inside the pipeline so orphans are caught before the validation suite runs. Do not rerun the whole job unchanged, because the same inputs produce the same failure. Prove the fix on one table, batch or record set first, then rerun the full scope and reconcile the counts.

referential integrity check failed - orphan rows

Use this when

• The operation in synthetic data generator fails with this exact message or symptom: referential integrity check failed - orphan rows.
• The same failure repeats on retry with unchanged inputs.
• Generation, validation or export is blocked until this item completes.

Not for this skill when

• The message is an outage notice from the platform. Resolve that first, then return here if this error remains.
• The operation completes and you only disagree with its output quality. That is a quality review, not this failure.

Steps

1. Reproduce the failure with the smallest scope that still shows it in synthetic data generator, and copy the full message plus the table, column or job involved into the run notes. Verify: the error is reproduced on demand with a named scope, not only seen in a log.
2. Apply the specific fix for this error: filter child rows to keys that exist in the generated parent output, regenerate or drop the orphans the check names, and add the same join check inside the pipeline so orphans are caught before the validation suite runs. Verify: the smallest failing scope now completes, or the named column, table or job behaves as the fix predicts.
3. Check the neighbouring tables, columns or jobs that share the same config, schema or credential, because this class of failure rarely affects only one item. Verify: the neighbouring items are listed and each is marked affected or clear.
4. Rerun the original operation in synthetic data generator on the previously failing scope, watching for the same message rather than assuming success from a quiet log. Verify: the operation completes and the original message does not appear.
5. Compare the result counts, distributions or status against the source in synthetic data generator so a silent partial success is caught before sign off. Verify: counts match the source or every difference is named and explained.
6. Record the fix, the triggering input and the verification result on the item so the next occurrence is recognised immediately. Verify: the record names the error, the fix applied and the evidence of success.

Why it happens

Child rows generated or filtered separately from parents end up pointing at keys the parent output no longer contains, which the integrity check reports as orphans.

Variant phrasings

referential integrity check failed - orphan rows

The verbatim form searchers paste from the terminal or console. Treat it identically: same fix, same single scope proof, then the full rerun.

How to fix - how to fix synthetic data fails referential integrity check error orphan rows

The how-to phrasing for the same failure in synthetic data generator. The steps above apply unchanged; start at the reproduction step rather than skipping to the rerun.

synthetic data generator troubleshooting for this failure

The symptom-first phrasing used when the console hides the full message. Surface the full message first, confirm it matches the code block above, then follow the steps.

Compatibility: synthetic data generator at current versions, plus the platform API or console the query names; no extra tooling beyond the source system access needed to verify counts.

Provenance

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

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

Thread query: how to fix synthetic data fails referential integrity check error orphan rows

TL;DR: Fix this in synthetic data generator by acting on the exact message first: filter child rows to keys that exist in the generated parent output, regenerate or drop the orphans the check names, and add the same join check inside the pipeline so orphans are caught before the validation suite runs. Do not rerun the whole job unchanged, because the same inputs produce the same failure. Prove the fix on one table, batch or record set first, then rerun the full scope and reconcile the counts.

Not specified by the original author.

TL;DR

Child rows without parents break joins and drop revenue or activity silently: enumerate the orphan keys, classify them as late parents, filtered parents, or genuinely missing, and reconcile at the load layer. The check should end with zero unexplained orphans, even if a small documented quarantine remains.

referential integrity failed error: child rows without parent failed check

Use this when

• The check or run fails with this symptom: the referential integrity check lists child rows whose parent keys do not exist.
• You are working on a referential check failing on child rows with no parent 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

• An orders-specific foreign key failure with business context on the parent table, which the orders-focused skill covers in depth instead.
• The error text names a different tool, stage, or root cause. Match the verbatim error first and use that skill.

Steps

1. Run the check query and save the orphan keys with counts per child table. Verify: orphan keys are saved per table
2. Look the keys up in source systems and parent load history to classify each group. Verify: each orphan group has a classification
3. Check the parent load for filters, late windows, or key transformations that differ from the child's. Verify: the parent-side cause is confirmed or ruled out
4. Backfill or correct the parent load, resequence dependent loads, or quarantine genuinely missing references with owner sign-off. Verify: the reconciliation action is applied per group
5. Rerun the referential check and confirm no unexplained orphans remain. Verify: the check passes with only documented quarantine, if any

Variant phrasings

referential integrity failed child rows without parent

The verbatim child-without-parent phrasing.

child rows missing parent referential check fix

The fixing phrasing for load-order and filter causes.

orphan keys referential integrity warehouse check

The orphan-key phrasing for classification workflows.

Compatibility: Referential integrity checks in Soda, dbt relationships tests, and warehouse SQL checks

Why it happens

Referential integrity spans two independently loaded tables, so timing, filtering, and key formatting differences between the loads manufacture orphans. The check compares snapshots; the loads' history explains whether the relationship or the scheduling is broken.

Edge cases / pitfalls

• Normalise keys identically on both sides before comparing. Formatting orphans waste more time than real ones.
• Gate child loads on parent load completion in the orchestrator to remove the timing class entirely.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_4-QBYuMldY6zmkJFtNBRoQ

Resolved from the public thread: https://vectle.com/posts/pst_4-QBYuMldY6zmkJFtNBRoQ

Thread query: referential integrity failed error: child rows without parent failed check

TL;DR: Child rows without parents break joins and drop revenue or activity silently: enumerate the orphan keys, classify them as late parents, filtered parents, or genuinely missing, and reconcile at the load layer. The check should end with zero unexplained orphans, even if a small documented quarantine remains.