Standardization vsTransformation

These are one of the most confusing topics. We all mix-up data quality fixes and standardization of data for better processing with transforming data for better processing and serving.

Let’s decode it…..

We all know, in Data Warehouse or when we pull data from an OLTP system, we first store it in a LANDING ZONE somewhere which normally is the true copy of source systems. Then we move it to STAGING ZONE before we transform it into a Unified Data Warehouse (Bill Inmon way) or in a Dimensional Model (Ralph Kimble way).

Tables in LANDING and STANGING ZONES, both keep the true DATA MODEL from source systems but not the CONDITION of DATA.

WHAT DOES ABOVE STATEMENT MEANS, not the CONDITION of DATA?

Data Standardization

Does standardizing the condition of data not equal to transformation? NOPS, ITS NOT. Data Standardization can have unlimited scenarios, few are shared below e.g.,

• Standardization of Dates:
  • Converting different dates formats from different source systems e.g., DD/MM/YY, MM/DD/YY, YY/MM/DD, YY/DD/MM etc., into one format e.g., DD/MM/YYYY.
  • Convert empty date rows into e.g., 01/01/9999.
• Convert special characters to NA.
• Convert empty fields to NA.
• Profile the fields, if data type is string or varchar but data is only number than convert it into digit or number as running queries on digit or numbers are way faster.
• If the field contains codes only with only character, then make it CHAR and if it only contains numbers then make it DIGIT.
• If the full name of a person is in one field, convert it in First, Middle and Last Name.
• Fix the spelling mistakes.
• Fix the up and lower cases e.g., if a country is Australia and in data its coming as Aus, Aust, australia, Aussie, aus etc., than convert it in one format e.g., Australia.

Note, there can be unlimited scenarios to standardize the data, so this exercise is an ongoing exercise.

As mentioned above, at the same time when few data folks mix data standardization with data transformation, there are few who also mix converting one file format to another as transformation 😊. I won’t go into details for this scenario…..

Data Transformation

In Data World, Data Transformation is about converting one data model to another data model e.g., when we convert source OLTP data model to target Data Warehousing Model or in Semantic Layer or in Cube format for DSS. These data model transformations are done for, sometimes for better processing and sometimes for better serving. Serving always supersedes as it serves the end users, the faster the results are, happier the customer is.

Impact to Compute Power:

• Data Transformation take huge computer power when transforming data from STAGING to TRANSFORMED ZONE
• Data Standardization take less compute power from LANDING to STAGING

Please note, if Data Standardization is done before Data Transformation, it can reduce computer power by 30%-50% computer power for Data Transformation.