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
- Analyse. Have the source files examined automatically: which columns, which kinds of values, what is missing.
- Map. Record which source column goes to which target field, with the conversions in between, such as translating statuses or cleaning up names.
- Transform. Apply those rules automatically, including references between files.
- Validate. Check before anything is loaded: mandatory fields, duplicate keys, invalid values.
- 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