Cleaning data at scale without losing signal
Profiling, standardisation, layered deduplication, survivorship and validation tests that raise data quality without quietly discarding the records you need.
Data cleaning has a reputation as tedious janitorial work. In reality it is one of the highest-leverage — and highest-risk — steps in any data pipeline. Clean too little and duplicates, typos and stale records poison your analytics and models. Clean too aggressively and you silently throw away exactly the records that mattered.
Here is how we approach cleaning large business datasets so quality goes up without signal going down.
Profile before you touch anything
Every cleaning project should start with a profile of the raw data: row counts, null rates per column, distinct values, value-length distributions, pattern frequencies and obvious outliers. Profiling tells you what is actually wrong, rather than what you assume is wrong — and gives you a baseline to compare against after each step.
- Which columns are mostly empty, and is that expected?
- Which free-text fields should really be categories?
- Which identifiers repeat when they should be unique?
- Which values fall outside plausible ranges?
Standardise first, deduplicate second
Deduplication on unstandardised data misses most duplicates. "ACME Pvt. Ltd.", "Acme Private Limited" and "acme pvt ltd" will not match until you normalise them. A good standardisation pass covers:
| Field | Typical standardisation |
|---|---|
| Company names | Case folding, legal-suffix normalisation, punctuation removal (kept in a separate match key) |
| Phone numbers | E.164 format with country code |
| Addresses | Parsed components, canonical city and state names, PIN code validation |
| Websites and emails | Lower-case domains, stripped tracking parameters, root domain extraction |
| Dates | ISO 8601 with explicit time zone |
Keep the original value alongside the standardised one. You will need it for audit trails and for debugging bad transformations.
Deduplicate with layers, not a single rule
No single matching rule is both precise and complete. We combine layers:
- Exact matches on strong identifiers — registration numbers, verified domains.
- Deterministic rules on combinations of standardised fields — name key plus PIN code plus phone.
- Fuzzy matching with similarity scores for the remaining candidates.
- Human review for the uncertain middle band.
Blocking — only comparing records that share a cheap key, such as the first letters of a name or a PIN code — keeps fuzzy matching tractable on millions of rows.
Merge, don't just delete
When two records are duplicates, the less complete one often holds something the other lacks: a newer phone number, a secondary location, an industry code. Survivorship rules decide which value wins for each field — most recent, most trusted source, most complete — so the merged "golden record" is better than either input.
Watch the false mergesA false merge (two different companies combined) is usually more damaging than a missed duplicate. Tune thresholds to favour precision, and route borderline pairs to review.
Outliers are not always errors
An automated rule that drops "outliers" can delete your most valuable records: the unusually large customer, the rare fraud pattern, the new market that does not look like the old ones. Flag outliers for review instead of deleting them, and ask whether an extreme value is implausible or merely unusual.
Validate with tests, not hope
Treat data quality rules like software tests and run them on every pipeline run:
- uniqueness of primary keys;
- non-null constraints on required fields;
- accepted values for categorical fields;
- referential integrity between tables;
- row-count and distribution checks against the previous run, with alerts on sudden shifts.
Measure what cleaning changed
After cleaning, compare against your initial profile. How many records were merged, corrected, flagged or removed — and why? Sample removed records by hand. If a step removed 8% of rows when you expected 1%, stop and investigate before shipping.
Good data cleaning is reversible, explainable and measured. Build it that way and you get the best of both worlds: datasets that are consistent enough to trust and complete enough to be useful.
