Skip to content

Data & Analytics

Data transformation: write down the cleanup, once

Every week someone fixes the exports: trims the names, translates the statuses, reformats the dates. Each of those fixes is a rule. Transformation writes them down, runs them the same way every time, and flags what doesn't fit instead of guessing.
Discuss your systemSee nine statuses become three ↓
CRMAcme Properties LLCClosed Won03/04/2026$1,850.00AccountingACME PROPERTIESPAID2026-03-041850SchedulingAcme Prop.completeMar 4—match customertranslate statusone date formatamount in centsOne recordCustomerAcme PropertiesStatusWonDate2026-03-04Amount185000

Three systems, three spellings

The CRM calls them Acme Properties LLC. Accounting has ACME PROPERTIES. The scheduling tool says Acme Prop. Put the three exports side by side and a report counts three customers, three statuses and one job that was somehow won, paid and complete on different days.

Somebody always knows how to fix it, usually in a spreadsheet only they understand. Transformation takes what that person knows and makes it a set of rules with names, so the fix doesn't leave when they do.

Four kinds of rule

Almost every cleanup is one of these. Each rule states what it means in business terms, so a change to it can be reviewed like any other decision.

  1. 01

    Normalize

    “Is this the same value?”

    Trim, case, units, date formats, phone numbers. “$1,850.00”, “1850” and “1,850” become one amount.

    Watch for: rounding rules nobody agreed on.

  2. 02

    Align identifiers

    “Is this the same thing?”

    Match the customer in the CRM to the one in accounting, by a shared ID where there is one and by agreed fields where there isn't.

    Watch for: matching on name alone.

  3. 03

    Translate statuses

    “What does this state mean here?”

    “Closed Won”, “PAID” and “complete” are three systems' words for different moments. The rule says which canonical status each one is.

    Watch for: one source word that means two different things.

  4. 04

    Derive values

    “What do we calculate from it?”

    Job duration from start and end, margin from price and cost, the month a job belongs to. Derived once, in one place.

    Watch for: the same value derived differently in two reports.

Many statuses, three meanings

Nine status values from three systems. Each one maps to a meaning the business agreed on. The one nobody has decided about yet doesn't get a guess; it waits for a rule.

Is a part-paid invoice completed work or open work? That's a business decision, not a data one. Until someone makes it, the three records wait in plain view instead of being counted either way.

No invented defaults

The easy way to make a rule “work” is to fill in whatever is missing. It also hides the problem in the report.
Job with no close dateDated today, so its revenue lands in the wrong monthHeld: missing close date, sent to the sales team to fill in
Status the rule hasn't seenCounted as WonHeld: new status “On hold”, needs a rule
Amount in a different currencyAdded as if it were dollarsHeld: currency not in the agreed list

Every value can be traced back

Because rules run on the preserved source data, any number can be walked back to the rows it came from and the rules it passed through. When a figure looks wrong, the trace says whether the source was wrong or a rule was.

  1. Report

    Revenue, March · 48,210

  2. from model

    jobs completed

  3. via rule

    amount in cents

  4. from Accounting export

    1 April · row 1,882

What transformation hands off

In

Staged records
untouched, from ingestion
Mappings and rules
each with its meaning
Target model
the shape it must fit
Transformation
normalize · align · translate · derive

Out

Clean records
one shape, one meaning
Written logic
reviewable, versioned
Exceptions
held, with an owner

Worth building when the cleanup repeats

Good fit

  • Sources use different structures or words for the same things.
  • The same cleanup happens by hand before every report.
  • Records from several systems need to line up as one.

Not the right fit

  • The business hasn't agreed what the fields mean or how records relate. Data modeling settles that first.
  • The data comes from one system that already uses the shape you need.

Where transformation does its work

Next to transformation

Questions about data transformation

Is data transformation the same as ETL?

It's the T. ETL and ELT describe the order of extract, load and transform; transformation is the rules themselves. We usually load untouched data first and transform after, so rules can be fixed and re-run.

Who decides what the rules should be?

The business. We write the rules from how your team already interprets the data, then show the edge cases nobody had to decide before, like a part-paid invoice, and ask.

Can AI clean the data for us?

It can help with genuinely messy text, like reading a free-form job description into a service type. The output is still checked against the rules, and the rules themselves stay written and reviewable.

What happens when a source adds a new status?

The rules don't recognize it, so the records are held as an exception and the owner is told. Someone decides what it means, the rule is added, and the held records run through.