Why Provenance and Reconciliation Matter
A model result is only as auditable as the data that produced it. A reviewer should be able to follow a value from its source record, through any cleaning or transformation, into the analysis dataset—and understand why each participant or record was included or excluded. This is data provenance: the documented origin, movement, and alteration of data.
Reconciliation is the process of showing that source counts and values agree with the downstream data, or of explaining every difference. It is not simply a check that two files have the same number of rows. A participant may contribute many concentration records, and an analysis population is usually defined at participant level while model input is often organized at record level. Both levels need explicit accounting.
A sound reconciliation identifies the source system and extract date, the participant identifier used for linkage, the unit of counting (participants or records), the processing rules, and the destination dataset. It also distinguishes a true zero from a missing value, an excluded record, and a record that could not be linked. Those states are not interchangeable.
Define the Counting Rules Before Counting
Start by defining the population and the unit being counted. For participant-level accounting, assign each person a stable identifier and state the analysis-set criterion in operational terms. For record-level accounting, define what constitutes one record and how repeated records, duplicate transmissions, quality-control entries, and invalid values are handled.
The simplest reconciliation uses mutually exclusive categories. If every source participant is either included, assigned one primary exclusion reason, or unresolved, then:
This identity is useful only if categories do not overlap and refer to the same source population and counting unit. If a participant fails two criteria, count the participant once in the total: either define a hierarchy for a primary exclusion reason or report overlapping reason counts separately and label them as non-additive. An unresolved record is not silently treated as excluded; its status requires investigation or explicit disposition.
Record counts require a separate accounting. A participant may have zero, one, or many records. Therefore, the number of concentration records cannot be substituted for the number of participants, and a participant-count flow does not by itself reconcile the rows in a modeling dataset.
Worked Example: Reconciling Treated and PK Analysis Sets
Hypothetical data. A study screened 82 participants, randomized 78, and administered study drug to 76. Two randomized participants were not treated. The safety set is defined as all treated participants. The PK set is defined as treated participants with at least one quantifiable concentration record linked to an identifiable participant and an evaluable sample time.
The safety-set count is 76 because the rule is treatment-based. The two randomized but untreated participants are outside this set. Thus the participant flow through treatment is: \(82-78=4\) screened participants were not randomized, and \(78-76=2\) randomized participants were not treated. These counts reconcile the screened, randomized, and treated totals.
Among the 76 treated participants, three have no quantifiable concentration records. A fourth, distinct participant has quantifiable concentration records but no evaluable sample time for any such record. Neither situation meets the stated PK criterion. The remaining 72 each have at least one qualifying record:
The PK-set count is therefore 72 participants. The reason categories are distinct, and the definition explicitly requires a quantifiable concentration, a participant link, and an evaluable sample time. Nonquantifiable records alone cannot make a participant eligible under this rule.
This participant flow does not yet give the number of PK rows. If the 72 participants contribute 1, 4, and 8 qualifying records respectively, the record count depends on their individual contributions, not on the participant total. A separate record-level reconciliation is required.
Trace Variables Through Transformations
For each key model input, retain enough information to reproduce its value: the source variable and source record, the original value and unit, the transformation rule, the resulting value and unit, and any relevant flags. Preserve source and derived variables separately where possible. Overwriting a source value with a transformed value makes it harder to detect unit errors or recover the original observation.
Transformations should be deterministic and documented. Examples include converting concentration units, calculating dose per body weight, mapping a coded treatment value to a label, and deriving time relative to dose. A derivation should specify which source fields it uses, the formula, handling of missing inputs, and any rounding applied. Linkage rules should likewise document the keys used and how unmatched or multiply matched records are treated.
Worked Example: Auditing a Concentration and Dose Transformation
Hypothetical data. A source record for participant P014 contains a concentration of \(1.20\) mg/L, a nominal sample time of 2 hours, and an actual sample time of 2 hours 15 minutes. A dosing record reports 60 mg administered to a participant weighing 12 kg. The model input uses concentration in ng/mL, actual time in hours, and dose in mg/kg.
The unit conversion is \(1\text{ mg/L}=1000\text{ ng/mL}\), so the derived concentration is:
The actual time is \(2+15/60=2.25\) hours. It should not be replaced by the nominal time of 2 hours without a documented analysis rule; both values can be retained with clear variable names. The dose per body weight is:
An auditable row therefore points back to the participant’s concentration record, sample-time fields, dose record, and weight value; records the conversion and calculation; and identifies the output units. A reviewer can verify each result from the stated inputs. If weight were missing, the derived mg/kg dose would be missing or handled according to a prespecified rule—not calculated from an assumed value.
Reconcile Records and Investigate Differences
Record-level reconciliation is clearest when each change has a count and a reason. Duplicate removal must be precise: deleting a repeated transmission is different from removing all records that share a participant and time. Similarly, a record with an unusable time may be excluded from a particular model input while its participant remains in the analysis set if other qualifying records are available.
Worked Example: Explaining a Record-Count Difference
Hypothetical data. A laboratory export contains 244 rows. Review identifies four redundant copies of records already present, three quality-control rows not representing participant samples, and two rows with missing participant identifiers. These categories are mutually exclusive, and the stated counts refer to rows removed from this export.
After those dispositions, the number of participant-sample records is:
A subsequent model-input rule requires an evaluable sample time. Four of the 235 records do not have one, so they are not supplied as concentration observations:
Suppose those four records belong to participants who each have at least one other qualifying record. Then removing the four rows reduces the record count from 235 to 231 but does not change the participant count in the PK set. That conclusion depends on checking the other records for those participants; it cannot be inferred from the row counts alone.
A useful audit table would give the row count at each stage, the rule applied, the number affected, and the disposition. The underlying record identifiers should permit a reviewer to reproduce which four copies, three quality-control rows, two unlinked rows, and four time-incomplete rows were handled.
A Practical Audit Sequence
A reconciliation can be carried out as a short sequence of checks. Each step should preserve the previous-stage data or an immutable extract so that processing can be repeated and differences localized.
Record the source system, extract date or version, and whether the count concerns participants, samples, dosing events, or rows.
Define each analysis-set criterion, record-level filter, duplicate rule, and transformation before applying it. Specify how missing and unlinked values are handled.
Show the starting count, additions or removals, reason codes, and resulting count. Check participant and record totals separately.
Compare selected source values with derived values, check units and plausible ranges, and inspect unmatched or multiply matched identifiers.
Trace differences to specific records and rules. Document any unresolved items and their impact rather than forcing totals to agree.
For a transformation check, independently recompute selected derived values from source fields and compare them with the final dataset. For a linkage check, confirm that each modeled record maps to the intended participant and source record. A join that unexpectedly multiplies rows, drops identifiers, or assigns one source record to multiple participants should be investigated before analysis.
Diagnostics, Alternatives, and Limitations
Warning signs include a final participant count that cannot be derived from the stated rules; exclusion categories that sum to more than the source count without being labeled as overlapping; a discrepancy between a flow diagram and a dataset; untraceable derived variables; and unexplained changes in units or time conventions. A count that matches is not sufficient evidence of correct lineage: incorrect records can replace missing records while leaving the total unchanged.
A manual spreadsheet review may be useful for a small extract or targeted spot checks, but it is vulnerable to unrecorded edits and inconsistent repeated work. Automated checks can apply the same rules consistently and produce count summaries, but they can faithfully execute an incorrect rule. A strong approach combines documented, repeatable processing with independent review of the rules and selected source-to-output records. Neither approach removes the need to explain the analysis population and data flow.
Reconciliation has limits. It can establish where data came from and how records were selected or transformed; it does not establish that measurements are scientifically valid, that a model is appropriate, or that a resulting claim is justified. Those are distinct evaluations. Provenance also cannot recover information that was never captured, and unresolved source inconsistencies should remain visible rather than being concealed by a convenient processing choice.
Common Mistakes
- Mixing participants and records. A reduction of 10 rows does not necessarily mean 10 participants were removed. Report each counting unit separately.
- Using vague exclusion rules. “No usable PK” is not reproducible unless “usable” is defined, such as requiring a quantifiable concentration, linked participant, and evaluable time.
- Counting overlapping reasons as a partition. If one person fails multiple criteria, either assign one primary reason or clearly label reason totals as overlapping.
- Overwriting source values. Keep the original field, derived field, units, and transformation trace so that errors can be detected and calculations repeated.
- Treating missing as zero or excluded by default. State what is missing, why it is missing if known, and how the relevant rule handles it.
Check Your Understanding
Answer each question, then compare your reasoning with the solutions below.
- A source contains 50 treated participants. An analysis set requires at least one linked, quantifiable concentration with an evaluable sample time. Two participants have no quantifiable concentrations, and one other participant has quantifiable concentrations but no evaluable time. How many participants meet the rule?
- A participant contributes 12 source concentration rows, of which 2 are duplicate transmissions. What can be concluded about the change in participant count after removing the duplicate rows?
- A concentration is recorded as 0.80 mg/L and converted to ng/mL. What value should result, and what should be retained to make the conversion auditable?
- Three exclusion-reason counts sum to 14, but 2 participants meet two exclusion criteria. Why might subtracting 14 from the source participant count be wrong?
Solutions. (1) The exclusion categories are distinct, so \(50-2-1=47\) participants meet the rule. (2) The row count decreases by 2; the participant count does not change if those rows are redundant copies and the participant remains represented by the other records. (3) \(0.80\times1000=800\) ng/mL; retain the source value and unit, conversion factor, derived value and unit, and source-record link. (4) The reason counts overlap, so the same two participants are counted twice. Use disjoint primary reasons or calculate the unique number excluded; otherwise, subtraction double-counts participants.