ETL was considered to be most effective way to load information into a data warehouse. With the advancement in data warehouse technology, data warehouse designers, developers are now reckoning ELT as viable option.
Generally in ETL case, data is moved to an intermediate platform or staging area where the transformation rules are applied before loading the data into warehouse. Where as in ELT, it uses FTP (File Transfer Protocol) to transfer the data directly into data warehouse or data mart. Now, the transformation rules are then applied and Dimensions & Fact tables will be loaded.
ETL & ELT Architecture
- Transformation logic for data standardization and other business rules is executed on a dedicated system; hence reduce impact on data warehouse.
- ETL tools can interface with other external engines for data validation before the data is loaded on the data warehouse.
- Errors that occur during the ETL process can be identified and resolved before loading into warehouse. Hence reducing the need for time consuming database roll-backs.
- Huge data volumes travel across the network twice before loading into the data warehouse
- The ETL server must contain enough CPU and disk capacity to support the transformation process. Hence need for expensive hardware.
- License cost for ETL tool can be significant depending on the data sources and resources to feed the data into the data warehouse.
- Considering the load files from the source system are fed in to the data warehouse thru FTP or other transfer methods, hence network traffic is reduced.
- Lower cost for loading the data warehouse since no additional software is required
- Overall time for getting the data to the data warehouse is reduced
- Transformation will utilize additional data warehouses resources for execution.
- Complex transformation requiring external rules or sources of data such as decoding of addresses cannot be easily performed using data warehouses stored procedures.
- Database roll-back will be required if an error occurs on a temporary table during the transformation process