Why Cleaning Decisions Need an Audit Trail
A data-quality check can identify a suspicious value, but a flag does not tell us what the value should be. A misspelled category may be easy to recognize, while a number that looks unusual might be a genuine observation. If someone changes a value without recording the reason and evidence, later users cannot tell whether it was corrected, standardized, or guessed.
Making a cleaning decision auditable means preserving enough information for another authorized person to trace the decision from the source record to the analysis value, understand why the decision was made, and reproduce the resulting data. The goal is not to make every flagged value look tidy. It is to make every action visible and defensible, including the decision to leave a value unresolved.
An audit trail is more informative than a note such as “fixed typos.” It lets a reviewer distinguish, for example, a correction confirmed by a source form from a consistent recoding rule applied to an entire category. It also helps locate records that should not yet be treated as settled.
Three Different Outcomes for a Flagged Value
A flag is a prompt for review, not proof that a value is wrong. After checking the available evidence, a reviewer may reach one of three useful outcomes:
- Verified correction: reliable evidence supports a particular intended value, so the cleaned value can be changed while the source value is retained.
- Rule-based standardization: a documented, consistent rule changes the representation of a value without claiming that the original observation was erroneous. For example, an approved mapping may convert the label “N” to “North.”
- Unresolved: the evidence does not establish which value is correct. The original remains available, but the value is marked as unresolved rather than replaced with a guess.
These outcomes should not be collapsed into one generic “cleaned” status. A verified correction says something about the evidence for the intended value. Standardization says a rule has been applied. An unresolved status says the review could not justify a single replacement. If an analysis requires a usable value, the analyst should handle unresolved records according to a stated analysis plan rather than silently inventing a value or deleting the record.
A Practical Structure for Recording Decisions
A useful log connects each decision to a record and a field. It also records enough detail to make the decision understandable later. The appropriate amount of detail depends on the project, but a reviewer should generally be able to answer: What was flagged? What did the source contain? What was changed, if anything? What rule or evidence supports the outcome?
| Log item | What it helps a reviewer determine |
|---|---|
| Record identifier and field | Which observation and variable were reviewed |
| Original value | What was present before the decision |
| Proposed or resulting value | What value will be used, or whether the case remains unresolved |
| Action and status | Whether the value was corrected, standardized, left unchanged, or held unresolved |
| Reason and evidence | Why the action is justified, such as a source form or a specified mapping rule |
| Reviewer or process and time | Who or what made the change and when, subject to the project’s documentation practices |
Keep the original data in a preserved source file or an unchanged raw field. Store the analysis-ready value separately, and link both to a stable record identifier. A correction log should not be the only place where the original value exists. If a script applies a rule, retain the rule in the recorded workflow and make it possible to identify which records it affected. If a person makes a judgment, record the evidence and decision rather than representing it as an automatic rule.
Worked Example: Reviewing Flagged Category Labels
Suppose a hypothetical clinic intake file contains a field called “Region,” with allowed categories North, South, East, and West. A check flags two records with the value “Noth.” The preserved raw values are shown below.
| Record | Raw Region | Review evidence |
|---|---|---|
| R014 | Noth | The original intake form clearly says North |
| R015 | Noth | The original form is unavailable |
| R016 | N | The documented category mapping defines N as North |
For R014, the form provides direct evidence of the intended category. Record a verified correction from raw “Noth” to cleaned “North,” and log that the form was checked. The source value remains “Noth”; it is not overwritten.
For R015, the spelling suggests North, but the source is unavailable. The similarity of the text is not independent evidence that the intended category was North. Keep “Noth” in the raw field and mark the cleaned value unresolved. If a later analysis cannot use unresolved region values, it can handle that record explicitly; the review itself should not pretend the intended category is known.
For R016, the mapping rule supports a rule-based standardization from “N” to “North.” Log the rule used and its version or other identifying detail if the project maintains versions. This action differs from R014: the mapping standardizes a documented shorthand, while R014 corrects a value based on evidence from that record’s source.
Work Through a Review Consistently
A repeatable review makes it less likely that similar records receive different treatment. The following sequence keeps the evidence, action, and saved data connected.
Use a stable record identifier and name the field. Confirm the value in the preserved source data before deciding what to do.
Look for a source record, an approved codebook or mapping, or other evidence appropriate to that field. Do not treat the flag itself as evidence of the correct replacement.
Record whether the value is a verified correction, a rule-based standardization, unchanged, or unresolved. If evidence is insufficient, leave the intended value undecided.
Store the cleaned value separately, record the action and its reason, and check that the saved result agrees with the log and the stated rule.
Worked Example: Checking an Apparent Unit Mix-Up
Imagine a hypothetical dataset in which the height variable is intended to be stored in centimetres. A range check flags a value of 68 among values near 160 to 185. The size of the value alone does not establish its unit. A reviewer checks the original measurement card for two records.
| Record | Raw entry | Source evidence | Review outcome |
|---|---|---|---|
| R021 | 68 | Card explicitly records 68 inches | Convert to centimetres; verified unit correction |
| R022 | 68 | Card is unavailable and unit is not recorded | Unresolved; do not infer a unit from the value alone |
For R021, the evidence identifies the original measurement as 68 inches. Since 1 inch equals 2.54 centimetres, the conversion is:
The cleaned value may be stored as 172.7 cm if the dataset’s stated display precision is one decimal place. The log should retain the raw entry, identify the source evidence, state the conversion rule, and record the resulting value. The conversion is justified because the card confirms the unit, not because 172.72 happens to fit the surrounding values.
For R022, “68” could represent inches, centimetres, or another data-entry issue. Without evidence, changing it to 172.7 cm would be a guess. Preserve the raw entry and mark the case unresolved. A later, documented source check could change that status.
Why Preserving the Original Matters
Keeping raw and cleaned values separate is not just an administrative preference. It protects the ability to check and reproduce the work. Suppose several raw labels are mapped to the same cleaned label. Once only the cleaned value is kept, it may be impossible to determine which original label appeared in a particular record. In this example, a mapping could send “N,” “North,” and “Noth” to “North.” From the cleaned value alone, the original cannot be recovered.
In general, if the cleaned value is computed as \(y=f(x)\), where \(x\) is the raw value and \(f\) is a cleaning rule, a many-to-one rule can give the same \(y\) for different \(x\) values. For example, \(f(\text{“N”})=\text{“North”}\) and \(f(\text{“Noth”})=\text{“North”}\). Therefore, knowing \(y=\text{“North”}\) does not identify whether \(x\) was “N” or “Noth.” Keeping \(x\), the rule \(f\), and the decision record preserves the information needed to trace the result.
An audit trail also makes review more efficient. A second reviewer can focus on decisions that rely on judgment, unresolved cases, and whether the recorded rule was applied consistently. A log that says only “corrected” cannot support that check, because it does not show what evidence or rule justified the change.
Worked Example: An Invalid Date With Unequal Evidence
In a hypothetical file, a date field is expected to use the year-month-day format. A check flags “2025-04-31,” because April has 30 days. The review finds the following evidence for two records.
| Record | Raw date | Source check | Outcome |
|---|---|---|---|
| R031 | 2025-04-31 | The original visit card clearly shows 2025-04-13 | Verified correction to 2025-04-13 |
| R032 | 2025-04-31 | The original card is unreadable | Unresolved |
For R031, the source card supports one specific date. The log records the raw value “2025-04-31,” the cleaned value “2025-04-13,” the fact that the card was checked, and the verified-correction status. The original value remains preserved.
For R032, the raw value is invalid as a calendar date, but that does not identify the intended date. Several different valid dates could have been mistyped. Because the source is unreadable, there is no defensible basis for selecting one. Retain the raw entry and record that the date is unresolved. This communicates both the data problem and the limit of the available evidence.
Common Pitfall: Making a Value Fit
A tempting but unreliable approach is to replace an unusual value with the value that seems most plausible. For example, a reviewer may reason, “Most heights are around 170, so 68 must mean 68 inches,” or “Noth obviously means North.” Sometimes that guess will be right. But a plausible guess is not verification, and a flag does not supply the missing evidence.
The correction is to separate plausibility from evidence. Use a source record or an established, consistently applicable rule when it supports a change. Otherwise, preserve the source value, record why it was flagged, and leave the case unresolved. Do not silently delete a record merely because it is difficult to interpret. If an analysis later excludes, recodes, or otherwise handles unresolved cases, that is a separate decision that should be made explicitly and documented.
Auditability also depends on appropriate access. Review logs and source records may contain sensitive information, so retain only the information needed for the review and follow the project’s access and storage controls. A traceable decision should not require unnecessary exposure of personal details.
Check Your Understanding
Use the ideas in this tutorial to answer the following questions.
- What information should a reviewer be able to find in an audit trail for a changed value?
- A source form confirms that “Noth” was intended to be “North.” What should be retained, and how should the change be classified?
- A value is flagged as unusual, but no source record is available. Why is replacing it with the most plausible value not a verified correction?
- How does rule-based standardization differ from correcting an error using record-specific evidence?
- Why can the original value be impossible to reconstruct from the cleaned value alone?