Data transformation

What is data transformation?

Data transformation is any operation that changes data on its way from a raw source to a form you can analyse. Sometimes the change is technical, like turning the text 01/07/2026 into a real date value. Sometimes it is about meaning, like calculating net revenue, assigning a customer segment, or standardising order statuses that three systems each spell differently.

Transformations are the normal middle step of almost any data pipeline. Source systems are built to run a business, not to make reporting easy, so their tables rarely match the names, shapes, and grain a report needs. The transformation layer closes that gap.

A transformation is broader than a single mapping rule or derivation rule, and it is a different thing from an incremental load, which controls how much data you move rather than what you do to it once it arrives.

The common kinds of transformation

Most transformation work falls into a handful of recurring shapes, and one pipeline usually chains several of them.

• Type casting. Convert a value to the right data type: text to date, string to decimal, a "Y" or "N" flag to a real boolean.

• Cleaning. Trim stray spaces, fix encodings, and drop or flag values that are invalid.

• Deduplication. Collapse repeated records down to one, usually on a business key like order number or email address.

• Joining. Combine rows from two tables on a shared key so orders carry their customer and product details.

• Aggregating. Roll detail up to a summary, so invoice lines become revenue per customer per month.

• Pivoting. Rotate rows into columns, or unpivot columns back into rows, so the shape fits the report.

• Enriching. Add context that was not in the source, such as region, product category, or a converted currency amount.

Tools name these operations directly. Apache Spark calls them transformations and contrasts them with actions that return a result; Power Query exposes filter, merge, group by, pivot, and unpivot as menu commands. The vocabulary is shared even when the syntax is not.

A worked example

A webshop exports orders with amounts that include VAT, in the currency of each store, with test orders mixed in. Finance wants net revenue per customer per month, in euros, with test orders removed. One SQL model does all of it:

select
    customer_id,
    date_trunc('month', order_date) as order_month,
    sum(gross_amount / (1 + vat_rate) * fx_to_eur) as net_revenue_eur
from raw.orders
where is_test = false
group by 1, 2

That single query filters out the test rows, converts every amount to euros, strips VAT, and rolls the result up to the grain Finance asked for. It is four transformations at once: a filter, a currency conversion, a per-row calculation, and an aggregation. If nobody records what it does, the reported figure will not match a raw export of the same orders, and no one will be able to explain the difference.

The T in ETL versus ELT

Where the transformation runs is the difference between two pipeline patterns. In ETL, which stands for extract, transform, load, the data is transformed in flight, before it lands in the destination, so only the cleaned result is stored. In ELT, extract, load, transform, the raw data is loaded first, into a data warehouse or lakehouse, and transformed afterwards with the destination's own compute.

The trade-off is about where the work sits and how much history you keep. ETL keeps the destination lean but discards the raw grain, so a new question about old data can mean extracting from the source again. ELT keeps the raw data on hand and pushes the work into warehouse compute, which is why the modern SQL-first stack leans towards it. Neither is automatically right. Volume, cost, governance, and how much history you want to replay all pull the decision one way or the other.

Where transformations run

The logic can live in many places, and the tool matters less than whether that logic is written down, tested, and versioned.

dbt models a transformation as a SQL select statement and wraps it in a view or table inside your warehouse, so the data never leaves the warehouse to be processed. Power Query and Dataflow Gen2 give a low-code path with more than 300 built-in transformations, generating M code behind each step you click. Spark and notebook jobs cover the heavy, distributed cases. In Spark the transformations are lazy: they only run when an action finally asks for a result.

Version control and testing

A transformation is business logic, and business logic that exists only inside a click-path is hard to review or reproduce. Writing transformations as code files, kept in Git version control, means every change is diffable, has an author, and can be rolled back. Dataflow Gen2 now creates items with Git integration by default, which is the same idea applied to a low-code tool.

Tested transformations are the second half. In dbt a test is an assertion written as a query that looks for rows which break a rule: a column that should be unique but has duplicates, a field that should never be null, a status outside its list of accepted values, or a reference with no match in the table it points to. Zero failing rows means the assertion holds. This is where transformation meets data quality and data testing, because the point of the checks is that a wrong transformation gets caught before it reaches a report, not after someone questions the number.