Relationship validation checks whether linked records exist and connect under the intended lifecycle and scope. Define parent keys, child references and expected cardinality, then classify unmatched records. Investigate arrival delays and extraction boundaries before deleting or changing valid business events.
Describe the relationship and its expected shape
Field validation can show that a customer reference looks correct without proving that the customer exists in the supplied population. Identify the parent record, the child reference and the namespace of each key. State whether one child may have one parent, several parents or no parent in a legitimate circumstance. A household relationship and an invoice ownership relationship need different assumptions about multiplicity.
Set the time basis as well. An invoice may need the customer version effective when it was issued, rather than the customer's current address or account grouping. If the source stores history, define how effective intervals are matched. Overlapping parent versions can multiply child rows during a join and inflate totals without creating any visibly malformed field. Test join counts alongside unmatched references.
Classify unmatched references before choosing an action
An unmatched child can reflect a typing error, delayed parent delivery, a deliberately restricted export or a deleted parent record. Those explanations lead to different remedies. Compare source coverage dates and export filters before requesting a correction. An inactive customer may be excluded from today's customer file while historical invoices remain valid. That is a scope problem for the extract, not proof the invoices should disappear.
Produce an exception table containing the child identifier, referenced key, source, event date, arrival date and investigation status. Keep monetary value or another decision-relevant size where permitted so a small number of exceptions does not hide a large exposure. Assign an owner and a next review time. Do not replace missing references with a shared placeholder unless its meaning and downstream counting consequences are explicitly approved.
Reconcile both missing and multiplied relationships
Count children before joining, matched children after joining and resulting output rows. A successful match can still be wrong if duplicated parents create several outputs for one invoice. Where a many-to-many relationship is intentional, define allocation weights or report separate relationship counts. Avoid using distinct totals as a blanket repair because equal amounts from different events can then disappear together.
Retain unresolved children in a controlled exception population and qualify any affected metric. Compare exception counts and values over successive arrivals to distinguish a transient delay from a persistent fault. The further reading supplies data-quality context, while the following example is an invented reconciliation. Resolving a reference establishes a particular connection under the rule; it does not by itself verify the commercial accuracy of the invoice.
Test deletion behaviour explicitly. Removing an inactive parent from a current operational screen need not remove its historical existence from analytical extracts. A documented archived-parent population can preserve valid links while keeping current-account reporting focused on the intended active scope.
Fictional invoice feed: a customer arrives tomorrow
A fictional scenario to explain the method, not a customer result or a market benchmark.
A fictional invoice export contains 50 invoices worth 12,000 units. Two invoices worth 1,400 refer to customer C-77, absent from the accompanying customer export. The analyst reports 48 matched invoices and two unresolved invoices, preserving all 50 in the import. A matched-only total of 10,600 would understate recorded activity if shown as complete.
The source owner confirms that the customer feed stopped an hour before the invoice feed. C-77 appears in the next delivery with an effective date preceding both invoices. Re-running the relationship check matches all 50 invoices without changing their amounts or dates.
A separate duplicate customer version would have produced 52 joined rows, so the reviewer also verifies output cardinality. This fictional case demonstrates why parent arrival and join multiplication need different checks. It does not establish that every unmatched reference is harmless or that the invoices were independently authenticated.
TAKE THIS INTO YOUR NEXT CONVERSATION
A practical next-step checklist.
- Specify keys, namespaces and expected cardinality.
- Compare source scope and arrival times.
- Track unmatched counts and decision-relevant values.
- Check that joins do not multiply child events.
Common questions.
Should unmatched invoices be discarded?
Preserve them for investigation unless an authorised rule establishes they are invalid. A delayed customer feed can explain a valid invoice.
Does a zero unmatched count prove the join is correct?
No. Duplicate or wrongly mapped parents can match every child while producing incorrect relationships or multiplied totals.
Further reading & source context.
These primary references provide background. They are not evidence for the fictional example above and do not endorse Accuralytix.
Found an error or a missing qualification? Send an editorial correction.
Check my data readiness.
Use a practical tool to turn this idea into a clearer starting point.
Check my data readiness