A student merges scores from two school exercises into a spreadsheet and calculates an impressive average. One score is entered as 105 even though the task is marked out of 100. Another appears twice because a pupil’s submission was imported two times. A third belongs to a practice test rather than the final assessment. The formula works perfectly. The information it summarises does not.
In a Science investigation, a table shows an abrupt temperature jump that might indicate an important finding—or a missing decimal point. In a community survey, 99 could mean ninety-nine respondents or a special code for unanswered. Without checking the rules, software can transform recording mistakes into apparently precise evidence.
Data quality auditing is the disciplined examination of collected records, definitions, permissible values, missingness, consistency, duplication, transformations and calculation paths *before* drawing a substantive conclusion. The objective is not to delete inconvenient observations. It is to distinguish valid surprising data from identifiable errors, preserve original information and document the treatment of uncertain cases.
Good audits are proportionate. A Primary tally chart might need a count of rows and a check of units. An advanced research dataset may need systematic validation rules, independent review and a reproducible transformation record. The guiding question is the same: does the analysed information still faithfully represent what was observed?
This guide complements Data Definition Alignment Skills (whether sources measure the same thing), Missing Data Interpretation Skills (what unobserved records mean), Measurement Interpretation Skills (understanding a result), and Reasoning Audit Skills (checking inferential steps). Here the owner job is the *integrity of the input records and the processing path that created the analysis dataset*.
Before the Top 10: A Valid Formula Cannot Rescue Invalid Inputs
Start with a source file, its intended record layout and a short data dictionary. One row might mean one pupil, one assessment, one trial or one visit. One column might mean a score, measurement or category. Without those meanings, a duplicate cannot be recognised and a seemingly unusual value may not actually be invalid.
Differentiate validation from correction. Validation flags a value that violates an established rule or deserves checking. Correction requires evidence of the right replacement. If a score is 105 on a 0–100 test, the row may contain a typing error or incorrect test identifier; it should be investigated, not silently replaced with 100.
Keep the raw data unchanged and record a separate cleaned version or auditable changes wherever practicable. A pupil should be able to explain what was fixed, which original value was retained for reference and why an observation was excluded or corrected. Changing data to improve a desired conclusion is not auditing.
The US Census Bureau’s statistical quality standards provide practical examples of range, duplicate, consistency, missingness and record-linkage checks, as well as retaining unedited values. These are professional statistical standards used here as inspiration, not an official Singapore school data-processing requirement.
DEFINE RECORD → VALIDATE VALUES → RECONCILE SOURCE → CHECK TRANSFORMATIONS → REPORT LIMITS
1. Define the Record Layout and Data Dictionary Before Checking Values
A validation rule is only meaningful if the analyst knows what one row and each column represent. A table of pupils is different from a table of assessment attempts. A label such as attendance can count unique people or repeated entries, and the expected number of rows may differ accordingly.
Consider a spreadsheet with columns named StudentCode, Assessment, Score and Date. If each row is one assessment attempt, multiple rows per code can be valid. If the data promise exactly one final score per pupil, duplicates of that code-assessment combination deserve investigation. The dictionary determines the rule.
Write a short definition, unit, allowed category and observation window for each important variable. Include how anonymised codes are assigned where necessary. Reject generic data-cleaning routines that assume every identifier must be unique without understanding the actual unit of analysis.
Worth learning because a valid audit starts from what the records are supposed to mean, not from whether their numbers look tidy.
2. Check Data Types and Allowed Ranges
Some fields must contain dates, categories, integers or measurements within a defined range. A score of 105 on a test out of 100 violates an established score rule, while a negative temperature in Celsius can be entirely legitimate in a suitable context. The validity rule must fit the quantity.
A student enters 105 in a 0–100 score column. The audit should flag that row, compare it with the original script and check whether it belongs to a different test or was mistyped. The correct replacement might be 10.5, 100, missing or something else; the number alone does not establish which.
Use explicit allowed values and units. Flag, investigate and document rather than clipping every out-of-range value automatically. For categories, reject unrecognised entries only after considering whether the allowed category definitions have been updated.
Worth learning because mechanically applying a plausible formula to invalid inputs produces precise-looking but untrustworthy results.
3. Distinguish Missing Observations From Genuine Zeros
Blank cells, special codes and valid zeros can have different meanings. Treating every blank as zero changes totals and averages; ignoring zero because it looks unusual can inflate results. A correct audit preserves what was and was not observed.
Suppose completed quiz scores are 0, 5, 7 and 8, plus one unrecorded outcome. The observed four-score mean is 20/4 = 5. Filling the missing value with zero gives 20/5 = 4, but this invents a fifth score. The legitimate zero from the completed quiz should remain in the data.
Define codes for absent, not administered, unknown and not applicable as appropriate. Report missing counts with their denominators. Use the linked missing-data guide for deeper questions about nonresponse mechanisms and sensitivity.
Worth learning because absent information is not a measured failure and should not be disguised as one.
4. Detect Duplicate Records Without Deleting Valid Repetitions
A duplicated row may result from importing a file twice, copying a form submission or merging records incorrectly. But repeated observations can also be genuine: the same pupil may attend several lessons or sit two separate tests. Deduplication must follow a documented key.
A programme export shows eleven records for ten expected unique enrolments. Two entries have the same pupil code, session and timestamp. They may be duplicate imports, but if a system permits two legitimate actions in the same minute, further evidence is required before deleting one. A pupil appearing on different dates should not be erased simply for repeated identity.
Identify the intended unique key, compare source timestamps and inspect related fields. Preserve which records were excluded and why. Report unique pupils and repeated visits as separate metrics when both are meaningful.
Worth learning because automated deduplication can remove real learning activity as easily as it can remove an import mistake.
5. Run Cross-Field Logic and Total Reconciliation Checks
Columns can be individually within range yet logically inconsistent with one another. A record might say that an assessment was never attempted but contain a final mark. Subtotals may fail to equal a reported total. Such contradictions need checking against the process that created them.
Suppose an observation table lists five red, three blue and two green counters but its total column says twelve. The component sum is 5+3+2 = 10. At least one field or the stated counting rule is inconsistent. The analyst should return to the actual observation instead of forcing the data to add up to the preferred value.
Define a few meaningful integrity rules, such as total equals sum of valid categories where those categories are exhaustive. Flag exceptions and record explanations. An apparent inconsistency may reflect a legitimate exception, so do not automatically delete the entire row.
Worth learning because internal consistency can reveal errors that no single range check could find.
6. Investigate Outliers Without Automatically Removing Them
An unusually high or low value may be a real observation, a different population, a change of units or an entry error. Removing it simply because it changes the mean is a form of selective reporting. A useful audit examines provenance and measurement conditions first.
A pupil records 900 minutes of weekly revision while classmates report 40–120 minutes. The value could be a mistaken zero, total time for a different period, or genuinely high activity. None can be established from visual surprise alone. Similarly, a Science temperature anomaly could reveal a new event or a displaced probe.
Flag unusual values using subject knowledge or a justified rule, inspect original records and document any treatment. Compare summaries with and without the flagged case as a sensitivity illustration where appropriate, explicitly labelling which version is reported and why.
Worth learning because unusual data can be errors or discoveries, and a fair audit must distinguish them with evidence.
7. Check Units, Dates and Category Codes Across Records
Even in one dataset, entries may use incompatible units or change coding conventions over time. A speed column containing both metres per second and kilometres per hour will produce invalid averages unless the values are aligned. A category code may change meaning after a software update.
Imagine one class records a paper bridge span as 250 cm and another as 2.5 m. The numerical values differ while the lengths agree. If both are entered into a column labelled metres without conversion, the first appears a hundred times larger. Similarly, a code of 9 could mean “other” on one survey version and “not answered” on another.
Track the original unit, date and collection version. Apply verified conversions only to compatible quantities and retain the source value. If category mappings are ambiguous, leave the records flagged or separated rather than forcing a match.
Worth learning because mixed units and changing codes can create trends that are entirely artefacts of data handling.
8. Reconcile Row Counts and Important Figures to the Original Source
A cleaned table should be traceable back to the records that produced it. Row-count differences can reveal omissions, duplicate imports and unreported exclusions. Reconciliation need not mean every count matches before and after processing; it means every difference has a documented reason.
If a register contains twenty expected assessment entries and the exported analytic file contains eighteen, the audit asks which two records disappeared and why. Perhaps both are truly missing outcomes; perhaps the export filtered them out as blank. Recomputing the eighteen-score mean may be valid for observed scores but not a description of all twenty pupils.
Compare original counts, imported counts, included rows and excluded rows. Check one or two consequential values against the source scripts or instrument logs, using privacy-protecting methods. Treat discrepancies as an investigation, not a reason to fabricate entries.
Worth learning because an audit trail shows how a real observation became a number in the published result.
9. Verify Calculations and Processing Rules Independently
A correct-looking chart can be generated from a formula that references the wrong column, includes a hidden filtered row or rounds too early. Independent calculation and a test dataset can catch errors that repeated use of the same spreadsheet formula misses.
Suppose three Science mass measurements are 17 g, 21 g and 19 g. Their arithmetic mean is (17+21+19)/3 = 19 g. If a spreadsheet displays 18 g, inspect the cell references, values and any hidden records before attributing the difference to experimental uncertainty.
Document important formulas, filters, rounding and transformations. Recompute one representative case by another route or with a small known-input test. For formal analysis, appropriate peer review and reproducible scripts may be needed.
Worth learning because verified calculations protect against processing errors that can survive even a perfectly recorded original dataset.
10. Release a Data Quality Summary With Unresolved Issues Visible
The audit should end in an interpretable decision: ready for the intended analysis, suitable only for a narrower purpose or not yet reliable. A data quality log lists detected problems, changes made, source evidence, remaining uncertainty and how those issues affect conclusions.
A fictional class survey of thirty respondents contains one duplicate submission, two missing answers and an ambiguous category code. A fair report can state the counts and show which records were included in each statistic. It should not silently present the adjusted total as if every answer had been observed and unequivocal.
Keep original files secure, minimise identifying information and share only appropriate summaries. Invite a fresh independent check of one reported claim. If a material unresolved defect could reverse the decision, withhold the overstrong conclusion until the issue is addressed.
Worth learning because transparent data quality is part of the evidence, not a secret cleanup step performed before writing.
The Top 10 Data Quality Audit Skills as One System
The route is DATA DICTIONARY → TYPES AND RANGES → MISSINGNESS → DUPLICATES → CROSS-FIELD LOGIC → OUTLIERS → UNITS AND CODES → SOURCE RECONCILIATION → FORMULAS → AUDIT SUMMARY. Use the relevant checks rather than a bureaucratic checklist for every simple tally.
A useful question is: can another careful reader distinguish valid original observations from corrected entries, exclusions and unresolved uncertainties? If not, the reported analysis may be difficult to trust.
Data Quality Audit Is Not Data Definition Alignment or Reasoning Audit
Data Definition Alignment asks whether quantities from different sources mean the same thing. Data quality auditing asks whether the records and processing rules in an actual dataset are internally accurate and traceable before analysis.
Reasoning Audit tests whether the final argument follows from its premises. Data quality audit checks those premises’ numeric records and transformations. A correct logical inference from corrupted data can still produce a false factual conclusion.
Worked Primary Mathematics Example: Check the Counting Table
A class tallies coloured counters and records five red, three blue and two green. The total should be ten. If the student’s summary says twelve, inspect whether two counters were counted twice, a category was omitted or a number was copied incorrectly.
The pupil can recount using a different grouping order, then check 5+3+2 = 10. Correcting the total after verifying the objects is a data quality improvement. Rewriting individual category counts merely to force a total of twelve would fabricate observations.
Young learners can start with checkable totals and units; they do not need database engineering terminology.
Worked Secondary Science Example: A Suspicious Temperature Entry
A teacher-approved experiment records 28°C, 29°C, 31°C and then a suspicious 310°C. The last value could be a lost decimal point, a wrong instrument mode, a different quantity or a real reading in a very different physical setting. Its plausibility depends on the apparatus and safety conditions.
Flag the value, inspect the original notes and measurement setup, and correct only with appropriate evidence. If the original is unavailable, record it as unresolved rather than automatically replacing it with 31°C. A graph that excludes the flagged reading needs to disclose that exclusion.
This example illustrates why domain knowledge belongs alongside automatic outlier detection.
Worked Secondary English Survey Example: An Impossible Count
Suppose a class survey reports sixteen yes answers, nine no answers and six unanswered items, while stating that only thirty pupils submitted forms. The category sum is 16+9+6 = 31. At least one field, unit or assumption requires checking.
A strong audit returns to the response forms and checks whether one pupil selected more than one option, duplicate data entry occurred or the total number of forms was recorded incorrectly. Changing the no count from nine to eight just because it makes the table add up is not justified without evidence.
The finished report should state the verified denominator for each question, not just the attractive percentage computed afterward.
A Data Quality Release Gate for a Small Study
Before using a dataset to make a claim, confirm that core variables have definitions and units, that the number of records reconciles to the original source, that missing values and exclusions are visible, and that the most consequential calculations have been independently checked.
An unresolved minor typo in an unused descriptive comment may not stop every analysis. An unresolved duplicate or score-definition mismatch affecting the principal outcome can prevent a defensible conclusion. The release decision should reflect the nature and stakes of the remaining problem.
Document the result of the gate: suitable, suitable with explicit limitations, or not yet suitable for the intended question.
For Primary Students: Count, Recount and Explain the Difference
Primary pupils can check whether a tally total equals the visible objects and whether a measurement has the correct unit. Ask what one row or mark represents before summing. A duplicated entry is a useful classroom example of why recordkeeping matters.
Encourage an independent recount and correction based on observation, rather than changing an answer to match the teacher’s expected result without knowing why.
For Secondary Students: Audit the Spreadsheet Before the Graph
Secondary Science and Humanities students can check score ranges, category codes, units, missing cells, duplicate responses and chart formulas. Before plotting, they should explain which records were included and whether values genuinely measure the same thing.
Use small synthetic datasets with deliberate errors so no child’s real scores are exposed. Ask learners to flag problems, explain the likely cause and choose whether to correct, exclude or retain a value with an explicit limitation.
For JC Students: Reproducibility and Statistical Quality
JC learners can investigate reproducible transformations, data-entry validation, inconsistent record linkage, unusual values and sensitivity to exclusions. They should understand that an outlier rule chosen after seeing the result may bias the conclusion.
A good audit does not automatically improve causal identification, sampling representativeness or construct validity. Those require additional design and inferential checks, even after the dataset is technically clean.
A Fifteen-Minute Data Quality Clinic
Minutes 1–3: define one record and the main variable. Minutes 4–6: check valid ranges, blank codes and duplicates. Minutes 7–9: test logical relations and the source count. Minutes 10–12: independently recompute a key result. Minutes 13–15: write an audit summary and identify any unresolved issue.
The timing is illustrative rather than validated. A consequential research release can demand much more formal review, while a Primary count may need only one independent check.
Data Quality and AI-Generated Spreadsheets
AI and spreadsheet tools can quickly detect anomalies but also invent corrections, interpret special codes incorrectly or silently exclude rows. A polished chart should never be accepted as evidence that the underlying records were valid.
Keep original data separate, require explanation of transformations and independently test important calculations. Do not upload identifiable student records or private school results without appropriate permission and safeguards. The student should know what was observed and what was changed by software.
The Wintour House Test: Does a Clean Chart Mean Clean Evidence?
When automation can turn any file into a dashboard, the scarce skill is recognizing whether the source records and transformations deserve trust. A chart can be beautifully formatted while one imported duplicate, wrong unit or missing record reverses the conclusion.
The Gold Standard is an auditable path from original observation to validated record, defensible processing and a result that states consequential unresolved quality limits.
Research Anchors and Limits
The US Census Bureau’s Statistical Quality Standard C2: Editing and Imputing Data explicitly describes checks for missing data, duplicates, outliers, validity ranges and cross-field consistency, as well as retaining original and edited values.
The Census Bureau’s Statistical Quality Standard B2: Acquiring and Using Administrative Records discusses checking record layouts, incoming record counts, variable distributions and the quality of acquired source files.
The Census Bureau’s Statistical Quality Standard E1: Analyzing Data calls for appropriate methods, checking computations, considering relevant nonsampling error and producing documentation needed to assess analysis.
Its Statistical Quality Standard S2: Managing Data and Documents supports preservation and secure management of information for reproducibility and transparency.
These professional statistical standards are source anchors, not official MOE/SEAB rules for classroom work. The ten headings, fictional datasets and teaching clinic are original Wintour House editorial synthesis and do not certify that an audited dataset is free of every type of bias.
Final data-audit check: Can the learner define each analysed record, flag invalid and ambiguous values, trace corrections to source evidence, verify consequential calculations and explain unresolved quality problems before publishing a conclusion?
