Why Reconcile Prepared Records With Their Sources?
A prepared analysis record is not necessarily a literal copy of a source record. Values may have been converted, harmonized, derived, or reshaped during preparation. Reconciliation asks a specific question: does each prepared value agree with the appropriate source evidence after applying the documented transformation? It is a targeted way to find and resolve differences while preserving a trail from the prepared record back to its origin.
For example, a laboratory result recorded as 2,400 ng/mL in a source extract can correctly appear as 2.4 mg/L in a prepared dataset. A comparison that flags every unequal text value would call this a discrepancy, even though the units are equivalent. Conversely, a prepared value of 24 mg/L would not agree with the source under that conversion. Reconciliation therefore requires both a reliable record match and an explicit expectation for how the field should be represented.
Establish What Is Being Compared
Before comparing values, specify the version of the prepared dataset, the source files or documents, and the rules used to map fields. A source may be an electronic extract, a laboratory report, a clinical record, or another controlled record. If two sources disagree, there may be no defensible answer until the project’s source hierarchy or an authorized clarification identifies which evidence governs. Do not silently choose the value that seems most plausible.
Record matching is a separate task from value comparison. Identify records using stable keys appropriate to the data, such as subject identifier, sample identifier, visit, and collection date or time. A subject identifier alone is rarely enough when a subject has several samples. Confirm that identifiers have not been altered or lost during preparation. If the match is uncertain, label it unresolved rather than comparing values across a possibly incorrect pair of records.
Then define the comparison rule for each field. The rule should state the source field, prepared field, any conversion or derivation, treatment of missing values, and any permitted formatting differences. Relevant earlier material on traceable data transformations supports this step: reconciliation checks a prepared result against the transformation that is supposed to produce it. It does not assume that the source and prepared columns must be identical.
A Practical Reconciliation Sequence
Identify the prepared dataset version, source versions, fields, and record population being reviewed. Preserve enough information to reproduce the comparison.
Use the designated keys and flag absent, duplicated, or ambiguous matches before comparing field values.
Normalize permitted units, formats, or documented derivations. Do not normalize away a difference that may be meaningful.
Describe the observed difference, likely cause, affected records and fields, and potential analysis impact. Mark uncertain causes as unresolved.
Record the evidence and authorized decision, update the prepared data or its specification as appropriate, and retain a status that shows whether the issue is open, resolved, or awaiting clarification.
Classifying Differences by Cause and Impact
Cause and impact answer different questions. Cause describes how a difference arose; impact describes what could be affected if it remains unresolved. A likely transcription error might have high impact if it changes a dosing event, while a formatting difference might have no impact if both forms represent the same date under a clear rule. Classifications should be useful for triage, not treated as proof of the underlying explanation.
| Cause category | Example | Question to resolve |
|---|---|---|
| Record linkage | A result is attached to the wrong sample identifier | Can the intended source record be established? |
| Transcription or extraction | A digit differs between a report and prepared value | Which value is supported by the source evidence? |
| Unit, format, or coding | Mass units differ, or a qualifier is absent | Was the conversion or coding rule applied correctly? |
| Omission or duplication | A source event is missing or appears twice | Is the event absent, excluded by rule, or repeated in error? |
| Transformation or specification | A derived field does not follow its stated rule | Is the implementation wrong, or is the rule unclear? |
| Source conflict or ambiguity | Two controlled sources report different values | Which source governs, or who must provide clarification? |
Impact categories should be defined for the project and tied to use of the affected fields. A practical scheme might distinguish high, moderate, and low potential impact. High impact can mean a plausible effect on record identity, event timing, dose, concentration, analysis inclusion, or another decision-driving field. Moderate impact can mean an effect on interpretation or a derived field that requires review. Low impact can be reserved for differences that do not change the represented value or analysis use under the documented rules. The classification is provisional until the evidence and intended use are considered.
A discrepancy log should be a usable record of investigation, not simply a list of flagged cells. At minimum, include a unique issue identifier; dataset and source versions; record keys and field names; source and prepared values with units; discrepancy description; cause and impact categories; evidence or reference; owner; decision; action taken; status; and resolution date or version. If one issue affects many records, identify the affected set in a reproducible way rather than copying an incomplete list into a note.
Worked Example: A Unit Difference That Is Not a Discrepancy
Suppose a hypothetical source extract identifies sample S204 with a concentration of 2,400 ng/mL. The data specification says the prepared field is expressed in mg/L. The prepared record contains 2.4 mg/L. Determine whether to log this as an unresolved discrepancy.
Match. The sample identifier S204 appears in both records, so the comparison is between the intended pair. The applicable rule is conversion from ng/mL to mg/L. Since 1 mg equals 1,000,000 ng and 1 L equals 1,000 mL, the numerical conversion is:
The prepared value agrees exactly with the converted source value. The source and prepared strings differ, but the values agree after the specified transformation. This is an expected unit conversion, not an unresolved discrepancy. Retain the conversion rule and source linkage as traceability evidence; do not inflate the discrepancy log with an issue that the specification already explains.
Worked Reconciliation and Resolution
Worked Example: A Weight Value With a Suspected Scale Error
In a hypothetical record for subject P017 at visit 1, the source document reports body weight as 68.4 kg. The prepared dataset contains 684 kg, and the field specification requires kilograms without further scaling. Explain the comparison, classification, and resolution.
Check the match and rule. Subject P017 and visit 1 identify the same measurement in the source and prepared record. No conversion is specified, so the expected prepared value is 68.4 kg. The absolute difference is \(684 - 68.4 = 615.6\) kg, and the ratio is \(684 / 68.4 = 10\). The prepared value is ten times the source value.
Classify cautiously. The observed cause is a value mismatch. A decimal-place or scaling error is plausible, but the comparison alone does not establish where it arose. Because weight is used in the project’s weight-derived covariates, a tenfold difference could affect those fields and their interpretation. Mark potential impact as high under a scheme in which such errors can alter analysis inputs.
Resolve with evidence. The source document supports 68.4 kg, and the specification requires that unit. If the source is confirmed as authoritative, correct the prepared value to 68.4 kg, rerun any dependent transformation under its documented rule, and record the affected record, before-and-after values, evidence, decision, and new dataset version in the log. If the source itself is unclear, leave the issue open and request clarification; do not infer a correction solely because 68.4 kg appears more plausible.
The resolution is complete only when the authorized source decision, the prepared change or reason for no change, and the affected downstream fields are documented. A log entry that says only “fixed weight” cannot show what was changed or why.
Worked Example: A Missing Result Qualifier
Suppose a hypothetical laboratory source reports sample S311 as “< 0.5 mg/L,” while the prepared record contains a numeric result of 0. The source and prepared records match on sample identifier, but the prepared record has no qualifier. The specification says results below the quantification limit retain the source qualifier and are represented according to the project’s stated analysis convention.
The source value communicates both a limit and a qualifier; zero does not communicate the same information. The difference is therefore not resolved by matching the numeric portion or assuming that zero is an acceptable substitute. Classify the discrepancy as a coding or transformation issue, with potential impact on the representation and analysis use of the result. Check the specified convention and source mapping. If the source is confirmed, update the prepared representation to preserve the required qualifier and corresponding value treatment, then document the exact field change and affected sample. Do not invent a replacement numeric value outside the documented convention.
If the source report and extract disagree about the qualifier, classify the issue as a source conflict as well and seek an authorized decision. The log can preserve both observations while the question is open; it should not conceal uncertainty by selecting one silently.
Prioritizing, Summarizing, and Diagnosing
Reconciliation findings can be summarized to guide follow-up, but counts are not a substitute for issue-level evidence. Suppose a review compares 12 specified records and identifies 3 unresolved differences. The descriptive fraction is:
This says that 3 of the 12 records in that defined comparison had an unresolved difference at the time of review. It does not estimate the error rate in unreviewed records unless the review design supports that inference, and it does not say whether the issues are important. Three differences in identifiers or dosing fields may deserve more urgent attention than many harmless formatting variations.
Useful diagnostic signs include many mismatches in one field, a repeated constant conversion factor, unexpected unmatched keys, duplicated matches, or issues concentrated in one source version or preparation step. These patterns can help locate a shared cause, but they do not replace checking affected records. A log should distinguish confirmed cause from hypothesis; for example, “factor-of-ten pattern, likely scaling error” is more accurate than stating that a specific script caused it before evidence confirms that.
Reconciliation has limits. It can only compare records within its defined scope and against sources that are available and identifiable. Agreement with a source does not prove that the source is correct, that the data are complete, or that every preparation rule is appropriate. A clean reconciliation result should therefore be described narrowly: no unexplained differences were found in the records, fields, sources, and rules reviewed. It is not a guarantee that the dataset has no errors.
Choosing the Right Review Approach
A field-by-field manual comparison can be useful for a small number of complex records, especially when the evidence is documentary. It is slow and can be difficult to reproduce if the reviewer does not record the scope and decisions. A scripted comparison is efficient and repeatable for structured fields, but it can produce misleading flags if keys, units, formats, or allowed transformations are not encoded correctly. A practical workflow often uses scripted comparisons to identify candidate differences and documented human review to classify and resolve them.
Reconciliation is also distinct from cross-field consistency checks. A record can be internally consistent yet disagree with its source; a record can match its source and still violate an internal rule. Neither comparison answers the other question. Similarly, a discrepancy log tracks issues and their dispositions; it is not a substitute for the later independent review of dataset quality. Keep the scope and evidence for each review clear.
Common Mistakes
- Comparing values before confirming the record match. A numerically plausible result attached to the wrong sample is still a serious discrepancy. Verify stable keys first.
- Flagging every unequal value. Unit conversions, formatting, and documented derivations can produce different representations of the same information. Apply the specified rule before classifying.
- Calling a suspected cause confirmed. A factor-of-ten difference suggests a scaling problem, but the log should separate observation, hypothesis, and verified explanation.
- Correcting a value based on plausibility alone. Use authorized source evidence or clarification. Do not replace an unusual source value just because another number looks more reasonable.
- Closing an issue without recording its disposition. Preserve the evidence, decision, affected records, action, and dataset version so that another reviewer can understand the resolution.
- Treating a low discrepancy count as proof of quality. State what was compared and avoid conclusions about records or sources outside that scope.
Check Your Understanding
Answer each question, then compare your reasoning with the brief solutions below.
- A source reports 1,500 ng/mL and the prepared field is specified in mg/L. What value should be compared with the prepared field after conversion?
- A prepared record differs from its source, but the subject identifier matches while the sample identifier does not. What should be checked before classifying the value difference?
- A source and prepared dataset both report the same value, but two source documents disagree. Is the reconciliation issue necessarily resolved? Explain.
- A review finds 2 unresolved differences among 20 compared records. What descriptive percentage can be reported, and what does it not establish?
- Name four useful elements of a discrepancy log entry beyond the discrepancy description.
Solutions. (1) \(1{,}500\ \text{ng/mL} = 1.5\ \text{mg/L}\), so compare the prepared value with 1.5 mg/L. (2) Confirm the intended sample-level match using the designated keys; do not compare values across an uncertain record match. (3) No. The conflicting sources require the applicable hierarchy or authorized clarification, even if the prepared value matches one source. (4) \(2/20 = 0.10 = 10\%\) of the records in that defined comparison had unresolved differences; it does not establish an error rate for unreviewed records or the importance of the differences. (5) Examples include source and dataset versions, affected identifiers and fields, source and prepared values with units, cause and impact classifications, evidence reference, owner, decision, action, status, and resolution date or version.