Difference between ETL and ELT
ETL -- Extract , Transform and Load
ELT -- Extract, Load and Transform
Its just the approach we take to get the job done. So, how do I say in my current project I am doing and ETL or ELT?
In most of the implementations we have ETL tools where there is an ETL Server like informatica, SSIS (SQL Server Integration Services) etc is used in the DW environments. In this case the transformations happen in the ETL server which is outside of the databases. Some of the ETL server / services are very power full and scalalbe and can understand various sources which makes it more effective in most of the implementations.
There are some RDBMS for example Teradata can load lots of data into database very fast compared to SQL Server as the architecture of Teradata is Parallel processing by default and its design to take care of huge amount of data. So, here using the RDBMS capabilities for transformation and loading makes it better. In this case ETL tool is not capable of loading data, so we some times use the procedures to do transformations and load the data into the DW.
In the integration layer, if you use more of database to implement transformations then it falls into ELT where as if you use more of ETL tool to do transformations then we can call it as ETL tool.
ETL Tools in the market: Informatica, Data Stage, SSIS
ELT Tools in the market: ODI (Oracle Data Integrator)
80% transformations in Database and 20% in others --- here we can conclude that we are using ELT
80% transformations in ETL tool and 20% in others --- here we can conclude that we are using ETL
In the ETL world, we utilize ETL Server the most for transformations. In the ELT world, we utilize more of database server (staging database) scripts and procedures to do transformations.
ETL -- Extract , Transform and Load
ELT -- Extract, Load and Transform
Its just the approach we take to get the job done. So, how do I say in my current project I am doing and ETL or ELT?
In most of the implementations we have ETL tools where there is an ETL Server like informatica, SSIS (SQL Server Integration Services) etc is used in the DW environments. In this case the transformations happen in the ETL server which is outside of the databases. Some of the ETL server / services are very power full and scalalbe and can understand various sources which makes it more effective in most of the implementations.
There are some RDBMS for example Teradata can load lots of data into database very fast compared to SQL Server as the architecture of Teradata is Parallel processing by default and its design to take care of huge amount of data. So, here using the RDBMS capabilities for transformation and loading makes it better. In this case ETL tool is not capable of loading data, so we some times use the procedures to do transformations and load the data into the DW.
In the integration layer, if you use more of database to implement transformations then it falls into ELT where as if you use more of ETL tool to do transformations then we can call it as ETL tool.
ETL Tools in the market: Informatica, Data Stage, SSIS
ELT Tools in the market: ODI (Oracle Data Integrator)
80% transformations in Database and 20% in others --- here we can conclude that we are using ELT
80% transformations in ETL tool and 20% in others --- here we can conclude that we are using ETL
In the ETL world, we utilize ETL Server the most for transformations. In the ELT world, we utilize more of database server (staging database) scripts and procedures to do transformations.
