Steps In Data Cleaning

In the previous article, you have learned about dirty data and issues with data cleaning. Keeping that in mind, we introduce steps in data cleaning.

The data cleaning steps involve data analysis , creating and applying transformation rules, and back-flow.

Step 1: Data Analysis

Before you clean the data, you must analyze it using the definition of a dirty data. The data that has no common standards, missing data, duplicate data, factually incorrect, and contains many inconsistencies such as inconsistent associations, semantic inconsistencies, and referential inconsistencies are dirty data.

Therefore, identifying such data is the first step towards cleaning the data set. Also, you must obtain the metadata.

Step 2: Create Transformation Rules

The transformation rules can transform data from its “dirty” form to the required “clean” form. The transformation can be applied either at the schema level or data level.

The schema level transformation is necessary because of poor schema design of the databases and multiple sources of data. Every database has its own schema structure and requires that it must be optimized.

The data set may also has missing and duplicate data, and incorrect aggregated values. Therefore, the transformation rule for both schema and data level depends on the source of data. Is the data from single source or multiple sources ?

Step 3: Rule Verification

Once the rules are in place, you must verify that it works by applying it on test data sets.

Step 4: Transformation

Once you are satisfied with the test results, apply the transformation rules on the actual data set.

Step 5: Backflow

Re-populate the data sources with cleaned data is called back-flow.

Data Analysis Techniques

There are many data analysis techniques used to find dirtiness in the data. See the table below.

Problems To Be DetectedMeta-data Used
Illegal values(max, min), (mean, deviation), cardinality
Spelling MistakesHashing, N-gram outliers
Lack of StandardsColumn comparison ( compare value sets from given
column across tables )
Duplicate and
Missing Values
Compare cardinality with rows, detect nulls, use rules to predict incorrect or missing values.

In the next article, we will discuss about transformation algorithms that remove the dirtiness of data.