What is Data Cleansing?
What does data cleansing mean?
As a business grows and matures, the size, number, formats, and types of its data assets change along with it. Evolutions in payroll systems, new network hardware and software, emerging supply-chain technologies, and the like can all create the need to migrate, merge, and combine data from multiple sources. “Dirty” data — data that contains redundancies, includes duplicate records, is missing information, or has been otherwise corrupted in the process of being imported or merged — is one inevitable result. Data transformation, which involves “massaging” data to make its fields and formats conform to those of its destination, can also be the source of hair pulling and sleepless nights.
The art and science of handling these odious tasks is called “data cleansing.”
Clean up your dirty data
The goal of data cleansing is to improve data quality and utility by catching and correcting errors before it is transferred to a target database or data warehouse. Manual data cleansing may or may not be realistic, depending on the amount of data and number of data sources your company has. There are data cleansing tools designed to take some of the difficulty out of the process.
Regardless of the methodology, data cleansing presents a handful of challenges, such as correcting mismatches, ensuring that columns are in the same order, and checking that data (such as date or currency) is in the same format. Depending on the situation, other difficulties may include enriching data with supplementary information on the fly, revising or updating schema, and detecting errors. These data discrepancies may have originated from human error, aging (data such as contact information degrades over time), omissions due to optional fields in forms, or merge errors.
Both manual and automatic data cleansing execute the same basic steps, in varying order:
- Import data via API or in .csv (or another delimited text format).
- Format data to match the destination database.
- Re-create missing data, wherever possible.
- Correct errors, such as spelling.
- Reorder columns and rows to match the target database.
- Compare and delete duplicate records.
- Enrich data by merging in additional information (such as adding data from purchased marketing and sales databases), if desired.
