Audit Join Cardinality Before Merging Research Tables

State the intended relationship

Before merging tables, describe one row in each table. A participant table may contain one row per person, while a visit table contains one row per person per visit. Decide which columns uniquely identify a record and whether the intended join is one-to-one, one-to-many, or many-to-one.

The official dplyr join documentation explains relationship checks and warns that unexpected many-to-many relationships can multiply rows. This is not just a software detail: duplicated analysis rows can distort denominators and the influence of individual participants.

Inspect keys before values

Count records and distinct keys in both inputs. Identify missing keys, repeated keys, and formatting differences such as leading zeros or accidental spaces. A patient identifier stored as a number may lose meaningful leading zeros; preserve identifiers according to the data dictionary.

Use the data-join audit worksheet to record the intended key, expected relationship, join type, and unmatched-record policy. If the relevant record is a visit, participant identifier alone is usually insufficient to distinguish it. Include the appropriate visit or specimen identifier rather than removing duplicates without explanation.

Predict a fictional join

Imagine the left table contains two rows with key A and one row with key B. The right table contains three rows with A and one with C. An equality inner join produces six A rows because each of the two left A rows matches all three right A rows. B and C do not appear in that inner join.

The join-cardinality checker helps inspect this pattern using synthetic keys. These invented records demonstrate multiplication, not a recommended data structure. Do not paste identifiable research data into a demonstration tool; use harmless labels when exploring the behavior.

Verify the joined result

After joining in the approved analysis environment, compare row counts, distinct participant counts, and key multiplicities with the prediction. List unmatched keys from each input. An inner join can silently remove participants, while a left join can retain them with missing values; neither choice is automatically correct.

Check how the software handles missing keys, since matching rules can differ. Set relationship assertions where supported, and deliberately test a small example containing duplicates and unmatched records before processing the full dataset.

Save the input versions, join specification, and exception list with the output. Resolve conflicting duplicate records using source information and a documented rule. A final table with the expected number of rows can still contain incorrect matches, so inspect a sample of linked records as well.

Source checked on 31 October 2026; fictional join independently enumerated.