18 Data Transformation Types Every Data Engineer Should Know
A practical reference for ETL teams deciding how source data should be cleaned, reshaped, protected, and loaded.
Data transformation types are the repeatable operations that turn source data into target-ready data. In ETL, transformation is where messy inputs become usable rows, fields, files, and reports. Without it, you are mostly transporting problems at speed. Very efficient. Still problems.
Data transformation is where ETL earns its keep
Extraction gets data out. Loading writes it somewhere useful. Transformation is the middle step that makes the handover safe. It fixes formats, maps fields, calculates values, protects sensitive data, and reshapes structures so the target system receives what it expects.
Good transformation logic also makes workflows repeatable. A manual spreadsheet edit may be fine once. If the same edit happens every Monday morning, it belongs in the ETL process before someone names a file `final_final_use_this_one.xlsx`.
For related concepts, compare this page with the Data Validation hub, the Data Import hub, the Data Export hub, and Advanced ETL Processor Enterprise for self-hosted workflow automation.
Data transformation types compared
| Transformation type | Main purpose | Typical ETL example | Dedicated guide |
|---|---|---|---|
| Data Standardization | Makes values follow one agreed format. | An ETL workflow receives dates as `20/07/2026`, `2026-07-20`, and `Jul 20 2026`. The transformation writes every value as `2026-07-20` before loading. | Read the dedicated guide |
| Data Normalization | Rescales or restructures values into a consistent model. | A customer feed includes repeated address fields in one row. The ETL process separates customers and addresses into related target tables. | Read the dedicated guide |
| Data Aggregation | Summarizes detailed rows into totals or grouped metrics. | A nightly ETL job groups sales transactions by store and day, then writes total revenue, order count, and average order value to a reporting table. | Read the dedicated guide |
| Data Filtering | Keeps records that match agreed conditions. | An ETL workflow loads only invoices with `Approved` status and routes `Draft` or `Cancelled` records to a separate audit output. | Read the dedicated guide |
| Data Sorting | Orders records by one or more fields. | A payment export is sorted by bank account, payment date, and reference number before being written to a flat file. | Read the dedicated guide |
| Data Mapping | Connects source fields to target fields. | A source field named `CustNo` maps to a target field named `customer_id`, while `FirstName` and `LastName` combine into `customer_name`. | Read the dedicated guide |
| Data Enrichment | Adds extra context from lookup or reference data. | An order file contains customer IDs. The ETL process looks up customer names, regions, and account status before loading the reporting table. | Read the dedicated guide |
| Data Cleansing | Fixes or routes incorrect, incomplete, or inconsistent values. | An ETL process trims spaces, converts blank strings to nulls, fixes known status aliases, and rejects rows with invalid mandatory fields. | Read the dedicated guide |
| Data Deduplication | Finds and removes repeated records. | A customer file contains repeated email addresses. The ETL process keeps the newest record and routes older duplicates to an exception table. | Read the dedicated guide |
| Data Masking | Hides sensitive values while preserving limited usefulness. | An ETL workflow writes card numbers as `************1234` and masks national identifiers before exporting test data. | Read the dedicated guide |
| Data Anonymization | Removes or changes identifiers so people cannot be identified. | A healthcare analytics feed removes names, exact birth dates, addresses, and patient IDs before records enter a research dataset. | Read the dedicated guide |
| Data Pivoting | Turns row values into columns. | Monthly sales rows become columns named `Jan`, `Feb`, and `Mar` for a management report. | Read the dedicated guide |
| Data Unpivoting | Turns columns back into rows. | Columns named `JanSales`, `FebSales`, and `MarSales` become rows with `month` and `sales_amount` fields. | Read the dedicated guide |
| Schema Transformation | Changes structure to match the target schema. | A nested JSON order document is flattened into order header and order line tables before loading into SQL Server. | Read the dedicated guide |
| Data Type Conversion | Converts values to the correct target data type. | A text value `19.95` converts to a decimal, `true` converts to a boolean, and `2026-07-20` converts to a date. | Read the dedicated guide |
| String Transformations | Changes text values into a target-ready form. | An ETL workflow splits `Smith, Jane` into `last_name` and `first_name`, then trims spaces before loading. | Read the dedicated guide |
| Date Transformations | Parses, reformats, and derives date values. | A workflow parses `20/07/2026 14:30`, stores it as a timestamp, and derives `year`, `month`, and `week_start_date` for reporting. | Read the dedicated guide |
| Numeric Transformations | Calculates, rounds, scales, and adjusts numbers. | An ETL workflow calculates line total as quantity multiplied by unit price, applies tax, and rounds the result to two decimal places. | Read the dedicated guide |
Definition
Data standardization converts inconsistent source values into a common representation. Dates, country codes, product names, currencies, phone numbers, and units all follow the same agreed pattern.
Why it is used
It is used because different systems describe the same thing in different ways. Without standardization, reporting groups split apart and joins fail for reasons that look petty but are very real.
Practical ETL example
An ETL workflow receives dates as `20/07/2026`, `2026-07-20`, and `Jul 20 2026`. The transformation writes every value as `2026-07-20` before loading.
Common use cases
- Preparing multi-country customer files
- Standardizing product codes
- Aligning units of measure
- Cleaning report dates
Definition
Data normalization reduces variation so values fit a consistent scale, structure, or relational model. In analytics, it may rescale numeric values. In databases, it may split repeated groups into related tables.
Why it is used
It is used to make comparisons fair and storage cleaner. Normalized data is easier to join, aggregate, and maintain because repeated values have fewer places to misbehave.
Practical ETL example
A customer feed includes repeated address fields in one row. The ETL process separates customers and addresses into related target tables.
Common use cases
- Preparing database loads
- Building dimensions and facts
- Rescaling model inputs
- Reducing repeated attributes
Definition
Data aggregation groups records and calculates summary values such as counts, sums, averages, minimums, maximums, and totals.
Why it is used
It is used when reports need answers rather than raw transactions. Aggregation reduces volume and turns detailed activity into useful measures.
Practical ETL example
A nightly ETL job groups sales transactions by store and day, then writes total revenue, order count, and average order value to a reporting table.
Common use cases
- Daily sales summaries
- Inventory totals
- Finance reporting
- KPI dashboards
Definition
Data filtering includes or excludes records based on rules such as date ranges, status values, regions, file names, or validation outcomes.
Why it is used
It is used to stop irrelevant data from entering the target. A good filter is a bouncer for your pipeline, but with fewer opinions about shoes.
Practical ETL example
An ETL workflow loads only invoices with `Approved` status and routes `Draft` or `Cancelled` records to a separate audit output.
Common use cases
- Incremental loads
- Regional extracts
- Exception handling
- Status-based routing
Definition
Data sorting arranges records by fields such as date, customer, amount, priority, sequence number, or business key.
Why it is used
It is used when output order affects readability, comparison, grouping, or downstream processing. Some legacy systems still expect files in a specific order because apparently chaos was not enough.
Practical ETL example
A payment export is sorted by bank account, payment date, and reference number before being written to a flat file.
Common use cases
- Ordered exports
- Batch files for legacy systems
- Report preparation
- Comparison workflows
Definition
Data mapping defines how each source field populates a target field. It may include renaming, lookup rules, default values, joins, and calculated outputs.
Why it is used
It is used because source and target systems rarely agree on field names or structures. Mapping makes the contract explicit instead of leaving it in someone's memory and a suspicious spreadsheet.
Practical ETL example
A source field named `CustNo` maps to a target field named `customer_id`, while `FirstName` and `LastName` combine into `customer_name`.
Common use cases
- Database migration
- API integration
- File imports
- System consolidation
Definition
Data enrichment adds information to source rows from reference tables, APIs, lookup files, or calculated rules.
Why it is used
It is used to make records more useful before loading. A customer code may be technically enough, but adding region, account manager, and risk group makes the row report-ready.
Practical ETL example
An order file contains customer IDs. The ETL process looks up customer names, regions, and account status before loading the reporting table.
Common use cases
- Customer segmentation
- Product categorization
- Geographic reporting
- Reference data lookup
Definition
Data cleansing corrects known data issues such as extra spaces, invalid characters, bad casing, placeholder values, broken codes, and inconsistent labels.
Why it is used
It is used because source data often arrives with small defects that cause large downstream irritation. Cleansing makes data safer before validation and loading.
Practical ETL example
An ETL process trims spaces, converts blank strings to nulls, fixes known status aliases, and rejects rows with invalid mandatory fields.
Common use cases
- CRM cleanup
- Spreadsheet imports
- Product catalogue feeds
- Operational reporting
Definition
Data deduplication identifies duplicate rows or duplicate business events using exact matches, keys, or matching rules.
Why it is used
It is used because repeated records inflate totals and create unreliable outputs. Duplicate rows are like relatives at Christmas: one is fine, five unexpected copies becomes a planning problem.
Practical ETL example
A customer file contains repeated email addresses. The ETL process keeps the newest record and routes older duplicates to an exception table.
Common use cases
- Customer master cleanup
- Transaction imports
- Mailing lists
- Merged source feeds
Definition
Data masking replaces sensitive values with hidden, partial, or substitute values. The structure remains useful, but the original value is not exposed.
Why it is used
It is used to reduce privacy risk in logs, reports, test environments, and shared extracts.
Practical ETL example
An ETL workflow writes card numbers as `************1234` and masks national identifiers before exporting test data.
Common use cases
- Test data preparation
- Support extracts
- Privacy controls
- Shared reports
Definition
Data anonymization removes direct and indirect identifiers so records cannot reasonably be tied back to a person.
Why it is used
It is used when teams need analytical value without personal identity. Masking hides values; anonymization aims to remove the route back to the person.
Practical ETL example
A healthcare analytics feed removes names, exact birth dates, addresses, and patient IDs before records enter a research dataset.
Common use cases
- Analytics datasets
- Research extracts
- Privacy-safe reporting
- Data sharing
Definition
Data pivoting transforms rows into columns so values become easier to compare across categories, periods, or measures.
Why it is used
It is used when a report or target table needs a cross-tab structure. Pivoting is useful, but it should be deliberate. Accidental pivots are how spreadsheets learn dark magic.
Practical ETL example
Monthly sales rows become columns named `Jan`, `Feb`, and `Mar` for a management report.
Common use cases
- Excel-style reports
- Period comparisons
- Survey results
- KPI matrices
Definition
Data unpivoting converts repeated measure columns into a normalized row structure with attribute and value fields.
Why it is used
It is used when wide reports need to become loadable database rows. Databases usually prefer rows; spreadsheets often prefer drama.
Practical ETL example
Columns named `JanSales`, `FebSales`, and `MarSales` become rows with `month` and `sales_amount` fields.
Common use cases
- Importing spreadsheet reports
- Normalizing survey exports
- Loading period measures
- Preparing analytics tables
Definition
Schema transformation changes the shape of data by renaming fields, adding fields, removing fields, nesting values, flattening structures, or splitting one source into multiple targets.
Why it is used
It is used because source schemas and target schemas rarely match. The transformation layer is where that difference is handled explicitly.
Practical ETL example
A nested JSON order document is flattened into order header and order line tables before loading into SQL Server.
Common use cases
- JSON flattening
- API integration
- Database migration
- Warehouse loading
Definition
Data type conversion changes values between types such as string, integer, decimal, date, boolean, and timestamp.
Why it is used
It is used because source files often store everything as text. Target databases, being less forgiving than Excel, expect proper types.
Practical ETL example
A text value `19.95` converts to a decimal, `true` converts to a boolean, and `2026-07-20` converts to a date.
Common use cases
- CSV imports
- Excel loading
- Database writes
- API payload preparation
Definition
String transformations modify text using operations such as trim, split, concatenate, substring, replace, uppercase, lowercase, and pattern extraction.
Why it is used
They are used because text fields often carry multiple meanings in one value. Humans do that. Databases are less sentimental.
Practical ETL example
An ETL workflow splits `Smith, Jane` into `last_name` and `first_name`, then trims spaces before loading.
Common use cases
- Name parsing
- Code cleanup
- Address formatting
- Reference extraction
Definition
Date transformations convert date strings, change formats, calculate periods, extract date parts, and adjust time zones.
Why it is used
They are used because date formats vary by system, country, file type, and occasionally by mood. ETL makes the rules explicit.
Practical ETL example
A workflow parses `20/07/2026 14:30`, stores it as a timestamp, and derives `year`, `month`, and `week_start_date` for reporting.
Common use cases
- Time-series reporting
- SLA checks
- Finance periods
- Incremental loads
Definition
Numeric transformations apply arithmetic, rounding, scaling, currency conversion, percentage calculation, and unit conversion.
Why it is used
They are used when raw numbers need business meaning. A quantity, price, tax rate, and discount rarely arrive in exactly the shape the target report needs.
Practical ETL example
An ETL workflow calculates line total as quantity multiplied by unit price, applies tax, and rounds the result to two decimal places.
Common use cases
- Financial calculations
- Unit conversion
- Inventory values
- Metric preparation
Data transformation types FAQ
What are data transformation types?
Data transformation types are the common ways ETL workflows change source data before loading. They include standardization, normalization, aggregation, filtering, mapping, enrichment, cleansing, masking, pivoting, type conversion, and more.
Why is data transformation important in ETL?
Transformation is important because source data rarely matches the target format. ETL uses transformation to clean values, align schemas, calculate fields, protect sensitive data, and prepare reliable outputs.
Is data cleansing the same as data transformation?
Data cleansing is one type of data transformation. Cleansing focuses on fixing or routing bad values, while transformation also covers mapping, aggregation, enrichment, schema changes, pivots, date logic, and numeric calculations.
Should transformation happen before validation?
In most cases, basic structural transformation happens before validation, then validation checks the transformed result. Some checks also run before transformation to reject unusable source records early.
When should a transformation not be automated?
Do not automate a transformation when the rule is not understood, the source is a one-off file, or the business owner cannot explain the expected result. Test the logic on a small sample before scheduling it.
Use transformation rules deliberately
Start with the smallest rule that makes the target data correct. Then validate the result before loading. For function-level details, use the Advanced ETL Processor transformation functions reference. Transformation without validation is just confidence with a keyboard.
Keep raw files unchanged. Future you deserves evidence.