Key takeaways
- Cleansing fixes or flags known data defects while preserving meaning.
- Safe formatting changes differ from uncertain changes to identity or facts.
- Keep an original, a transformation record and a route for unresolved cases.
Overview
Cleaning can address malformed values, duplicates, inconsistent formats and obsolete records. Some issues are safe to fix automatically, such as surrounding whitespace; others require evidence, such as replacing a company domain. Preserve an original copy and a change log when transformations can lose information. A clean-looking file is not necessarily a correct dataset.
How it works
Profile the data and identify specific defects and required formats.
Apply reversible transformations and route ambiguous cases to review.
Validate the output and record changes, exclusions and unresolved issues.
Profile the dataset before changing it
Inspect field types, missing values, unusual categories and repeated identifiers. A column labeled company may contain legal names, domains and free-text notes. Applying a uniform transformation before understanding that mixture can damage useful data. Start with a small profile and examples of each defect category rather than a broad instruction to clean everything.
Define the destination requirements as well. A valid source value may need a different representation for a CRM import, while a destination-required field may genuinely be absent. Formatting and missing evidence need different remedies. Inventing a plausible value to satisfy an import requirement makes the file look cleaner while reducing its reliability.
| Defect | Reasonable action | Preserve or review |
|---|---|---|
| Surrounding whitespace | Trim under a documented rule | Original input where an audit is needed |
| Inconsistent country labels | Map to an explicit standard | Unknown or ambiguous labels |
| Conflicting company domains | Investigate the entity match | Both candidates and their evidence |
| Duplicate person records | Review and merge relationships | History, ownership and suppression status |
Separate deterministic repairs from judgments
Removing accidental whitespace is usually different from deciding that two similar company names represent the same organization. Keep automatic transformations narrow and route uncertain factual changes for review. If a rule changes meaning, it needs stronger evidence than a rule that changes presentation.
Use a reversible workflow where possible: retain the original file, write transformed output separately and log changed fields with the applied rule. For large jobs, inspect a representative sample before writing into a live destination. Reversibility also makes it possible to compare alternative rules without losing the starting point.
Validate what survived the cleanup
Compare row counts, unique identifiers, relationship counts and required fields before and after. A lower row count may be expected after deduplication, but every removed row should have a reason. Check non-English names, leading zeros and quoted CSV fields because simplistic transformations can corrupt values that were valid to begin with.
In an illustrative cleanup of 1,000 records, 120 formatting fixes, 25 merged duplicates and 15 unresolved identities are different outcomes. Report them separately and confirm the destination accepted the intended records. Then repair the upstream form or integration that creates the recurring defect so the next import does not require the same manual cleanup.
What this looks like in practice
A CSV cleaner trims spaces, standardizes country codes and flags invalid email syntax. It does not invent missing addresses or claim that syntactically valid emails have working mailboxes.
Examples explain the concept; they are not reported customer results.What to check
Check records changed, false corrections, retained originals and downstream acceptance. Review destructive operations such as merging or deleting separately.
Common mistake
Automatically deleting every row with a blank optional field or rewriting a name because it does not match a narrow formatting assumption.
Data cleansing vs. Data normalization
Normalization makes representations consistent. Cleansing is broader and can also address invalid values, duplicates and other defects. Normalization alone does not establish factual accuracy.
Read the Data normalization definition →Questions answered
What is Data cleansing?
Data cleansing identifies and corrects, standardizes, flags or removes data problems so records can be used reliably for a defined purpose.
Should cleaning remove incomplete records?
Only when the missing information makes the record unusable for the stated purpose. Flagging or enriching it may be more appropriate than deletion.
Can cleaning verify an email address?
Syntax checks can detect some defects, but mailbox verification is a separate operation. Keep those statuses distinct in the output.
Can cleansing establish that an email address works?
It can remove formatting defects or reject invalid syntax, but mailbox status requires a separate verification process and still has limits. A clean-looking address can be obsolete, catch-all or associated with the wrong person. Keep syntax validity and email-verification outcomes distinct.
Should names be forced into title case?
Not automatically. Personal and company names can contain intentional capitalization, particles and non-English conventions. Preserve the original and apply display rules cautiously. A cosmetic transformation should not become the authoritative identity value or erase distinctions needed for matching.
References and further reading
Primary documentation and source material for this topic. Sources checked September 14, 2026; provider requirements can change.
- What is data quality? ↗IBM
- RFC 4180: Comma-separated values ↗IETF / RFC Editor
Continue reading on the blog
Explore all articles and guides →Put the concept to work.
Explore the relevant AstroFabric workflow and see how the pieces connect.
Help keep this guide useful. Suggest a correction or browse the full glossary.