Ctrlk
For the complete documentation index, see llms.txt. This page is also available as Markdown.

Data Cleansing

Traditional data cleansing techiques ..

This workshop section focuses on demonstrating traditional data cleansing techniques using Pentaho Data Integration.

This dataset contains various issues:

  • Duplicate records (John Doe, Alice Johnson)

  • Inconsistent phone number formats

  • Inconsistent date formats

  • Missing values

  • Inconsistent address formats

  • Removing Duplicates

Your mission, should you wish to accept it .. is to build a workflow / pipeline that resolves the issues .. explain the decisions you have made and any suggest possible enhancements ..

CustomerID	FirstName	LastName	Email	                Phone	         BirthDate	Address
1	        John	        Doe	        john.doe@email.com	555-123-4567	 1985-03-15	123 Main St, City, CA, 12345
2	        Jane	        Smith	        jane.smith@email.com	(555) 987-6543	 03/22/1990	456 Elm Avenue, Town, AZ, 67890
3	        John	        Doe	        johnd@email.com	        5551234567	 1985-03-15	123 Main Street, City, CO, 12345
4	        Alice	        Johnson	        alice.j@email.com	555-555-5555	 1988-12-01	789 Oak Rd, Village, State, 54321
5	        Bob	        Williams	bob.w@email.com		                 1975-07-30	101 Pine Lane, Hamlet, State, 13579
6	        Emma	        Brown	        emma.brown@email.com	(555)246-8135	 05-19-1992	202 Cedar Blvd, Borough, NY, 24680
7	        Alice	        Johnson	        alice.johnson@email.com	555.555.5555	 12/01/1988	789 Oak Road, Village, FL, 54321
8	        Charlie	        Davis	        charlie.d@email.com	555-369-2587		        303 Maple Dr, City, State, 97531
9		                Taylor	        d.taylor@email.com	555-159-7532	 1982-09-25	404 Birch St, Town, State, 86420
10	        Grace	        Lee	        grace.lee@email.com	5557894561	 11-11-1995	505 Walnut Ave, City, State, 
...

All the workshop files: ../Databases/CRUID/Workshop - Data Cleansing

First step is to onboard the data .. currently its a CSV file - which could easily be onboarded - instead let's onboard into a table.

There's a number of databases already installed and configured, running as containers.

  1. Log on to Portainer and check the MariaDB database container is up and running.

https://localhost:9443/#!/2/docker/containerslocalhost
Link to Docker Containers in Portainer

  1. Execute the following script to create a sourceDB & targetDB databases.

'grant all' to pentaho_user & pentaho_admin with the password: 'password'.

create sourceDB & targetDB

Transformation

Currently the customer records are in CSV format. It's optional, but, let's onboard into a database table as this is a more likely scenario.

tr_onboard

  1. Drag and drop a CSV File input onto the canvas.

  2. Double-click to configure the following settings:

CSV File input - customer_data.csv

  1. Increase the varchar (length) to prevent truncation.

  2. After clicking on 'Get Fields', 'Preview' the data.

Preview data

The MariaDB jdbc database driver has been copied to the /lib directory.

Define Database Connection (MariaDB)

  1. In Spoon, click File > New > Transformation. Any one of these actions opens a new Transformation tab for you to begin designing your transformation:

    • By clicking New, then Transformation

    • By using the CTRL-N hot key

  2. From within Spoon, Select:

File > New > Database Connection The Database Connection dialog box appears.

MariaDB: sourceDB connection

  1. Enter the following details:

Section Name
Value

Connection Name

MariaDB: sourceDB

Connection Type

MySQL

Host Name

localhost

Database Name

sourceDB

User name

pentaho_admin

Password

password

  1. Click Test.

  2. Configure the Table ouput step with the following settings:

create source_customer table

  1. Click on the SQL button & Execute.

Execute SQL script

  1. Close all the windows & save / RUN transformation.

RUN Transformation

  1. Check the table has been created and populated with customer data.

souurce_customer table

Lets' breakdown the data cleansing workflows that need to be applied:

Resolve FirstName

x

x

x

Let's start with resolving the FirstName.

Luckily we access to the National Address Database (NAD), which is a aggregation of data provided by state, local, and tribal governments.

Resolve FirstName

If field value is null

The step "If field value is null" is able to replace nulls by a given value either by

  1. Processing the complete row with all fields

  2. Processing the complete row but only for specific field types (Number, String, Date etc.)

  3. Processing the complete row but only for specific fields by name

Changing a numeric field type to an empty string field type can cause an error in the subsequent steps. The error will not appear until the next step accesses that field.

Replace NULL values

Filter FN

Replacing NULL values with 'Unknown' enables the FirstName field to be filtered.

You could also modify the Filter condition to: NOT

Filter: FirstName = Unknown

Stream lookup FN

If the condition is TRUE, i.e FirstName = Unknown then send the row to Stream lookup FN.

Lookup FirstName and set as ResolvedFisrtName

Ok .. I know this is a training lab ..!

The Data grid (FisrtName) has customer data that could be coming from another system.

Fingers crossed the CustomerID references the same customer.

The key fields - hashed - ensure that the row returned 'matches' the row in the data stream.

Select values FN

As we know the FirstName = Unknown, however, based on the lookup criteria its matched Resolved FirstName = David to the record. To 'map' the ResolvedFirstName to the FirstName data stream field, simply rename ..

So .. now when you merge the record back into the 'main' stream

x

x

x

x

Last updated

Was this helpful?