# What are some common transformation patterns in ETL workflows?

Source: https://zilliz.com/ai-faq/what-are-some-common-transformation-patterns-in-etl-workflows
Author: zilliz

Common transformation patterns in ETL workflows address specific data processing needs, ensuring data is accurate, structured, and ready for analysis. Three widely used patterns include **data cleansing**, **aggregation**, and **joining datasets**, each serving distinct purposes in preparing data for downstream use.

**Data Cleansing** focuses on correcting inconsistencies and errors in raw data. This includes handling missing values (e.g., replacing nulls with defaults or interpolating values), standardizing formats (e.g., converting dates to `YYYY-MM-DD`), and removing irrelevant characters (e.g., trimming whitespace). For example, a dataset containing customer addresses might require cleansing to unify country codes (e.g., changing "US" and "USA" to a single format) or validating ZIP codes against a reference list. This step ensures data quality and consistency, which is critical for reliable reporting or machine learning models.

**Aggregation** summarizes detailed data into meaningful metrics. This often involves grouping records and applying functions like `SUM`, `AVG`, or `COUNT`. For instance, a retail company might aggregate daily sales transactions into monthly revenue totals per product category. Aggregation reduces data volume for analytical queries, improving performance in dashboards or OLAP systems. It also helps identify trends, such as calculating average customer spend or peak sales periods. However, over-aggregation can lead to loss of granularity, so balancing summary and detail is key.

**Joining Datasets** combines data from multiple sources using shared keys. A common example is merging customer orders (from a transactional database) with customer demographics (from a CRM system) via a `customer_id` field. Joins can be inner (matching records only), outer (including non-matching records), or lookups (enriching data with reference tables). For example, appending product prices from a master catalog to an e-commerce order table. Careful handling of duplicates, null keys, and schema mismatches is essential to avoid data loss or inaccuracies during this process.

Other patterns include **splitting** (e.g., separating a `full_name` column into `first_name` and `last_name`), **validation** (e.g., flagging invalid email formats), and **deduplication** (e.g., removing duplicate customer records). Choosing the right patterns depends on the data’s structure, quality, and business requirements.