Profile before changing the data
Profiling measures what is present: types, counts, ranges, patterns, duplicates, and missing values. These observations provide evidence for cleaning rules and help identify whether a problem is isolated or systematic.
- Confirm expected data types.
- Measure completeness and uniqueness.
- Inspect ranges, patterns, and unusual values.
Choose a defensible quality treatment
A missing value can be flagged, removed, or estimated depending on its meaning and the analytical goal. Duplicate records require matching rules, while inconsistent formats may require standardization. Every treatment should be documented.
- Do not automatically convert every missing value to zero.
- Preserve original values when auditability matters.
- Validate the result after each transformation stage.
Integrate without changing meaning
Combining sources requires compatible keys, units, granularity, and definitions. Two fields with similar names may represent different business concepts, while two differently named fields may represent the same concept.
- Confirm join keys and expected row counts.
- Standardize units before comparison.
- Check for duplicated rows after joins and appends.
Quick Reference
Important Terms
- Data profiling
- A structured examination of values, patterns, types, and quality conditions in a dataset.
- Deduplication
- The identification and resolution of records that refer to the same entity or event more than once.
- Imputation
- The replacement of a missing observation with an estimate selected through a documented method.
- Normalization
- A transformation that places values or structures into a consistent form for storage or comparison.
- Validation rule
- A test that determines whether data satisfies an expected format, range, relationship, or business condition.
Original Public Practice
Check Your Understanding
1. An analyst wants to measure null rates and value ranges before writing cleaning rules. What should be performed first?
Show answer and explanation
Answer: Data profiling.
Profiling documents the current condition of the data so cleaning decisions are based on evidence.
2. Two customer systems contain repeated records for the same individuals. Which task most directly addresses the problem?
Show answer and explanation
Answer: Deduplication.
Deduplication uses matching and resolution rules to handle multiple representations of the same entity.
3. Why should row counts be checked after joining two tables?
Show answer and explanation
Answer: Unexpected many-to-many matches can multiply rows and distort later results.
Comparing expected and actual row counts is a practical validation check after integration.