🡆The “Transformation” phase (the “T” in ETL or ELT) involves a series of components and techniques to apply business rules and ensure data quality before the data is used for analysis or loaded into a target system like a data warehouse.
🡆Key components and techniques include:
🡆Data Cleansing/Cleaning: Identifying and correcting or removing errors, inconsistencies, and inaccuracies in the dataset (e.g., handling missing values, standardizing date formats, correcting typos).
🡆Data Deduplication: Identifying and removing duplicate records to ensure data accuracy and consistency.
🡆Data Formatting/Standardization: Ensuring all data conforms to a consistent format or set of standards (e.g., converting all currencies to a single standard, unifying capitalization, ensuring consistent units of measurement).

🡆Data Filtering: Selecting only relevant rows or columns based on specific criteria to focus the dataset and improve processing efficiency.

🡆Data Aggregation/Summarization: Combining data from multiple sources or records into a single summary value (e.g., calculating total monthly sales from daily transaction data).
🡆Data Joining: Merging related data from two or more tables or datasets based on a common key (e.g., combining customer information with order details using a customer ID).
🡆Data Derivation/Feature Engineering: Creating new data elements or calculated values from existing data using business rules or mathematical functions (e.g., calculating a customer’s age from their birth date, computing profit margins).
🡆Data Splitting: Dividing a single column into multiple columns (e.g., separating a full address field into street, city, state, and zip code).
🡆Data Validation: Performing checks to ensure the data is accurate and complete based on predefined rules before it is processed further.
🡆Data Normalization/Scaling: Standardizing numerical data to a common scale or range, often necessary for machine learning models or statistical analysis.