3.3 Data Transformation and Cleansing

Data transformation and cleansing are vital steps in the ETL process, ensuring data quality and usability for business intelligence. These processes convert data from various sources into a unified format, resolving issues like missing values, outliers, and inconsistencies.

Techniques like data type conversion, normalization, and aggregation transform data for analysis. Cleansing strategies include handling missing values, treating outliers, and resolving inconsistencies. These steps are crucial for accurate decision-making based on clean, trustworthy data.

Data Transformation and Cleansing in ETL

Purpose of data transformation and cleansing

Top images from around the web for Purpose of data transformation and cleansing

• 

  Informatica 9, a complete data integration platform | Dataprix translations View original

  Is this image relevant?

• 

  sql server - Best way to graph an ETL data flow - Stack Overflow View original

  Is this image relevant?

• 

  The Effects of Using Business Intelligence Systems on an Excellence Management and Decision ... View original

  Is this image relevant?

• 

  Informatica 9, a complete data integration platform | Dataprix translations View original

  Is this image relevant?

• 

  sql server - Best way to graph an ETL data flow - Stack Overflow View original

  Is this image relevant?

1 of 3

Top images from around the web for Purpose of data transformation and cleansing

• 

  Informatica 9, a complete data integration platform | Dataprix translations View original

  Is this image relevant?

• 

  sql server - Best way to graph an ETL data flow - Stack Overflow View original

  Is this image relevant?

• 

  The Effects of Using Business Intelligence Systems on an Excellence Management and Decision ... View original

  Is this image relevant?

• 

  Informatica 9, a complete data integration platform | Dataprix translations View original

  Is this image relevant?

• 

  sql server - Best way to graph an ETL data flow - Stack Overflow View original

  Is this image relevant?

1 of 3

• Critical steps in the Extract, Transform, Load (ETL) process ensure data quality, consistency, and usability for business intelligence and analytics
• Data transformation converts and standardizes data from various sources (databases, files, APIs) into a unified format enabling seamless integration and analysis
• Data cleansing identifies and resolves data quality issues (missing values, outliers, inconsistencies) improving data accuracy, completeness, and reliability for enhanced decision-making and reporting based on clean and trustworthy data

Impact of data quality issues

• Missing values occur when records or fields lack information leading to inaccurate analysis and skewed results (null values, blank cells)
• Outliers are data points that significantly deviate from the normal range or distribution (extreme values, anomalies) and may distort statistical measures and affect decision-making
• Inconsistencies arise from contradictory or conflicting data across different sources or within the same dataset (formatting variations, data entry errors) hindering data integration and leading to confusion and misinterpretation
• Duplicates are redundant records or entries representing the same entity (repeated customer records, identical transactions) inflating data volume and leading to incorrect aggregations and analyses
• Incorrect data types occur when there are mismatched or inappropriate data types for specific fields (storing dates as text, numbers as strings) causing errors in calculations, filtering, and reporting

Techniques for data transformation

• Data type conversion ensures consistency and compatibility by converting data types (string to numeric, date/time formats)
• Data normalization restructures data to eliminate redundancy and improve data integrity by splitting data into separate tables based on dependencies and relationships (1NF, 2NF, 3NF)
• Data aggregation combines and summarizes data at different levels of granularity enabling analysis and reporting at various dimensions (daily sales, monthly revenue, yearly growth)
• Data splitting divides data into smaller, manageable subsets based on specific criteria (customer segments, product categories) facilitating parallel processing and targeted analysis
• Data enrichment enhances data with additional information from external sources (demographic data, weather information) providing more context and insights for analysis and decision-making

Strategies for data cleansing

• Handling missing values:
  1. Deletion: Removing records with missing values
  2. Imputation: Estimating missing values based on available data using mean, median, or mode substitution or regression and machine learning techniques
• Outlier detection and treatment:
  1. Statistical methods: Z-score, interquartile range (IQR)
  2. Domain knowledge: Defining acceptable ranges based on business rules
  3. Winsorization: Replacing extreme values with the nearest valid value
• Resolving inconsistencies:
  1. Data standardization: Applying consistent formats, units, and conventions for date formats (YYYY-MM-DD), address structures (street, city, state, zip), and naming conventions (lowercase, camel case)
  2. Data validation: Enforcing predefined rules and constraints through range checks (age between 18 and 100), pattern matching (email format), and cross-field validation (end date after start date)
• Deduplication involves identifying and removing duplicate records and merging or consolidating duplicates into a single representative record (customer records, product entries)
• Data profiling analyzes data characteristics, patterns, and quality metrics helping identify and prioritize data quality issues for cleansing efforts (data distribution, value frequencies, data types)