Data cleansing, also known as data cleaning or data scrubbing, is the process of identifying and addressing the inconsistencies, inaccuracies, or other erroneous aspects of the data set. These may be because of human errors in data entry, system failures, corruption during transmission, or any other cause.
The Need for Data Cleansing
Data cleansing is crucial because it significantly improves the quality of the data, and high-quality data drives better insights. To maintain a valid and trustworthy data pipeline, data engineers need to know how to clean data, spotting the inconsistencies and eliminating or fixing them.
Data Cleansing Techniques and When to Use Them
Here are the primary data cleansing techniques that candidates should be comfortable with:
- Data Validation: This involves creating defined sets of rules to check and validate the data against it. For instance, a rule could identify an error if the date format in the ‘Date of Birth’ field is inconsistent.
- Missing Data Handling: Not all missing data need to be addressed, but in some cases, missing data could lead to inaccurate analysis or reports. In such cases, you may either decide to ignore it, use a standard value to replace the missing data, or use statistical methods to estimate a suitable replacement value.
- Outlier Detection: This process involves identifying values that significantly deviate from the overall pattern in a data set. These values could skew statistical analyses and could lead to inaccurate results.
- Deduplication: This is the process of identifying and removing duplicate entries in the dataset. Deduplication is essential to avoid overcounting or over-representing certain data points in the data set.
Depending on the nature of the data and the use-case, one or more data cleansing techniques may be suitable:
| Use-Case | Appropriate data cleansing technique(s) |
|---|---|
| Real-time transaction data | Data Validation, Outlier Detection |
| Historical customer data for targeted marketing | Deduplication, Missing Data Handling |
| Financial reporting | Data Validation, Deduplication |
How to Clean Data in AWS
AWS provides several tools and services which make it possible to automate the process of data cleansing:
- AWS Glue: A fully managed extract, transform, and load (ETL) service for cleaning and preparing data for analytics. AWS Glue can auto-generate Python or Scala code to extract, transform, flatten, and load your source data into your target data store.
- AWS Data Pipeline: A web service for orchestrating and automating the movement and transformation of data between different AWS services and on-premises data sources.
- Amazon QuickSight: This is a scalable, serverless, embeddable, machine learning-powered business intelligence (BI) service for AWS. It is used for creating and publishing interactive dashboards which include ML insights.
In conclusion, data cleansing is a critical skill for aspiring AWS Certified Data Engineers. It involves understanding the various techniques and the appropriate scenarios in which they are applied. AWS provides a range of tools and services that assist in automating the data cleansing for more efficient and accurate data analyses and reporting. Hence, understanding these aspects help considerably in preparing to ace the AWS Certified Data Engineer – Associate (DEA-C01) exam.
Practice Test
True or False? Data cleansing is the process of detecting and correcting (or removing) corrupt or inaccurate records from a record set, table, or database.
- True
- False
Answer: True.
Explanation: Data cleansing is indeed the process of identifying and rectifying or eliminating corrupt or incorrect records from a dataset or database.
In AWS, which tool can be used for data cleansing?
- a. AWS Glue.
- b. AWS Lambda.
- c. AWS S
- d. AWS EC
Answer: a. AWS Glue.
Explanation: AWS Glue is a fully managed ETL (extract, transform, and load) service that makes it simple to prepare and load data for analytics. It includes inbuilt data cleaning features.
True or False? The transformation stage of ETL does not include data cleansing.
- True
- False
Answer: False.
Explanation: The transformation stage in ETL implies converting the data into a format that can be appropriately and accurately analyzed. Often, this process encompasses data clean-up activities.
Multiple Select: What are some common data cleansing techniques?
- a. Data profiling.
- b. Data standardization.
- c. Data matching.
- d. Encrypting data.
Answer: a. Data profiling, b. Data standardization, c. Data matching.
Explanation: Data profiling involves studying the data available and identifying errors and inconsistencies, data standardization involves cleaning up the data so it is consistent, and data matching involves identifying, linking, or merging related entries within or across sets of data.
Data cleansing should be performed:
- a. Only before analysis.
- b. Only after analysis.
- c. Both before and after analysis.
- d. None of the above.
Answer: a. Only before analysis.
Explanation: Data cleansing should be done before the analysis phase to ensure accuracy in the data analysis process.
True or False? Regular expressions can be used in data cleansing.
- True
- False
Answer: True.
Explanation: Regular expressions can be used to identify patterns in data, and can therefore be used as a data cleansing technique.
Data validation is a part of data cleansing. True or False?
- True
- False
Answer: True.
Explanation: Data validation, which is the process of checking the accuracy and quality of data, is an integral aspect of the data cleansing process.
Which of the following AWS service is not used for data cleansing?
- a. Amazon Redshift.
- b. AWS Glue.
- c. AWS S
- d. Amazon EMR.
Answer : c. AWS S
Explanation : Amazon S3 is a storage service, it is not specialized in data cleansing.
Data cleansing is always a necessary process for data preparation. True or False?
- True
- False
Answer: False.
Explanation: Not all data requires cleansing; it’s contingent on the quality of data and its purpose. However, for most data analytics, data cleansing is usually performed to increase the accuracy of analysis.
In AWS Glue, which language is usually used for transformations and cleansing?
- a. Python
- b. Scala
- c. Both a and b
- d. None of the above
Answer: c. Both a and b
Explanation: Both Python and Scala scripts are supported in AWS Glue for transformation and cleansing. Developers can choose which ever language they are more comfortable with.
Interview Questions
What is the first step to consider when applying data cleansing techniques on AWS systems?
The first step is data understanding, identify the quality of data. Check whether the data is missing or inconsistent, and what type, format, and quantity of the data you are handling.
What is AWS Glue’s role in data cleansing?
AWS Glue is a fully managed Extract, Transform, Load (ETL) service which can auto-discover and categorize data, transform it, clean it, and move it between various data stores.
For what types of data is the AWS data validation and cleansing best suited?
AWS Data Validation and Cleansing service best suits structured and semi-structured types of data such as databases, JSON, CSV, and XML files.
Why is data cleansing important in a data pipeline?
It ensures the accuracy, consistency, and reliability of the data. With clean data, you can make more accurate predictions and decisions.
Which AWS service is used for data validation?
AWS Data Pipeline can be used for data validation. It helps to check the data for completeness and also for any anomalies in the data.
What issues can be resolved using data cleansing techniques?
Data cleansing can resolve issues like duplicate entries, incomplete or outdated data, irrelevant data, and inaccuracies in the data.
What is the relevance of data cleansing in data engineering?
Data cleansing improves the quality of the data and increases overall productivity. By eliminating inaccuracies and inconsistencies, data engineers can make more precise decisions.
When should a data engineer use the Dataset Transformations feature of the Amazon S3 Select platform?
A data engineer should apply them whenever they need to run big data workloads, filter the contents of an S3 object, or simplify the process of pulling object content into AWS for querying.
How does Amazon QuickSight help in data cleansing?
Amazon QuickSight automatically infers schema and builds data preparation scripts that clean and shape data, making it ready for analysis.
Why is it important to monitor log files while performing data cleansing activities?
Monitoring log files can help you track the progress of data cleaning. It helps detect any errors or inconsistencies during the cleaning process.
What process does the AWS Lake Formation use to cleanse and transform data?
AWS Lake Formation uses AWS Glue and AWS Glue DataBrew. AWS Glue DataBrew adds visual data preparation (prep) for cleaning and normalizing data.
What is the role of AWS Lambda in data cleansing?
AWS Lambda can be used to run code without provisioning or managing servers, and it automatically scales to support data cleaning processes.
What AWS tool can be used to handle streaming data for real-time cleansing?
Amazon Kinesis can be used to handle streaming data for real-time cleansing, as it can easily collect, process, and analyze real-time, streaming data.
How does AWS enable industry-specific data cleaning solutions?
AWS provides marketplace solutions like Talend Data Quality which provides industry-leading data cleaning, profiling, and masking capabilities.
How does ETL (Extract, Transform, Load) process assist in data cleansing?
During the Transform step of an ETL process, data cleansing can be performed to improve the quality and intelligence of data by removing errors, correcting or dropping the corrupt or inaccurate records.
extutorials.com