Skip to content

What is ETL? Data migration without VLOOKUP hell

ETL stands for extract, transform, load: taking data out of one system, converting it and loading it into another. With a fixed approach, an error-prone chore becomes a repeatable process.

In short

A new system, loading a new customer, merging two sets of books: each time, data has to move from A to B. ETL is the name for that process. Extract: take the data out of the source system. Transform: convert it into what the target system expects. Load: load it in.

How it often goes

An export to Excel, then days of work with VLOOKUPs, find and replace, and copy and paste. It works, but:

  • every time again, by hand;
  • errors only show up when the target system rejects them, or worse, when they slip in unnoticed;
  • afterwards nobody knows exactly what was changed.

How it can be done better

  1. Analyse. Have the source files examined automatically: which columns, which kinds of values, what is missing.
  2. Map. Record which source column goes to which target field, with the conversions in between, such as translating statuses or cleaning up names.
  3. Transform. Apply those rules automatically, including references between files.
  4. Validate. Check before anything is loaded: mandatory fields, duplicate keys, invalid values.
  5. Load and record. Create the import files and keep a record of what happened.

For a supplier of industrial vending machines we built a tool along these lines that converts thousands of rows of products, equipment, bins and stock into the target system's import format. Hours of manual work became a process of minutes, and it can simply be reused for the next customer.

What to watch out for

  • Start with the quality of the source data. A migration does not make dirty data any cleaner.
  • Make the rules repeatable, so that a trial migration and the real migration do the same thing.
  • Have a user who knows the data spot-check the outcome.

Would you like advice on data migration? Request a free advice conversation.

Updated on

Services

What is full stack?

Full stack means every layer of an application, from the screen to the database and the server. A full-stack developer or team works across those layers.

Services

Who holds up a mirror to your CIO?

CIOs and IT managers divide their time between budgets, suppliers, projects and people. A sparring partner with current technical knowledge makes their decisions stronger, and gives the board confidence.

Services

What is the Definition of Done?

The Definition of Done records in advance when something is really finished. That way everyone knows in advance what the delivered work will be checked against.