Why Trace Individual Records?
A reproduced analysis can agree with a submitted analysis and still inherit an error from the data preparation process. Comparing outputs addresses whether two runs produced the same results; record tracing addresses a different question: can a reviewer follow a particular analysis record back to the source data and account for the values along the way? This check can reveal incorrect links, undocumented recoding, missing source records, or changes that are not explained by the documented processing.
A useful trace is not merely a lookup by participant identifier. A participant can have multiple source records, and an analysis value may be transformed before it is stored. The review must establish which source record or records were used, which transformations were applied, and whether the resulting analysis values agree with those transformations. It should also preserve enough information that another reviewer can repeat the check against the same source extract.
Set Up a Trace That Can Be Repeated
Before selecting records, identify the exact analysis dataset and source extract being reviewed. Record their filenames or controlled identifiers, versions or extraction dates, and checksums when available. A checksum helps establish that the files have not changed since the review, but it does not establish that their contents are correct. Also record the code or specification that describes the relevant data-preparation steps. Without this context, a reviewer may find a matching value in a different file or be unable to tell whether an apparent difference is expected.
Define the record-selection frame: which analysis dataset, records, and fields are eligible for review. Selection can combine risk-based choices, such as records with a history of manual correction, with a reproducible random subset. For random selection, retain the frame, selection procedure, seed if one is used, and selected record identifiers. A targeted subset is useful for finding known risk patterns, but it is not a random sample and should not be described as representative of all records.
For each selected record, retain the analysis-row identifier, the source-row identifier or identifiers, the fields compared, the expected transformation, the observed result, and the reviewer’s disposition. Preserve original source values alongside normalized or transformed values when feasible. This makes it possible to distinguish what was observed in the extract from what was created during processing.
Identify the analysis dataset, source extract, relevant preparation code, and file versions or checksums.
State the eligible frame, selection criteria, and any random-selection settings; save the selected identifiers.
Use a stable row identifier or test that the proposed key identifies exactly one source record.
Apply only specified transformations, then classify every difference as explained, unresolved, or not comparable for a stated reason.
Store the selected records, link logic, comparison results, and discrepancy dispositions with the review materials.
Keys, Candidate Matches, and Value Checks
A proposed matching key may contain one field or several fields. A key is adequate for direct matching only if it identifies the intended source record uniquely in the specified extract. Let \(k\) denote the key values on an analysis record, and let \(C(k)\) be the set of source records having that key. A direct key-based link is unambiguous only when \(|C(k)|=1\). If \(|C(k)|=0\), there is no source match under that key. If \(|C(k)|>1\), the match is ambiguous and needs another documented identifier or a different linking rule.
The uniqueness condition matters because a join on a nonunique key can silently duplicate a row or attach values from the wrong source record. An explicit source-row identifier carried into the analysis dataset is often stronger than reconstructing a link from descriptive fields. If no such identifier exists, test key uniqueness in the relevant extract and retain the test result. Do not resolve ambiguity merely by taking the first row returned by software.
For a field \(f\), write the analysis value as \(y_{A,f}\), the linked source value as \(y_{S,f}\), and the documented transformation as \(T_f\). The basic check is whether the analysis value equals the documented result:
The comparison rule must suit the field and be stated in advance. A text recode may require exact equality after a specified case conversion; a numeric calculation may use a specified tolerance if rounding is part of the documented process. A tolerance should not be invented after a discrepancy appears. If the analysis record combines multiple source records, the link and the combination rule must identify all contributing records and explain how their values produce the analysis value.
Worked Example: Tracing a Recoded Category
A hypothetical source extract contains row S104 with participant key P-017, visit code V03, and status value “complete”. Analysis row A882 contains participant key P-017, visit label “Week 4”, and status value “COMPLETE”. The documented preparation rules say that V03 maps to “Week 4” and status text is converted to uppercase. Check the trace.
Link. The analysis row retains source-row identifier S104. In the specified extract, S104 identifies one row, so the link is unambiguous. The participant key also agrees: P-017 in the source and analysis records.
Visit value. Applying the documented mapping gives \(T_{\text{visit}}(\text{V03})=\text{Week 4}\). This equals the analysis value “Week 4”.
Status value. Applying the documented case conversion gives \(T_{\text{status}}(\text{complete})=\text{COMPLETE}\). This equals the analysis value “COMPLETE”.
Both checked values are explained by the specified transformations, and the record is traceable to S104. The review record should still preserve the source identifier, source values, analysis values, and the mapping rules used; a statement that the row “matched” is not enough evidence for another reviewer to repeat the check.
Why Unique Links Are Necessary
Consider trying to link records using a key that is not unique. If two source rows have the same key, the key-based lookup cannot tell which row supports the analysis record. This follows directly from the definition of the candidate set: when \(|C(k)|=2\), there are two possible source rows consistent with the key, not one. Any procedure that selects one without an additional rule adds information that the key itself does not contain.
The same issue can arise after normalization. For example, if a process removes leading zeros, distinct raw identifiers “0042” and “42” both become “42”. A normalized key can therefore create a collision even when the original values were distinct. The appropriate check is to examine uniqueness both at the level used for linking and, where relevant, before and after the normalization. Keep the raw key so the transformation can be inspected rather than inferred.
Worked Example: Resolving an Ambiguous Key
A hypothetical source extract has two rows: S701 has participant key P-204, category A, and value 12; S702 has participant key P-204, category B, and value 15. Analysis row A204 has participant key P-204, category B, and value 15. The preparation output also retains source-row identifier S702.
If the review matches only on participant key P-204, the candidate set is \(C(\text{P-204})=\{\text{S701},\text{S702}\}\), so \(|C|=2\). A lookup on that key alone is ambiguous; it cannot establish that A204 came from either row.
The retained source-row identifier points to S702. Checking that identifier against the extract gives one row, and its category and value agree with analysis row A204: category B equals category B, and 15 equals 15. Thus the trace is supported by the stable row identifier. The participant key and values provide corroborating checks, but they do not replace the row-level link.
If S702 were absent from the extract, or if the identifier appeared on multiple rows, the trace would remain unresolved. The reviewer should report that limitation rather than substitute a plausible-looking row.
Document Transformations and Classify Differences
A mismatch is not automatically an error: the source and analysis values can legitimately differ after a documented operation. But a difference is explained only when the applicable operation is specified, its inputs are traceable, and applying it yields the recorded analysis value. “The program cleaned the data” is not a reproducible explanation. Record the rule precisely enough to distinguish the expected result from alternatives.
Worked Example: Distinguishing a Documented Recoding From a Mismatch
In a hypothetical extract, source row S330 has participant key P-330 and response value “UNK”. The documented rule maps exactly “UNK” to a missing analysis value. Analysis row A330 points to S330 and has a missing value. A second source row, S331, has participant key P-331 and response value “PENDING”; its linked analysis row A331 also has a missing value. The documented rule does not mention “PENDING”.
For A330, applying the rule gives \(T(\text{UNK})=\text{missing}\). The analysis value is missing, so this difference from the literal source text is explained by the documented recoding.
For A331, the documented rule gives no instruction to map “PENDING” to missing. Therefore the reviewer cannot derive the analysis value from the stated rule. A331 is an unexplained mismatch, even though missing might appear plausible. It should be logged with both record identifiers, the source and analysis values, the rule reviewed, and a request for clarification or correction. Until resolved, it should not be relabeled as an expected transformation.
For every comparison, use a small set of clear dispositions: for example, “agrees,” “agrees after specified transformation,” “unmatched,” “ambiguous,” or “unexplained difference.” Include the reason and evidence for each nontrivial disposition. Do not collapse unmatched records and value mismatches into one category: the first concerns whether a source record can be identified, while the second concerns whether linked values agree under the documented processing.
Diagnostics, Alternatives, and Limitations
Useful warning signs include selected analysis rows with no source link; source identifiers that occur more than once; a join that increases the number of rows unexpectedly; missing comparison results; normalized identifiers that collide; and analysis values that cannot be reproduced from their linked inputs. Investigate these conditions before interpreting an apparent match rate. A high proportion of matching values does not compensate for ambiguous links, and a successful file checksum does not validate the record contents.
| Approach | Strength | Limitation |
|---|---|---|
| Stable source-row identifiers | Direct, repeatable link when identifiers are present and unique | Does not by itself verify that the linked values were transformed correctly |
| Composite-key matching | Can recover links when a stable row identifier was not retained | Requires demonstrated uniqueness; key collisions or missing fields can create ambiguity |
| Manual visual review | Can help investigate a small number of unusual records | Hard to reproduce at scale and vulnerable to transcription or selection errors |
| Automated record-level comparison | Efficient and repeatable for a defined set of fields and rules | Can consistently apply an incorrect link or incorrect transformation unless those are independently checked |
A selected-record trace is a targeted review, not proof that every analysis record is correct. Its strength depends on the selection frame, the quality of source identifiers, and the completeness of the transformation documentation. Document unresolved cases and the scope of what was checked. A record-level trace also does not replace separate reviews of domain-specific values or broader dataset construction; it establishes the lineage of the chosen records and evaluates the specified comparisons.
Common Pitfalls
- Matching on a familiar identifier alone. A participant key may appear trustworthy but still identify several source rows. Check uniqueness at the record level.
- Accepting a plausible value. A plausible analysis value is not evidence of lineage. Link it to source inputs and reproduce it using the stated rule.
- Explaining differences after seeing them. A newly invented recoding rule is not documentation of the process. Record the discrepancy and seek an authoritative explanation.
- Overstating a small check. A handful of traced records supports conclusions about those records, not an assurance that all records are correct.
Check Your Understanding
Answer each question, then compare your reasoning with the solutions.
- An analysis row matches three source rows when joined on participant key alone. What does this show, and what should the reviewer do?
- A source identifier is “0042”, but the analysis stores “42” after a documented rule removes leading zeros. What should be retained and checked?
- A source value is “N/A”, the written rule maps “N/A” to missing, and the linked analysis value is missing. How should this comparison be classified?
- A reviewer selects only records previously flagged as unusual. Can the reviewer describe the selected records as a random, representative sample? Explain.
- A linked source value differs from the analysis value, and no documented rule explains the difference. What is the appropriate disposition?
Solutions. (1) The key is ambiguous because it produces three candidates; use a stable unique source-row identifier or another justified linking rule, and do not select a candidate arbitrarily. (2) Retain the raw identifier and the normalized identifier, document the zero-removal rule, and check that normalization has not caused collisions. (3) Classify it as agreeing after the specified transformation, with the source value, analysis value, and rule recorded. (4) No. The selection is risk-based, not random; it can target likely problems but does not support a claim that the subset represents all records. (5) Record an unexplained difference with both record identifiers and values, investigate it, and leave it unresolved until an authoritative explanation or correction is available.