Knowledge Hub

Data Wrangling or Data Mangling or Data Munging are used interchangeably and do the same function.

Data Wrangling is something that every ETL developer, Data Engineer, Data Analyst etc. do daily. It is something that is done before Data Mining, Data Science, Business Intelligence or Business Analytics.

In simple words, Data Wrangling is to prepare data for processing. Note, prepare data for processing, not process data. Yes, you heard it correctly, sometimes Data Wrangling techniques are used for permanent data fixes and sometimes to prepare data for specific use cases.

Let's take an example, refer to the tables above, fixing and streamlining data in fields i.e., Name, Phone, Birth Date and State will clean the data permanently which will assist in fast job processing, save analysts time for investigation etc. Secondly, you can see row#5 has been removed as, if the analysis is on Date then Year is missing from row#5 so this row will not assist in modelling.

Normally Data Cleansing and Data Enrichment are confused with each other.

‘Data Enrichment is about enhancing the data whereas Data Cleansing is about correcting the data.’

Data Cleansing has been covered in one of the previous topics, so we will focus only on Data Enrichment. As the definition explains itself, Data Enrichment is about adding more data into your first-hand data set from other internal or third-party data sources to add additional context. In this era of social media and unstructured sources, there is always additional data available which can enhance the context of your data generated from internal systems.

‘Data Enrichment must depend on Business Goals.’

For example, if a brand wants to open a new branch, it can buy geographical data from third parties. If a bank wants to offer Credit Cards deals, it can buy crediting rating data from third parties.

Please note, there can be many sources of data sets for Data Enrichment and of course not all can be Enriched in your data stores. Just like any other implementation, Data Enrichment must follow business requirements.

Data Harmonization is normally confused with Data Integration, Data Blending, Data Warehousing, Data Marts etc. Yes, all do the same steps of connecting and bring all required data from all the disparate sources in one central location, but Data Harmonization goes one step ahead.

Data Harmonization standardizes data coming from multiple source systems in one unified format, so it becomes a bridge between data and business requirement. When we say, one unified format, it means data is converted in such a way that it can become accurate for someone.

Data Harmonization can be of many types.

• Harmonizing data in one location for a specific department can be called a Data Mart.
• Defining standard naming conventions, data type, codes coming from multiple sources in one unique format can be Harmonizing data for Master Data Management.
• Gathering Metadata details of all source systems, data lakes, data warehouses, data marts up to reports and dashboards is Harmonizing data for Data Lineage.

This is a principle rather a process to manipulate data, but it holds a very significant value in terms of managing and securing data.

Most of us mix Critical Data Elements with elements or data points identified during Data Quality exercise. Though those are also critical but when we talk about ‘Critical Data Element’ in Data Strategy, we categorize those elements which can have negative impact on the organization.

Critical Data Element can be any regulatory requirements, any legal or compliance requirements, those data elements which can negatively impact the image of company in general public, those data elements which if are found inaccurate make stakeholders’ loss their confidence on data.

Personal Information of customers is one of the most critical data elements in most of the organization. Credit or Debit Card details in the Banking sector are critical. Location details in Telecommunication are critical etc.

Data Anomalies are about any kind of unusual behavior which can degrade the quality of data. Please note, data quality is not only about dirty data but also about not being able to insert, update and delete required data.

Type of Data Anomalies

• Insertion: If one is unable to enter quality data in database.
• Update: If one is unable to update relevant data or able to update irrelevant data.
• Delete: If one is unable to delete relevant data or able to delete irrelevant data.
• Redundancy: If there are duplicate records in a table.

Data Normalization and database constraints play a vibrant role to make sure data anomalies doesn’t occur. To start with, let’s say there is a table with employee ID, name, department, and salary details. First wrong approach we can noticed i.e., this table is in de-normalized form, by right there should be separate tables for department and salary data using normalization process. Normalization and Denormalization are covered, in detail, in data modelling topic.

Data Validation is about verifying or validating the correctness and quality of data against the source. In other words, reconciliation of imported and processed data with its source data.

Data Validation can be done before pulling data and as well after data is processed but must be done before passing data to end users.

Sometime, data changes its shape after transformation from source to target, in this situation Data Validation is conducted against business rules implemented during transformation.

Types of Data Validation

