Missing values
Profile missingness by field, time, source, and segment before deciding what to do. Missing data can represent a collection failure, a legitimate not-applicable case, or a meaningful business pattern.
- Measure the percentage and distribution of missing values.
- Determine whether the field is required for the analysis.
- Document exclusions, imputations, and their likely effect.
Start with data profiling
Before changing values, profile row counts, column types, distinct values, missingness, distributions, minimums, maximums, and key uniqueness. Compare these results by source and time period to identify where a problem began.
Duplicates
First define the expected unique key. Distinguish exact duplicate rows from repeated business events, and investigate whether duplication comes from ingestion, joins, retries, or source-system behavior.
Outliers
Do not remove an extreme value only because it looks unusual. Check units, source records, business events, and distribution by segment. Genuine extremes may be the most important part of the analysis.
Inconsistent formats
Standardize dates, time zones, capitalization, whitespace, category labels, units, and identifier formats through explicit transformations. Preserve the original field when traceability matters.
Incorrect data types and parsing
Values that look numeric or date-like may contain currency symbols, locale-specific separators, mixed formats, or invalid characters. Parse explicitly, track failed conversions, and avoid silently turning invalid values into NULL.
Joining data safely
Many apparent cleaning problems are actually join problems. Confirm key uniqueness, normalization rules, and expected cardinality before merging tables. Measure unmatched records and investigate whether missing matches are valid or indicate inconsistent identifiers.
Data validation
Validate row counts, uniqueness, required fields, accepted values, numeric ranges, referential integrity, and reconciled totals. Automated checks are valuable, but they should reflect real business rules.
Reconciliation and before-after checks
Compare totals, record counts, distinct entities, and important distributions before and after cleaning. A transformation can be technically successful while changing a business metric unexpectedly.
- Record how many rows were changed, removed, or flagged.
- Compare key metrics before and after each major rule.
- Manually inspect a small sample of corrected and rejected records.
Building a repeatable cleaning workflow
Separate raw, cleaned, and reporting-ready layers. Use version-controlled SQL or code where possible, make rules deterministic, log exceptions, and keep a path back to the original record. Manual edits should be limited and documented.
Documentation
Record the issue, source, affected period, rule applied, number of changed records, owner, and validation result. Good documentation allows another analyst to reproduce and challenge the decision.
Communicating data quality issues
State the impact in business terms: which metrics or decisions are affected, the direction and size of uncertainty, the temporary workaround, and who is resolving the source problem. Avoid hiding limitations in technical notes.