Why Record-Level Reconciliation Matters
A population PK analysis dataset is assembled from records created for different purposes. A laboratory extract may contain every reported assay result, a dosing extract may include planned as well as administered doses, and a clinical extract may store measurements by visit. The analysis dataset selects and structures records for a particular analysis. A row that is absent from the analysis dataset is not necessarily an error, and a row that appears in both datasets is not necessarily the same event. Reconciliation establishes, record by record, which source records were carried forward, which were transformed, and why any records were not used.
The essential comparison is not simply “Do the files have the same number of rows?” It is “Can each relevant analysis record be traced to the appropriate source record, and can each source record’s disposition be explained?” This matters for concentration observations, dose events, and clinical covariates because mismatches can change the observations or covariate histories available for modeling.
Define What Counts as the Same Record
Before matching, define the record grain: what one row represents in each file. A laboratory row might represent one assay result for one specimen; a dosing row might represent one administered dose event; a clinical row might represent one measurement at one visit. The analysis file may use the same grain, or it may have been filtered, reshaped, or derived. If row meanings differ, a direct row-for-row comparison is not valid.
Then specify a matching key, the field or combination of fields used to identify corresponding records. Subject ID alone is rarely enough: a subject may have many samples and dose events. A laboratory key might combine subject ID, specimen or sample ID, and analyte. A dosing key might combine subject ID, dose-event ID, and event date-time. A clinical key might combine subject ID, visit, measurement name, and measurement date. Use stable source identifiers where available. Do not rely on row order, and do not treat a rounded time or value as a unique identifier unless the data specification explicitly supports it.
For each comparison, classify records into four basic groups: matched records; source-only records, which have no analysis counterpart; analysis-only records, which have no identified source counterpart; and records whose keys are duplicated or ambiguous. A match establishes an identifier-level link, not automatic agreement of all values. Compare relevant fields after matching, including date-time, units, dose amount, concentration status, and covariate value. The expected comparison depends on the record type and documented transformations.
A Reconciliation Strategy
A defensible process has three parts. First, verify the files being compared: study, extract date or version, subject-ID format, variable definitions, and expected record grain. Second, match records using the specified key and inspect both unmatched records and matched-field differences. Third, assign a disposition and record its evidence. A disposition might be “included unchanged,” “included after documented correction,” “excluded per analysis rule,” “duplicate confirmed,” or “unresolved—hold from analysis.” Do not silently remove, invent, or overwrite records to make the files agree.
The logic of the count check can be written for a one-to-one comparison as follows. Let \(S\) be the set of source keys and \(A\) the set of analysis keys. The matched keys are \(S \cap A\); source-only keys are \(S \setminus A\); and analysis-only keys are \(A \setminus S\). If keys are unique, the number of source records must equal the matched count plus the source-only count, and the number of analysis records must equal the matched count plus the analysis-only count.
These identities are a check on accounting, not proof that the reconciliation is correct. Two files can have equal row counts while containing different records. They can also have the same keys while disagreeing on values. In a one-to-many join, such as a subject-level clinical record linked to multiple sample records, the unique-key identities must be applied at the appropriate grain rather than to the expanded joined table.
Worked Example: Reconciling Laboratory Records
Suppose a hypothetical analysis uses a laboratory extract with five records and contains five analysis records. The intended grain is one row per specimen result, and the unique key is subject ID plus specimen ID. The records are:
| Key | Laboratory extract | Analysis dataset | Initial finding |
|---|---|---|---|
| P01-L11 | Present, 8.2 ng/mL | Present, 8.2 ng/mL | Matched, values agree |
| P01-L12 | Present, 3.1 ng/mL | Present, 3.1 ng/mL | Matched, values agree |
| P02-L21 | Present, 10.0 ng/mL | Present, 10.0 ng/mL | Matched, values agree |
| P03-L31 | Present, below quantification limit | Present, coded below quantification limit | Matched, status agrees |
| P04-L41 | Present, 7.0 ng/mL | Absent | Source-only |
| P02-L22 | Absent | Present, 6.0 ng/mL | Analysis-only |
The unique matched set has four keys. Therefore, \(|S|=5=4+1\), where four is the matched count and one is the source-only count; likewise, \(|A|=5=4+1\), with one analysis-only record. The equal totals of five did not show that the files agreed: the identities of the records differed.
The hypothetical review finds that specimen P04-L41 was collected after a visit was canceled in the study system, but the assay extract contains a result. The analysis rule excludes results from canceled visits, and the cancellation is confirmed in the visit-status source. The disposition is therefore “excluded per canceled-visit rule,” with that evidence recorded against P04-L41. The analysis-only key P02-L22 is traced to a transcription error: the analysis row was assigned a specimen ID not present in the laboratory extract. The source system confirms that the result belongs to P02-L21, which is already represented. The duplicate analysis row is removed under the documented duplicate-handling rule, and the original key and correction are retained in the audit trail. The records are not relabeled merely to achieve a match; the source evidence supports the correction.
Check Dose and Clinical Records at Their Own Grain
Dosing and clinical extracts require attention to what constitutes an event. A planned dose is not necessarily an administered dose. An administration may be represented by an administration ID in the source but by a derived event record in the analysis file. A clinical value may be recorded once per visit and appropriately associated with multiple PK observations. In those cases, a one-to-one row join is not the right test. Document the relationship, then check that each record is linked as intended and that no unexpected event is lost or multiplied.
Worked Example: Reconciling Administered Doses
In a hypothetical study, the dosing extract has six unique administration-event IDs. Five appear in the analysis dataset. The remaining source event, D406 for subject P06, is labeled “planned” in the source but has no administration confirmation. The analysis specification includes administered doses only. A separate check finds that the analysis row D403 lists 100 mg, while the source administration record lists 80 mg.
The key is subject ID plus administration-event ID, and the records are unique at that grain. Five keys match, one is source-only, and there are no analysis-only keys. The count check is \(6=5+1\) for the source and \(5=5+0\) for the analysis. The source-only D406 is documented as excluded because it was planned but not confirmed as administered; it is not silently discarded. For D403, the key matches but the dose amount does not. Review of the administration record confirms 80 mg, and the analysis amount is corrected from 100 mg to 80 mg while preserving the original value, corrected value, reason, evidence, and correction date. The final status is “included after documented correction.” A key match alone would not have detected this discrepancy.
For clinical records, first determine whether the source value is subject-level, visit-level, or measurement-level. If a visit-level value is intentionally linked to several sample rows, repeated appearances in the analysis dataset may be expected. Check that the value is attached only to eligible records and that the linkage follows the documented timing rule. Do not label a repeated covariate value a duplicate solely because it appears on several sample records: the question is whether the analysis row represents a separate sample and whether the covariate linkage is appropriate.
Worked Example: A Clinical Record Linked to Multiple Samples
A hypothetical clinical extract contains a weight measurement of 70.0 kg for subject P08 at visit V2, recorded under clinical record ID C208. The analysis dataset contains two PK samples for P08 at V2, each carrying weight 70.0 kg and source ID C208. A third PK sample for P08 is from V1 and carries weight 68.5 kg, linked to clinical record C201.
The clinical file has two relevant weight records, while the analysis file has three sample rows. The grain differs: one clinical record per subject and visit, but one analysis row per sample. Thus a row-count equality is not expected. The two V2 samples linked to C208 are a documented one-to-many relationship, not duplicate clinical records. The V1 sample links to C201, not C208. The reconciliation checks that each analysis sample has the expected subject and visit linkage, that the clinical source IDs exist, and that the recorded values agree. If a V2 sample instead carried C201, it would be flagged as a linkage mismatch even if its weight looked plausible. This check verifies provenance and visit alignment; it does not by itself establish that a clinical measurement is scientifically appropriate for every analysis use.
Diagnostics, Alternatives, and Limitations
Several patterns signal that matching is going wrong. A supposedly unique key appears more than once; many records become unmatched after a date format or ID-format change; one source record joins to unexpectedly many analysis rows; matched values differ more often than documented transformations would allow; or the number of matches changes sharply between extract versions. Investigate these patterns before accepting the reconciliation. Common causes include whitespace or leading-zero differences in IDs, inconsistent time zones, an incorrect record grain, mixed extract versions, accidental many-to-many joins, and a source file that is incomplete.
The main alternative to direct key-based matching is fuzzy or approximate matching, for example pairing records by subject and a nearby time when event IDs are absent. It can help generate candidates for human review, but it should not silently establish identity: two samples or doses may occur close together. If used, state the matching fields and tolerance, inspect ambiguous candidates, and preserve the evidence for the final decision. Exact identifier matching is preferable when a reliable source identifier exists. Manual review is necessary for unresolved cases, but a manual decision still needs a recorded rule and supporting evidence.
Reconciliation also has limits. It can show that an analysis record traces to a source record and that specified fields agree; it cannot establish that the source itself is correct, that the study procedures were followed, or that an analysis rule is clinically or statistically appropriate. Some source records may legitimately be absent from analysis because of protocol rules, data status, or analysis scope. Conversely, an unexplained analysis-only record is not made valid simply by appearing in a derived file. Unresolved mismatches should remain visible and be escalated or held out according to a documented decision process.
Maintain a Traceable Disposition
A useful reconciliation log contains enough information for another reviewer to reproduce each decision. At minimum, record the source and analysis file versions, comparison date, record key, match status, relevant field differences, disposition, reason, evidence or reference, reviewer, and any correction made. Preserve original values and keep corrected values distinguishable from them. A file-level summary may report counts by status, but the detailed record-level log is what makes each count auditable.
Identify file versions, record grain, matching key, expected relationship, and fields that should agree.
Identify matched, source-only, analysis-only, duplicated, and ambiguous records; check field values for matched keys.
Use source evidence and analysis rules to distinguish legitimate exclusions, correctable errors, and unresolved issues.
Document each decision, retain original values, apply approved corrections, and confirm that final counts and links are explainable.
A common mistake is to equate equal file sizes, or a high percentage of matched keys, with successful reconciliation. That reasoning overlooks unmatched records, incorrect values under matched keys, and invalid many-to-many joins. The correction is to define the grain and key first, inspect both directions of mismatch, check relevant values, and account for every record with a traceable disposition. Reconciliation is complete only when the apparent differences are resolved or explicitly documented as unresolved—not merely when the files have been made to look alike.
Check Your Understanding
Answer each question, then compare with the brief solutions below.
- A laboratory extract has 12 unique specimen keys; 10 match the analysis file, and the analysis file has 11 unique keys. How many source-only and analysis-only keys are there?
- Why is subject ID alone usually an inadequate key for matching concentration records?
- A visit-level clinical record is linked to three sample rows from the same visit. Is this necessarily a duplicate? What should be checked?
- A dose key matches, but the dose amount differs between source and analysis. What should happen before the analysis value is changed?
- When might approximate matching be useful, and what limitation must be addressed?
Solutions. (1) Source-only keys equal \(12-10=2\). Analysis-only keys equal \(11-10=1\). These counts account for each file: \(12=10+2\) and \(11=10+1\). (2) One subject can have many concentration records, so subject ID does not distinguish specimens or sampling events; add a stable specimen or sample identifier, or another justified event-level key. (3) No. The clinical record may be visit-level and intentionally linked to multiple samples. Check the documented grain, the subject and visit for every link, and whether repeated linkage is expected. (4) Investigate the discrepancy against the source evidence and applicable analysis rule. If a correction is justified, preserve the original value and document the corrected value, reason, evidence, and reviewer. (5) Approximate matching can identify candidate pairs when stable identifiers are unavailable, such as records with nearby times. It can confuse distinct events, so candidates require review and the matching rule and evidence must be documented.