Types of Data Transformations in ETL Pipelines

What Is a Data Transformation in an ETL Pipeline?

Extract, Transform, Load breaks into three stages, and transform is the middle one: data has already been pulled from a source, and it isn't in the destination yet. Whatever happens to it in between, cleaning a field, joining it to another table, rolling it up into a summary, counts as transformation.

In DataFuseAI's pipeline builder, this stage is visual rather than scripted. You drag a source node onto the canvas, drag a transformation node next to it, connect the two, and double-click the transformation to configure it. A preview pane shows the effect before you run anything for real, and the output of one transformation connects directly into the input of the next, so a pipeline is really just a chain of these nodes between a source and a sink. That chaining pattern is what section 9 of this guide walks through end to end.

The Four Transformation Categories in DataFuseAI

Twelve nodes is a lot to hold in your head at once, so DataFuseAI groups them by what they're for rather than listing them alphabetically.

The examples in every section below share one running scenario: a fictional industrial-parts distributor consolidating orders across two point-of-sale systems, a CRM, and an ERP. None of the data is real, and none of it is drawn from an actual DataFuseAI pipeline; it's a standard sample dataset built for this guide.

Reshape: Pivot, Unpivot & Explode

Reshape transformations change the layout of a table, wide to long, long to wide, or nested to flat, without changing any of the underlying values.

Reshape
Pivot

Pivot rotates unique values from one column into separate output columns, aggregating as it goes. It turns long, row-per-period data into a wide table that's easier to scan and chart.

Input — regional_revenue_long (12 rows)

Configuration: Pivot Column = Quarter · Aggregation Column = Revenue · Function = sum

Output — 3 rows

The same pivot logic configured in DataFuseAI's pipeline builder

Reshape
Unpivot

Unpivot is the reverse: it rotates columns back into rows, which is usually what a BI tool wants instead of a wide, pre-pivoted table.

Input — the pivoted table above (3 rows) · Configuration: Unpivot For = Quarter, Unpivot By = Revenue, columns Q1–Q4 selected

Output — 12 rows (identical to the original long-format table)

The same unpivot logic configured in DataFuseAI's pipeline builder

Reshape
Explode

Explode flattens an array-type or nested field into one row per element, duplicating every other column for context. It's the node you reach for after an API returns a list inside a single field.

Input — orders_raw, items column (12 orders, array field)

Configuration: Array Column = items · New Column Name = item

Output — 19 rows (one per item, all other columns duplicated)

The same explode logic configured in DataFuseAI's pipeline builder

Combine: Join, Fuzzy Join & Union

Combine transformations bring together data from more than one source, either by matching keys across tables or by stacking rows with the same shape.

Combine
Join

Join merges two sources on a matching key. DataFuseAI supports the standard set of join types, and which one you pick changes exactly which rows survive.

Input A — orders (12 rows, from the Explode example above, customer_id added)

Input B — customers (10 rows; C009 and C010 have no orders)

Output — Inner Join on Customer ID (12 rows)

How row counts change by join type, on this exact dataset

Right Outer lands at 14 rather than 10 because C001, C003, C004, and C006 each placed two orders, so they appear twice; C009 and C010 still appear once each with null order fields. That's the detail that trips people up when they assume "outer join" just means "add the missing rows."

The same join logic configured in DataFuseAI's pipeline builder

Combine · Fuzzy Match
Fuzzy Join

Fuzzy Join is the same idea as Join, except the match key doesn't need to be identical on both sides. Instead, DataFuseAI scores how similar two text values are and accepts pairs above a threshold you set, which is what makes it possible to reconcile a CRM's freehand company names against an ERP's canonical customer list. For more on how the scoring itself works, see our dedicated guide to fuzzy matching in ETL pipelines.

Input — crm_contacts (12 rows, freehand company names)

Configuration: Organization ≈ Customer Name · Combined Score ≥ 70%, weighted · matched against the customers table above

Output — 12 rows, every contact resolved to a canonical customer (scores illustrative)

Combined-score configuration for the fuzzy join above

Field mapping between the two sources for the fuzzy join above

Combine
Union

Union stacks rows from two or more sources that share the same schema. It doesn't match on a key, it simply appends, which makes it the right tool when two systems produce the same kind of record rather than related records.

Input A — orders_store_a, legacy POS (10 rows)

Input B — orders_store_b, cloud POS (10 rows; rows 7 & 8 duplicate Input A)

Union All (duplicates retained): 20 rows. Union Distinct (Remove Duplicates checked): 18 rows, the two highlighted duplicate rows collapse into one each. This is also where multi-source data conflicts tend to surface, since union only catches duplicates that are byte-for-byte identical; near-duplicates need Dedupe or Fuzzy Match instead.

The same union logic configured in DataFuseAI's pipeline builder

Filtering & Deduplication: Filter, Fuzzy Filter & Dedupe

These three nodes are about what stays in the dataset: rows that meet a condition, rows that approximately match a value, and rows that aren't repeats of something already there.

Filter & Route
Filter

Filter keeps only the rows that satisfy an expression, built from available fields, comparison operators, and logical operators, similar to a SQL WHERE clause.

Input — clean_orders (12 rows, the deduped set from below)

Expression: status == 'Shipped' AND amount >= 300

