Data cleaning is not the removal of anything inconvenient. It is the controlled process of making a dataset fit for a stated decision while preserving an audit trail. The practical question is not “is this data clean?” but “is it sufficiently complete, unique, consistent, timely, valid, accurate and reconciled for this use?”

This guide introduces CLEAN-8, a reusable checklist for business analysis. It aligns with the UK Government Data Quality Framework's principle that quality means fitness for purpose and should be managed across the data lifecycle, not treated as a last-minute correction exercise.

Direct answer

Before analysis, define the decision and unit of observation, profile the raw data without overwriting it, test quality dimensions, log every issue, reconcile important totals, document transformations, and apply a written acceptance gate. Never silently delete a row simply because it looks unusual.

CLEAN-8

Step Question Required record
Context What decision will the data support? Decision, owner, scope and materiality
Level What does one row represent? Unit, key and grain statement
Evaluate Which quality dimensions matter? Tests, thresholds and denominators
Address How will each issue be treated? Keep, correct, map, exclude or escalate
Normalize Which formats and definitions must align? Transformation rule and version
Reconcile Do important counts and totals tie to a trusted source? Difference, tolerance and explanation
Trace Can every changed value be reproduced? Raw reference, rule, timestamp and reviewer
Hold/release Is the dataset acceptable for this decision? Gate result, unresolved risks and expiry

1. Define the decision before the tests

A customer-contact list, a revenue forecast and a regulatory return can require different thresholds. State the question, audience, time period, tolerance for error and consequences of a wrong answer. This prevents a generic cleaning routine from erasing valid exceptions or spending effort on immaterial fields.

2. Declare the row grain and key

Write one sentence: “One row represents ...”. Then name the candidate key. A duplicate customer may be valid in an order table; it may be an error in a customer master. Check whether the key is missing, repeated, unstable or composed of several fields.

3. Profile quality dimensions

Use explicit denominators:

  • completeness = populated required values / expected required values
  • uniqueness = unique valid keys / rows expected to be unique
  • validity = values passing format and domain rules / tested values
  • timeliness = records within the stated freshness window / tested records
  • consistency = non-conflicting values / comparable values

Accuracy is harder: a value can have the right format and still be wrong. Test accuracy against an authoritative source, a controlled sample or a reconciliation where feasible.

4. Use an issue log

Copy this structure into a spreadsheet or ticketing system:

Issue ID Field/table Rule failed Count Material effect Treatment Owner Evidence Status
DQ-001 customer_id Required key missing 18 Customer count understated Exclude from entity analysis; escalate source fix Data owner Profile query Open

Keep the raw extract immutable. Perform cleaning in a reproducible layer, and retain the issue log even when a problem is corrected.

5. Standardize without hiding meaning

Normalize dates, units, casing and categorical labels only under an explicit map. Preserve source values when a transformation could be disputed. Do not turn “unknown”, “not applicable” and “not collected” into one blank unless the decision genuinely treats them the same.

6. Reconcile material measures

Reconciliation asks whether the cleaned dataset agrees with a trusted control total. Examples include invoice totals to a ledger, active contracts to a contract register, or imported rows to a source extract. Record the expected total, observed total, difference, tolerance and explanation.

7. Record lineage and review

For each transformation, retain the source, rule, code or formula, execution date, row count before and after, and reviewer. If a correction depends on judgement, record the reason and authority. A clean output without lineage is difficult to trust or reproduce.

8. Apply a release gate

A simple gate has four decisions:

  • release: all critical tests pass;
  • release with limitation: a known issue is immaterial for the stated use and disclosed;
  • hold: a material issue can be corrected within the analysis window;
  • escalate: the dataset cannot support the decision or requires an accountable risk acceptance.

Worked fictional example

Assume a 1,000-row customer extract intended for a retention analysis. Profiling finds 18 missing customer keys, 26 duplicate entity rows, 12 invalid period values and 10 unresolved cross-source contradictions. The cleaning sequence removes 26 confirmed duplicate rows, excludes 18 rows without a usable key, excludes 12 rows outside the valid period and holds 10 contradictory rows pending source resolution. The accepted analytical population is therefore 934 rows.

Measure Calculation Result
Key completeness 982 / 1,000 98.2%
Entity uniqueness 974 / 1,000 97.4%
Accepted population 1,000 - 26 - 18 - 12 - 10 934

The example is illustrative, not a benchmark. The analyst should disclose that the result describes 934 accepted rows, not all 1,000 source records, and should test whether exclusions are systematically related to customer outcomes.

Apply the checklist in learning

MTF Institute's Professional Certificate in Data Analysis publishes a 20-lesson applied pathway with 20 artefacts. Use CLEAN-8 as a pre-analysis gate before dashboards, models or recommendations. The programme is professional education, not an academic degree, and does not guarantee a job or business outcome; verify current live terms before enrolling.

Sources