Relational export key and cardinality checks: verify joins before review

Write one sentence for every table: “One row represents ...” Examples include one order, one order line, one account snapshot, or one event. If the sentence needs “usually,” “sometimes,” or “depending on the file,” the grain is not ready for a join decision. A readable id column is a candidate identifier until a source contract or a check supports more.

The PostgreSQL Global Development Group, 22 Oct 2026 (opens in a new tab) documentation is a useful model for naming key and foreign-key concepts. An exported CSV, JSON file, or spreadsheet may not carry declarations, so do not infer a database constraint from a field name alone. Sources checked on 22 October 2026. Dates in the Sources list are retrieval dates, not claimed publication dates.

Test three separate conditions for every candidate key: the field exists, required values are present, and the key is unique at the declared grain. A composite key must be tested as a tuple; checking each component independently can miss collisions. Decide whether a repeated key is an error, version, or second grain.

Synthetic key and orphan checks

-- Synthetic fixture only. No production records are used.
SELECT account_id, month, COUNT(*) AS rows_at_grain
FROM account_month
GROUP BY account_id, month
HAVING COUNT(*) <> 1;

SELECT e.account_id
FROM events AS e
LEFT JOIN accounts AS a ON a.account_id = e.account_id
WHERE a.account_id IS NULL;

The cited database documentation notes that default unique constraints do not treat nulls as equal PostgreSQL Global Development Group, 22 Oct 2026 (opens in a new tab). An export needs a declared choice: reject unknown-key rows, hold them, or allow them as a known state. Keep null, blank, sentinel, not applicable, and unavailable distinct when the source distinguishes them.

Cardinality describes how rows relate, not whether a join produces a preferred count. If accounts has one row per account and events may contain many rows per account, the expected relationship is one-to-many. Two event rows for one account are not automatically a defect. Two account rows are a defect only if the account table promised uniqueness.

An unmatched child key may mean that the parent was excluded by scope, deleted before the export, renamed, delayed, or never existed. Preserve that distinction in the disposition. A count mismatch is a reconciliation signal, not proof of loss.

Record both sides of a reconciliation

1. 01Count rows at each table’s declared grain.
2. 02Count distinct candidate keys and the number of key violations.
3. 03Count matched and unmatched relationship keys.
4. 04Compare the result with declared scope, date window, and exclusions.
5. 05Explain each difference as included scope, legitimate repeat, source defect, transformation, or unresolved.

Before comparing joined row counts, decide whether the export contains snapshots, events, corrections, or versions. Add a version, effective-time, or event identifier to the review contract when it exists. Do not drop repeated rows just to make counts align. The exact and near-duplicate review owns content identity, while the schema and data dictionary owns field meaning and row grain.

The data provenance log owns source and transformation history. This guide owns the relationship check between already-described tables; it does not replace those records.

Start with headers, schema declarations, aggregate counts, key profiles, and synthetic fixtures. If a real sample is necessary, use an owner-approved, redacted artifact with a documented audience, retention period, and deletion path. A key can be a direct identifier, and a combination of quasi-identifiers can still reveal a person or organization. Structural join checks are not de-identification, privacy clearance, rights evidence, or accuracy validation.

• Use a neutral artifact ID instead of pasting a private row into a public worksheet.
• Treat a structural schema as separate from a de-identification decision.
• Do not treat a foreign-key match as proof of ownership or authority.
• Keep rights, privacy, retention, and contractual questions separate from the join result.

After the owner has documented the collection, scope, exclusions, and handling boundary, the appraisal route can be a qualified preparation reference for an eligible, supported non-health collection. It is a request for review, not automatic acceptance, a quote, a buyer match, a legal opinion, or a license. Do not upload sensitive raw records just to demonstrate that two tables join.

General information, not legal advice.

Takeaways

• Define row grain before choosing a key or joining tables.
• Test composite uniqueness, null treatment, unmatched keys, and expected cardinality separately.
• Preserve legitimate versions and events; do not force counts to align by deleting repeats.
• Use synthetic or metadata-only artifacts first, then escalate only with documented authority.