ETL stands for Extract, Transform and Load which is the process of moving data from the source database to the destination database.
The ETL process involves 3 steps which are extract, transform and load.
These steps involve extracting the data from the source database and then transforming it into the necessary format and then finally uploading it to the destination database.
This can be achieved with the help of various application tools that include the following:
- Oracle Warehouse Builder (OWB)
- Oracle Data Integrator (ODI)
- Open Text Integration Center
- CloverDx
- Informatica
- Talend
- DataMigrator (IBI)
What are the steps in ETL ?
- Extract: The first step in the process is to extract the data from the source database. The data in the source database can be either fully extracted or incrementally extracted and the data should be carefully extracted to avoid damaging the source database.
2. Transform: the next step in the process is the transformation step, which is used to add some rules to the extracted data. These rules are data queries and functions which can be used to merge, transform and cleanse the data into the required data format.
3. Load: the final step in the process is to load the transformed data into the destination database in the required destination database format.
Though the outline steps might make ETl look easy, it is not as straightforward as it seems. It might require numerous trials to get the final data uploaded into the destination database and even then you might have to delete that data and start again from scratch.
So the ETL should ideally perform the data extraction, transformation and load steps in a test database and once they are acceptable, then they can be recreated in the production environment.