Relational Integrity Test Suite¶
Data-quality test suite — instantiates Relation Constraint Enforcement
A maintained set of assertions run on a schedule over live data to detect relational violations that already exist — orphans, duplicate owners, forbidden pairings — and route them for correction.
A Relational Integrity Test Suite is a maintained collection of assertions that run on a schedule against live, accumulated data to find relational violations that have already leaked in. Its defining move is that it is detective and after-the-fact: it does not gate anything and it does not define the schema — it presupposes the schema and scans a real dataset for places where reality has drifted out of it. Each run counts the orphaned links, duplicate owners, forbidden pairings, and missing mandatory relations currently in the store, trends those counts against a baseline, and routes the offending rows to correction. It is a regression suite for relationships: cheap to run often, and honest about the fact that whatever it catches is damage that already happened.
Example¶
A data-platform team runs a suite nightly over the customer/account warehouse. Three of its assertions: every account must have exactly one owning_rep (a duplicate-owner test); every contact.account_id must match an existing account (an orphan test, the same relationship check that dbt's built-in relationships test performs); and no account may be simultaneously active and churned (a forbidden-pairing test).[n1] Last night's run flags 217 contacts pointing at accounts that a botched migration had deleted, plus 14 accounts that now show two owners after a territory reshuffle. The suite blocks nothing — the bad rows are already live — but it files the failing rows to the remediation queue and notes that the orphan count has climbed from 190 a week ago to 217, tripping the drift alert that says the upstream load is getting worse, not better.
That trend line is the payoff: it separates a one-off glitch from a systemic leak, and it tells the team the real fix is to move enforcement upstream, closer to where the orphans are born.
How it works¶
- Encode the assertions. Express each relation invariant — no orphans, one owner, no forbidden pairing — as a query that returns the rows violating it.
- Run on a schedule. Execute the whole suite periodically (nightly, hourly) over the current data, not on individual writes.
- Aggregate and trend. Report failing-row counts per assertion and compare against a baseline to distinguish noise from drift.
- Route to remediation. Hand the specific failing rows to a correction workflow, and alert when a count crosses a severity threshold.
Tuning parameters¶
- Run cadence — hourly, nightly, weekly. Fresher detection catches leaks sooner at higher compute cost.
- Assertion coverage — how many relation invariants are encoded. Wider coverage sees more classes of violation but takes more authoring and upkeep.
- Severity thresholds — the count or rate at which a rising trend pages someone versus merely logging.
- Sampling vs full scan — sample large tables for speed, or scan fully for completeness.
- Remediation routing — auto-file tickets, open a dashboard, or auto-correct low-risk classes — a trade of speed against oversight.
When it helps, and when it misleads¶
Its strength is that it catches what prevention missed: violations introduced by legacy loads, disabled constraints, upstream bugs, or manual edits that never passed a gate. The trend line turns a pile of bad rows into a diagnosis of whether the leak is closing or widening.
Its failure mode is intrinsic to being detective — by the time the suite fires, the invalid relations have already been trusted downstream, so it reduces dwell time but never prevents entry. An invariant nobody encoded is a whole class of corruption the suite cannot see, and a noisy assertion tends to get muted, hiding real drift behind alert fatigue. It embodies the practice of continuous relationship testing found in modern data-quality tooling.[n1] The guarding discipline is to read a rising failure count as a signal to push enforcement earlier rather than to scan harder, keep the assertion set curated, and pair every detection with an owned remediation path so findings are actually fixed.
How it implements the components¶
This suite fills the detective, after-the-fact slice of the archetype:
validation_rule— the authored assertions that turn each relation invariant into a testable query run procedurally across the data.monitoring_signal— the per-assertion counts, trends, and threshold alerts that reveal whether relational health is drifting.remediation_path— the routing of specific failing rows into a correction workflow for invalid relations already present.
It does not declare the relation_schema, compatibility_rule, or directionality_rule its assertions presuppose — those structural declarations belong to Graph Schema Validation, its nearest twin (and to Foreign-Key Constraint). Graph Schema Validation judges a structure's conformance to a declared schema; this suite is a scheduled detective scan over live, accumulated data that finds and routes violations already present.
Related¶
- Instantiates: Relation Constraint Enforcement — it detects and trends invalid relations that slipped past prevention, feeding correction.
- Sibling mechanisms: Authorization Relationship Check · Foreign-Key Constraint · Graph Schema Validation · Dependency Constraint Check · Policy Relation Rule · Workflow Transition Guard · Role Compatibility Check
Editorial Notes¶
Form Classification¶
Form family: Monitoring, Sensing & Alerting
Rationale: Relational Integrity Test Suite operates as ongoing observation, sensing, or alerting that detects and surfaces state without itself executing the response because it a maintained set of assertions run on a schedule over live data to detect relational violations that already exist — orphans, duplicate owners, forbidden pairings — and route them for correction.
Independent corroboration: The frozen evidence defines Relational Integrity Test Suite as 'A maintained set of assertions run on a schedule over live data to detect relational violations that already exist — orphans, duplicate owners, forbidden pairings — and route them for correction', so its operative form is Monitoring, Sensing & Alerting.
Nearest alternative: Assessment, Review & Assurance — Relational Integrity Test Suite includes features of a bounded evaluation of existing evidence or work that produces a finding or disposition, but its defining operation is ongoing observation, sensing, or alerting that detects and surfaces state without itself executing the response.
Review outcome: Independent reviewer agreement; medium confidence.
Origin Attribution¶
Primary origin: Computer Science & Software Engineering
Origin pattern: Single lineage
Present-day reach: Multi-domain
Rationale: Scheduled assertions for referential and relational violations are a database and software-testing mechanism.
Review resolution: Both blind reviewers agree that computer_science is the primary historical origin. Explicit reconciliation of alternate origin disagreement, encyclopedia synthesis disagreement adopts reviewer_a's evidence: Scheduled assertions for referential and relational violations are a database and software-testing mechanism. The selected record uses alternates=none, origin_mode=single_lineage, and domain_reach=multi_domain; the other review proposed alternates=engineering_design, origin_mode=single_lineage, and domain_reach=multi_domain. The selected combination better preserves the mechanism-specific formative lineages and calibrated scope; broader present-day use is not treated as proof of additional historical origin.
Review outcome: Reconciled after independent review; high confidence.
Notes¶
[n1] dbt tests — the data-build-tool convention of declaring data-quality tests alongside models, including a built-in relationships test that asserts every value in one column exists in a referenced column (an orphan check). It is a widely used named form of scheduled relational testing over accumulated warehouse data. ↩a ↩b