Data Quality and Cleaning
Data Quality and Cleaning tests whether you can define quality objectively, measure it, and fix causes rather than symptoms. Questions ask which dimension a defect violates, how to write a testable rule, and which cleansing technique is defensible.
Core rules and concepts
- Six dimensions (DAMA): completeness (required values present), uniqueness (no unintended duplicates), validity (values conform to domain, format, range), accuracy (values match reality), consistency (agreement across systems and within a record), timeliness (available when needed and current). A defect belongs to the dimension its rule tests - "email has no @" is validity, not accuracy.
- Rules must be testable: state column, condition, threshold and action. "Customer email completeness ≥ 99% at load; below that, quarantine the batch" is a rule. "Emails should be good" is not.
- Profile before you clean: null rates, distinct counts, min/max, pattern frequency, key duplication. Profiling shows whether a rule is realistic - a 40% null column may be optional by design.
- Cleansing techniques: standardisation (case, units, address parsing, mapping to reference codes), deduplication (deterministic exact match, then blocking plus fuzzy scoring), survivorship (source priority, most recent, most complete), imputation (mean/median, last known value, model-based), and quarantine for unresolvable records.
- Audit everything: before/after counts, rule IDs, who approved a merge, retained lineage. Cleansing without lineage destroys reproducibility and trust.
Worked example
A 1,000,000-row customer table yields 12,000 exact duplicates after trimming case and whitespace; deterministic dedup merges them. A further 3,000 suspected duplicates surface by blocking on postcode, then scoring name similarity (Jaro-Winkler ≥ 0.9) and matching normalised phone. Only pairs above threshold with a matching address are auto-merged; the rest go to a review queue. The team records 12,000 auto-merges and 1,100 reviewed merges, then samples 200 merges to estimate the false-positive rate before publishing.
Traps
Reporting 99% completeness as 99% accuracy; imputing skewed numeric data with the mean; merging without measuring false positives; fixing the report instead of the source; treating a one-off cleanup as a control.