On this page
Write the parent-child contract
Name the parent table or artifact, the parent key, the child reference, and what “must exist” means at the declared snapshot. A child may reference a parent that is intentionally outside the export, a parent deleted later, or a parent in a different partition. These states are different from a malformed key. Record the expected scope before counting or sampling.
The PostgreSQL, (opens in a new tab) documentation places foreign keys among table constraints, but an exported file may omit the database declaration or the parent table. Treat a constraint definition as evidence of an intended rule and a sampled reference check as evidence about the packet. Do not infer a database constraint from matching column names.
| Contract field | Record | Unknown if |
|---|---|---|
| parent identity | artifact, table, and version | Parent scope is assumed |
| parent key | field path and type | Type is inferred from a name |
| child reference | field path and null rule | Null means are undocumented |
| existence rule | required, optional, or conditional | Missing parent disposition is absent |
| snapshot | capture id and boundary | Parent and child were exported separately |
| exclusions | filters, partitions, and deletions | Export scope is incomplete |
Classify missing parents instead of hiding them
An orphan is an observed child reference with no matching parent in the review scope. It may be a true broken reference, a parent omitted by a filter, a delayed parent export, a deletion or tombstone, a cross-snapshot mismatch, or an invalid key. Keep the raw reference out of a public worksheet, but preserve a stable synthetic or redacted identifier and the reason category.
Link to the key and cardinality guide for uniqueness and duplicate-key evidence. This guide adds scope and snapshot reasoning: a unique child reference is not useful if the parent packet is from another release. A pass should therefore state the sampled population and exclusions.
| Observed state | Classify as | Next evidence |
|---|---|---|
| parent found in same snapshot | matched | retain pair and snapshot id |
| parent absent, excluded partition | out of scope | record exclusion rule |
| parent absent, deletion marker present | tombstoned or deleted | link deletion evidence |
| parent absent, no explanation | orphan unresolved | owner or source investigation |
| child reference null | optional or missing | apply documented null rule |
| duplicate parent key | parent identity ambiguous | hold dependent checks |
Design a bounded sample that can be audited
A useful sample states how parent and child records were selected without exposing their values. Choose a deterministic slice or a synthetic fixture, record the sample frame and exclusions, and retain counts for matched, missing, null, duplicate, and out-of-scope references. Do not describe a sample as representative unless the selection method supports that statement. An unavailable population size should remain unavailable.
Use paired samples when parent and child files have separate capture dates. Compare capture ids, schema versions, and declared filters before comparing references. A percentage computed from an unknown denominator is not evidence. Report counts only for the bounded sample you actually inspected.
Sample fields
- sample id and selection rule, without raw identifiers
- parent and child artifact ids and capture boundaries
- expected reference rule and null policy
- counts for matched, orphan, duplicate, null, and excluded states
- unavailable population or coverage fields left explicit
- reviewer decision and next evidence request
Use synthetic identifiers for the worksheet
Exercise the contract with invented keys that show every state. The fixture below includes a valid pair, a missing parent, an excluded parent, a duplicate parent, and a nullable child. It is a text ledger, not a database query. The point is to make the disposition visible while ensuring that no private row, customer name, or production identifier appears in the public packet.
If a real sample is necessary, use an owner-approved and redacted artifact with a documented audience and deletion path. Keep the raw reference in the controlled system and place only a stable evidence id in the worksheet. Sampling does not grant permission to redistribute the source data.
| Fixture finding | Disposition | Next evidence |
|---|---|---|
| C1 points to P1 in same snapshot | matched | Retain pair and sample id |
| C2 points to excluded P9 | out of scope | Confirm partition boundary |
| C3 has null reference | conditional | Apply nullable rule |
| C4 points to P2 with duplicate parent key | hold | Resolve parent identity before sampling |
| Duplicate parent key | hold | Resolve parent identity before sampling |
Synthetic reference sample
parents: P1, P2, P2
children: C1->P1; C2->P9; C3->(null); C4->P2
scope: P9 excluded from this snapshot
expected: matched=1, null=1, out_of_scope=1, ambiguous_parent=1, unresolved_orphan=0
identity check: parent key P2 intentionally duplicates in fixtureSeparate integrity evidence from quality claims
Report “no unresolved references in this bounded sample” rather than “the dataset has referential integrity.” Include the sample rule, source scope, snapshot alignment, and unknowns. An export can pass the sample while missing an entire partition, and an orphan can be explained by a documented deletion workflow. Keep the observation narrow enough to reproduce.
Use the data provenance log for capture and deletion history and the data card for intended scope. Referential integrity says something about relationships, not about semantic accuracy, privacy, rights, value, or demand. Keep those decisions separate.
| Finding | Disposition | Do not infer |
|---|---|---|
| Bounded sample has no unresolved references | sample pass | Every row is linked |
| Orphans map to excluded partition | conditional pass | The parent is missing |
| Parent and child snapshots differ | hold | The references are broken |
| Duplicate parent keys observed | hold | The child chooses one parent |
| Population or coverage unavailable | report limitation | The sample is representative |
Close with a qualified preparation step
After the relationship contract, sample frame, snapshot alignment, and orphan categories are documented, keep the packet at structural evidence level. Do not upload source tables to demonstrate a reference check. A redacted manifest and synthetic keys are enough to test the review contract.
For a supported non-health collection, with authority to describe the linked artifacts and a controlled handling boundary, the qualified appraisal path may be a next preparation step. It does not sample rows, repair links, create a quote, match a buyer, issue a legal opinion, or grant a license. A clean sample is not a permission or accuracy decision.
Takeaways
- Define the parent-child rule and snapshot before sampling.
- Keep orphan, null, excluded, deleted, and duplicate states distinct.
- Use a bounded sample with an explicit denominator and coverage note.
- Exercise the worksheet with synthetic keys before touching real rows.
- Report sample evidence narrowly and keep rights and privacy separate.