0012 How to Cleanse the data

Clean the data before it reaches the target

This example shows how to reject bad rows first, then fix the values that can be safely corrected. That order matters. Loading questionable data and hoping reports will sort it out later is how small errors become monthly meetings.

Typical data quality problems

The source file contains several common issues:

• Inconsistent values: US and USA mean the same thing to people, but they are different values to a database.
• Missing values: UK and Germany have missing values that should be rejected before loading.
• Data entry errors: Spain and SPain are not the same value unless you clean them.
• Duplicate keys: ORDER_ID must stay unique.
• Inconsistent date formats: Dates from different countries often arrive in different formats.
• Non-numeric characters inside numeric fields: Currency symbols, commas, and spaces need removing before numeric loading.
• Leading and trailing spaces: Invisible, annoying, and usually found only after someone asks why a lookup failed.

Data cleansing example

Use the sample transformation to see the validation and cleansing steps working together.

• Download and install Advanced ETL Processor [Link]
• Download and unzip the example [Link]
• Create a new transformation and open the .ats file.

• Double-click the Reader object and amend the source file path.
• Double-click the Writer object and amend the target file path.
• Run the transformation by pressing the green arrow.

How validation works

The Data Reader loads the Excel file into memory. The validator then rejects rows where the Country Name field is empty. Those rows should not continue into the cleansing stage because there is not enough reliable data to repair them.

How cleansing works

After the bad records are rejected, the transformer cleans the remaining rows.

• The Delete Characters transformation function removes dollar signs, pound signs, commas, and spaces from the Amount field.
• The Date Format transformation function reformats the Order Date field into standard ODBC format.
• The Lookup transformation function corrects Country field values.