Output — 6 rows

The same filter logic configured in DataFuseAI's pipeline builder

Filter & Route · Fuzzy Match
Fuzzy Filter

Fuzzy Filter applies the same similarity scoring as Fuzzy Join, but as a standalone condition rather than a merge, useful for isolating records that probably refer to one specific entity without an exact-match key.

Input — crm_contacts (same 12 rows as the Fuzzy Join example)

Condition: Organization fuzzy-matches "Meridian Traders Ltd" · Score ≥ 65% (scores illustrative)

Output: 4 rows (CR001–CR004). For a closer look at how similarity scoring, thresholds, and weighting actually work under the hood, our fuzzy matching guide covers the algorithms directly.

The same fuzzy filter logic configured in DataFuseAI's pipeline builder

Filter & Route
Dedupe

Dedupe removes duplicate rows, either across every column or a chosen subset, keeping the first occurrence of each unique combination. It's usually one of the first nodes in a pipeline, since it's hard to trust anything downstream if the same record entered twice.

Input — orders_raw (14 rows; two exact duplicates highlighted)

Configuration: Dedupe on all columns · Output — 12 rows (this is the clean_orders table used throughout this guide)

Every node from here forward, Filter, Split, Route, Aggregate, Derived, and Window, uses this same 12-row deduped table as its input, the way a real pipeline would chain them. For a broader look at catching this kind of issue before it reaches a warehouse, see data quality validation in ETL pipelines.

The same dedupe logic configured in DataFuseAI's pipeline builder

Routing & Splitting: Split & Route

Split and Route both divide one stream into multiple branches. The difference is how many branches they support.

Filter & Route
Split

Split evaluates one condition and sends every row down one of exactly two paths: a true branch and a false branch. Both outputs keep the full original schema.

Input — clean_orders (12 rows) · Condition: amount >= 300

Output 1: 7 rows. Output 2: 5 rows. Both branches typically feed different sinks or different downstream transformations.

The same split logic configured in DataFuseAI's pipeline builder

Filter & Route
Route

Route works like Split but supports any number of conditional branches, each defined independently, which is what you need once "yes or no" isn't enough categories.

Input — clean_orders (12 rows) · Routes: LOW (<100), MEDIUM (100–499), HIGH (500–999), CRITICAL (≥1000)

Route summary

The same routing logic configured in DataFuseAI's pipeline builder

Compute: Aggregate, Derived & Window

Compute transformations don't change which rows exist, well, Aggregate does, they calculate. The distinction between the three comes down to whether rows collapse, whether a new column gets added, and whether the calculation looks across other rows at all.

Compute
Aggregate

Aggregate groups rows by one or more columns and collapses each group into a single summary row.

Input — clean_orders (12 rows) · Group By: Region · Functions: sum, count, avg, max, min on Amount

Output — 4 rows

Aggregation functions available (selected reference)

The same aggregation logic configured in DataFuseAI's pipeline builder

Compute
Derived

Derived adds new columns calculated from existing ones, string, numeric, date, or conditional logic, without touching row count.

Input — clean_orders (12 rows) · New columns: total_with_tax = amount × 1.08, order_year = YEAR(order_date), customer_tier = CASE WHEN amount ≥ 1000 THEN 'VIP' WHEN amount ≥ 300 THEN 'Standard' ELSE 'Occasional'

Supported function categories (selected reference)

The same derived-column logic configured in DataFuseAI's pipeline builder

Compute
Window

Window functions calculate across a group of related rows, a rank, a running total, a prior-row comparison, without collapsing anything. Every input row still appears in the output, just with extra columns attached.

Input — clean_orders (12 rows) · Partition By: Region · Order By: Order Date · Functions: rank() by amount desc, running total of amount

Window function types available (selected reference)

The same window logic configured in DataFuseAI's pipeline builder

Quick Reference: All 12 Transformation Nodes

One table covering everything above, for whenever you already know which problem you're solving and just need the node name.

Chaining Transformations Into a Real Pipeline

No single node does the whole job. A real pipeline connects several of them in sequence, and the output row count changes at every step. Here's the Union example from earlier, extended through Filter, Join, Derived, and Aggregate to land at a single regional revenue summary.

Each node in that chain does exactly one job: Union combines two systems into one stream, Filter scopes it down to what matters, Join adds context that wasn't in the original records, Derived calculates fields the business actually asked for, and Aggregate turns 11 transaction rows into 4 numbers a dashboard can show. That's the same design principle behind keeping change data capture pipelines incremental rather than reprocessing everything on every run.

Where Transformation Logic Breaks in Production

Every node above works correctly in a preview pane. The failures that matter show up once real, messy volume hits the pipeline.

Join keys that don't quite match

Union hiding near-duplicates

Catching this kind of gap usually comes down to whether anyone is watching row counts at each step of the chain, not whether any single node was configured incorrectly. See our guide on pipeline observability for how to monitor that across a full pipeline rather than one node at a time.

Data Transformation Best Practices

None of the 12 nodes above is complicated in isolation. What makes a pipeline reliable is sequencing them correctly, deduplicate before filtering, join before aggregating, and previewing at every step so a bad assumption gets caught while it's still cheap to fix.

Frequently Asked Questions