Data Quality, Cleaning, and Validation: find, fix, prove
What this domain tests
Data Quality, Cleaning, and Validation tests whether you can find defects, fix them defensibly, and prove the fix. Expect questions on quality dimensions, validation rules, null handling, deduplication, and auditability rather than one-off spreadsheet tricks.
Core rules
- Profile before you clean: count nulls, distinct values, min/max, pattern matches, and cross-field violations per column.
- Separate validity from accuracy. A value can match a regex and still be wrong; accuracy needs an external source or business rule.
- Treat missing data explicitly. Distinguish unknown, not applicable, and zero; do not coerce nulls to 0 or drop rows silently.
- Validate at ingestion with schema and type checks; quarantine bad records with the rejection reason instead of deleting them.
- Normalize with documented rules: trim whitespace, standardize case, country and currency codes, and date formats to ISO 8601.
- Deduplicate with a defined match key and threshold; keep a survivorship rule for which record wins.
- Record before/after metrics and keep the raw layer immutable so every transformation is reproducible.
Worked example
A customer table has 100,000 emails. You trim whitespace and lowercase, then validate with a conservative pattern and a domain check. 4,000 fail. You do not delete them: you write them to a quarantine table with reason codes (syntax, disposable domain, missing @), then compute a match rate against the CRM. Reasoning: lowercasing is safe for the domain part but can change case-sensitive local parts in rare systems, so document it; a failed syntax check is a validity defect, not proof the customer is fake; the quarantine preserves the ability to recover records and audit the cleaning rate. You report completeness as non-null valid emails divided by total rows and trend it weekly.
Traps that cost marks
- Dropping null rows and reporting the remaining sample as representative.
- Mean-imputing a skewed numeric field without noting the distortion.
- Over-aggressive fuzzy deduplication that merges distinct people.
- Treating a passed regex as proof of accuracy.
- Cleaning in place with no raw copy or change log.