In data preparation, what is data cleaning (data cleansing), and what are common techniques used to perform it before loading into a data warehouse?

Introduction / Context:
Data cleaning, also called data cleansing, is a critical step in any data integration or data warehousing project. Poor quality data leads to misleading reports, incorrect analytics, and a lack of trust in the system. Interviewers ask about data cleaning to see whether you understand how to improve data quality systematically rather than just moving raw data from source to target.

Given Data / Assumptions:

• Source systems may contain missing values, invalid codes, duplicates, and inconsistent formats.
• The data warehouse is expected to provide reliable, consistent, and accurate information.
• ETL or data integration processes can transform data during loading.
• Business rules and validation rules are available or can be defined.

Concept / Approach:
Data cleaning involves identifying problems in the data and applying rules or transformations to fix them. Typical issues include missing values, invalid date formats, inconsistent codes, duplicate records, and outliers that do not make business sense. Techniques include using validation rules, reference and lookup tables, deduplication algorithms, standardizing formats, and sometimes contacting data owners for clarification. The goal is to improve the quality, consistency, and usability of data before it becomes part of the warehouse.

Step-by-Step Solution:
Step 1: Define data cleaning as the process of detecting, correcting, or removing inaccurate, incomplete, or inconsistent data from datasets. Step 2: Explain that common steps include validating data types and ranges, such as ensuring dates are valid and numeric fields are within expected limits. Step 3: Describe how missing values can be handled by imputation (for example using averages or default values), by flagging records, or by excluding them based on business rules. Step 4: Discuss deduplication, where duplicate customer or transaction records are identified and merged using matching rules or keys. Step 5: Mention standardization, where formats like addresses, phone numbers, and codes are converted into consistent, canonical forms to support accurate aggregation and joining.

Verification / Alternative check:
Data quality reports generated before and after cleaning often show improvements in metrics such as completeness, uniqueness, validity, and consistency. For example, the percentage of records with missing customer emails might decrease after implementing cleaning rules, and the number of duplicate customer IDs may drop significantly. Business stakeholders usually notice fewer anomalies in reports, such as negative quantities or impossible dates, confirming the value of data cleaning activities.

Why Other Options Are Wrong:
Option B confuses data cleaning with encryption, which protects confidentiality but does not improve data quality. Option C equates cleaning with deleting history, which can actually harm analytics if done incorrectly. Option D reduces cleaning to renaming columns; while naming can improve usability, it does not address underlying data errors or inconsistencies.

Common Pitfalls:
A common pitfall is performing data cleaning ad hoc inside individual reports instead of centralizing it in the ETL layer, which leads to inconsistent results across reports. Another mistake is changing data without documenting the rules, making it difficult to explain how numbers are derived. Effective data cleaning involves collaboration with business experts, clear documentation, automated rules, and ongoing monitoring of data quality metrics.

Final Answer:
Data cleaning is the process of finding and correcting errors, inconsistencies, and missing values using validation, deduplication, standardization, and similar techniques so that data loaded into the warehouse is accurate and reliable.