ETL process is an important component of the Data Warehousing Architecture. ETL stands for Extract Transform and Load. We will discuss about the Steps of ETL process , where ETL is used and also check the ETL tool available in the Market.
What is ETL Process
As the name clearly suggest ETL is short form for Extract, Transform,Load the data from one or multiple Source to one or more Target system.This terminology is mainly used in data warehouse, data migration processes
Check here for Informatica powercenter
ETL Process Steps :
ETL Process mainly consist of 3 steps as described in picture also
- Extract :
It is the process of reading data from the sources. Source can be Database (like Oracle , Mysql) , Files (csv, fixed length , excel) or other system like SAP Below are the several ways of extraction process:
- Update notification –In such type of extraction source system provides a notification that a records has been changed and gives the description of the changes done. ETL process will automatically pick those records and process them.
- Incremental extract – In such type of extraction m source system needs to identify changes and send those changed records for further processing.
- Full extract – In such type of extraction , sources full data dump is provided. ETL processes the data on the basis of current file and old data already loaded in Target.
- Transform : In this part, processing of extracted data take place. There can be some change in the data/ filtering of data etc. depending upon the business scenarios. This part can include :
- Translating coded values : like M for ‘Male’ or ‘F’ for ‘Female’
- Encrypting of data : like credit card no
- Driving new calculated value :annual salery = salery * 12
- Validation of data : with help of ref lookup table
- Load : This part included the loading of processed data into the target system . Again target can be some database or some files . It control the flow of data into the various system/tables.
Where ETL Process is used
ETL are used mainly:
- To create migrate data from one data base to another type of database
- To create data marts and data warehouse
- To transform the data of one system to another system type
Example of ETL Tools:
Some of well known ETL tool available in market are:
- Oracle Data Integerator
- SAP Data Integerator
- AB Initio
- IBM InfoSphere Data Stage
Source : www.wikipedia.org
So here we talked about ETL process in data warehouse , steps of ETL process along with various ETL tool available in market.