• Constraints Validation checks the accuracy of all types of constraints like primary key, foreign keys, unique, not null, values check and query performance constraint.
• Structured Validation checks the structures of tables and columns.
• Data Range Validation checks the allowable data range to be allowed for processing e.g., if only latest data is allowed to be process the only latest data should get processed.
• Code Validation checks the quality of the syntax. There are ways to write quality and optimized code.
• Data Type Validation checks the type of data to be process e.g., if a data type of a column is Date then only data format with date values should be processed.

Data Curation is about generating, managing, and maintaining data for future use.

‘Curation comes from the Latin word curae, meaning "care for" -- a meaning it still has today, including in relation to data.’

Data Curation is about taking care of the data from start till end. From extracting till loading. From management to governance. From raw till transformed. From generation till secured and private storage. From open till encryption. As the name says, CARE FOR. Data Curation is also about taking steps to preserve data for future reference as well. Data Curation is the process of preparing data for business and for decision support systems. There is nothing complex about its definition 😊.

Steps for Data Curation

• Understanding the data
• Transformation
• Metadata Management
• Cleansing
• Reconciliation

Stages of Data Curation

• Storing --> Sharing --> Discoverable

Data Usability is the key to being a Data Driven Organization.

To better understand, let’s refer to the difference between Useful and Usable.

• Useful means e.g., a system is fulfilling the business requirements and able to perform all the operation tasks.
• Usable means e.g., how easy the system is for operational tasks i.e., how automated it is, which makes the operator’s life easy.

Let’s map it with Data World. Consider, the marketing team needs data from, let’s say, 5 source systems to send campaigns to premium bank customers. Now, dumping data from all 5 source systems at one centralized location like Data Lake might be USEFUL as all sort of data is available at one place but it’s not USABLE unless until it is not converted into a format which marketing team can easily use to send campaigns. In other words, unless data is not formatted in a Data Warehouse or a Data Mart or in a Semantic Layer, it might not be usable for relevant team.

Data Profiling is used by Data Modelers, Data Engineers, Data Governance and Data Quality team as their daily routine.

Data Profile is done using many methods to summarize data for analysis. The aim is to find out the data patterns.

For example, if you want to find out, is there a person in your customer list whose age is above 90 years? Now, one way is to go through each line of data and find out who is aged more than 90 years, or the other way is to run a DINTINCT command on age column which will give you all-unique ages from age column. Consider, if there are customers from age 21 to 100 in your database then unique values should not be more than 80.

Data Profiling is also heavily used by Data Quality teams to find out whether data is following standards defined by Data Quality teams. For example, there is Data Quality Rule i.e., all date columns in the system must follow date format as DD/MM/YYYY. Now, as data is flowing-in on daily basis and sometimes in real-time manner, Data Quality teams have automated Data Profiling functions running to run DISTINCT commands to see if there is any other date format coming in the data. Based on the above date format example, there should always be one unique row from date fields.

Data Standardization can be imposed at a data level or at a data store level.

Data Standardization means converting data into such a format that it’s accessible, useable, readable, and actionable.

Data Standardization reduces manual efforts, increase efficiency, enable segmentations, remove duplications, increase data quality, enforce data governance, increase business opportunities, improve analysis and analytics.

Steps for Data Standardization

• Know your data and what it is required for.
• Recognize data entry points.
• Outline the data standards.
• Standardize your data with the help of data scoring.

Data Standardization Approaches

• Converting all dates format into single format.
• Arranging or sorting database on requirements.
• Converting unstructured data set into structured data set e.g., video, audio, image, pdf, email, word etc. in one readable format.
• Converting multiple semi-structured formats in one format e.g., JSON and XML in one readable format.
• Having Gold Records using MDM system.
• Via Data Profiling process
• Via Data Transformation process
• Via Data Virtualization environment
• Data pipeline processing at one given time i.e., real-time, daily, monthly etc.
• Exposing data in the shape of Reports and Dashboards.
• Converting all data from different language to one language e.g., English.
• Bring data from all relevant source systems into one Unified Data Model in a Data Warehouse or in Star or Snowflake Schema or in Semantic Layer or having a multi-dimensional Cube.
• Enrichment of metadata of the data and Creation "Quality gates" that indicate freshness and anomaly.