The second stage is the key to the whole ETL process. This is where we take the data that has been extracted and transform it into something the receiving tool can import and/or use.

At this stage, any number of processes can be applied to the data to get it into shape. Including;

• Data Validation – Check your data meets a given criteria and quarantine or flag any that don’t.
• Data Cleansing – The data we extract may not be clean and need cleansing (fields commonizing, formatting correcting etc).
• Correcting Formatting – Make sure everything is in the correct format throughout e.g. the same date format is used throughout.
• Merging Fields – Combining multiple fields into a single field.
• Splitting Fields – If a complete address is stored in one field, it can be split into its component parts and stored in different fields.
• Unit Conversion – If two systems use a different unit of measurement, the transformation process can convert numbers into the right units (e.g. Inches into millimetres).
• Currency Conversion – Conversions can be carried out using current exchange rates or historical exchange rates can be looked up and used.
• Adding Calculated Figures – Calculating a new figure based on the other data provided. E.g. calculating an items volume if only its length, width and height are provided.

The final step of the process is loading the transformed data into its destination system. This uses the same processes as the extraction process but in reverse, so these include;