Data cleaning fixes problems after they occur. Quality assurance (QA) is about preventing problems from occurring in the first place — and catching them early when they do.
Validation Rules
Validation rules define what valid data looks like. They can be applied at data entry (preventing bad data from being entered) or at processing time (flagging records that don't meet standards).
Common validation rules include:
- Format checks — postal codes must match A1A 1A1 pattern
- Range checks — age must be between 0 and 120
- Referential integrity — province code must exist in the province reference table
- Completeness checks — required fields must not be null
- Uniqueness checks — customer ID must be unique
- Cross-field checks — end date must be after start date
Data Profiling
Regular data profiling — systematically examining a dataset to understand its content and quality — helps you catch problems early. Profile key metrics: row counts, null counts, distinct value counts, min/max values, and value distributions.
Manual QA doesn't scale. Build automated checks into your data pipelines that run every time data is loaded or updated. Alert the data owner when checks fail rather than letting bad data silently accumulate.
Data Quality Scorecards
A data quality scorecard tracks quality metrics over time for a dataset or system. It makes quality visible and creates accountability. Metrics might include: percentage of records with complete required fields, percentage passing validation rules, and age of the most recent update.
Put it all together in Building a Data Refinery.