Relational subsetting without orphan rows

Use case

A full copy of a production database is rarely what a test needs. It is slow to move, expensive to store and it spreads far more personal data than any test case touches. The obvious shortcut — take ten percent of every table — produces a dataset that looks small and healthy until the first query joins two tables and half the rows have nothing on the other side. This page shows the difference with numbers you can reproduce.

The scenario

A regression suite for claims adjudication needs a handful of members with their complete history: coverages, claims, the lines on each claim, the diagnoses and the procedure codes. Every claim in the subset must point at a member in the subset, every line at a claim, every procedure at a line. A dataset with dangling references either fails to load (when the target enforces foreign keys) or, worse, loads and makes tests pass or fail for reasons that have nothing to do with the code under test.

The reliable technique starts from driver entities — here, members — and follows every relationship outwards and back in until the set is closed. The example runs both approaches on the synthetic healthcare bundle.

Run it

Python 3.10 or later, standard library only.

import csv
import io
import os
import random
import urllib.request

BASE = os.environ.get("DATANIVRA_DOWNLOADS", "https://www.datanivra.com/downloads")
BUNDLE = f"{BASE}/packs/healthcare/2.1.0/synthetic"


def rows(name):
    with urllib.request.urlopen(f"{BUNDLE}/{name}") as response:
        return list(csv.DictReader(io.TextIOWrapper(response, encoding="utf-8")))


members = rows("enrollment.members.csv")
coverages = rows("enrollment.coverages.csv")
claims = rows("claims.claims.csv")
lines = rows("claims.claim_lines.csv")
diagnoses = rows("claims.diagnoses.csv")
procedures = rows("claims.procedures.csv")

# 1. Naive sampling: keep every claim line with probability 0.5, ignore relationships.
random.seed(7)
sampled_lines = [line for line in lines if random.random() < 0.5]
kept = {line["claim_line_id"] for line in sampled_lines}
orphans = sum(1 for p in procedures if p["claim_line_id"] not in kept)
print(f"naive 50% sample: {len(sampled_lines)} claim lines, {orphans} procedures lose their line")

# 2. Entity-driven subset: start from two members, follow every foreign key.
drivers = {m["member_id"] for m in sorted(members, key=lambda m: m["member_id"])[:2]}
sub_coverages = [c for c in coverages if c["member_id"] in drivers]
sub_claims = [c for c in claims if c["member_ref"] in drivers]
claim_ids = {c["claim_id"] for c in sub_claims}
sub_lines = [line for line in lines if line["claim_id"] in claim_ids]
line_ids = {line["claim_line_id"] for line in sub_lines}
sub_diagnoses = [d for d in diagnoses if d["claim_id"] in claim_ids]
sub_procedures = [p for p in procedures if p["claim_line_id"] in line_ids]
for table, subset, full in [
    ("members", drivers, members),
    ("coverages", sub_coverages, coverages),
    ("claims", sub_claims, claims),
    ("claim_lines", sub_lines, lines),
    ("diagnoses", sub_diagnoses, diagnoses),
    ("procedures", sub_procedures, procedures),
]:
    print(f"{table:12} {len(subset):3} of {len(full)}")
dangling = (
    sum(1 for c in sub_claims if c["member_ref"] not in drivers)
    + sum(1 for line in sub_lines if line["claim_id"] not in claim_ids)
    + sum(1 for p in sub_procedures if p["claim_line_id"] not in line_ids)
)
print(f"dangling references in the subset: {dangling}")

Expected output

naive 50% sample: 13 claim lines, 7 procedures lose their line
members        2 of 5
coverages      2 of 5
claims         5 of 11
claim_lines   11 of 20
diagnoses     12 of 21
procedures    11 of 20
dangling references in the subset: 0

The naive sample keeps roughly half the lines and silently strands seven procedure rows. The entity-driven subset is smaller in members but complete: two members bring exactly the claims, lines, diagnoses and procedures that belong to them, and nothing points outside the set.

Schema

The entity-relationship diagram in the bundle (er-diagram.svg) shows the same graph for the whole pack.

What DataNivra does with your own data

On a real source the agent builds this graph from the declared keys it discovers plus any relationships you map explicitly, picks driver rows with the filters in your subset policy, and closes the set across tables — including across systems when a shared business key links them. Validation counts orphan references before certification, and a dataset with broken references is not certified. Rows stay inside your network; the control plane sees table names, row counts and gate outcomes. Certification here means DataNivra-certified against configured policy gates; it is not a regulatory or third-party certification and does not make a system compliant with any regulation.

Limits to plan around

• Relationships that exist only in application code, not as declared keys, must be mapped explicitly; the engine cannot infer every implicit link.
• Closing a set over a densely connected graph (a shared product catalogue, a reference table used by every row) can pull in more than you expect; subset policies let you treat such tables as copied in full or excluded.
• This page is a teaching example on synthetic files, not the production engine, and its two-member subset is far smaller than a real regression set.

Next steps

Read the data subsetting tutorial and referential integrity, then estimate your own subset with the subset size calculator.