Difference between ETL and ELT
ETL (Extract, Transform, and Load)
Extract, Transform and Load is the technique of extracting the record from sources (which is present outside or on-premises, etc.) to a staging area, then transforming or reformatting with business manipulation performed on it in order to fit the operational needs or data analysis, and later loading into the goal or destination databases or data warehouse.
Strengths
Development Time: Designing from the output backwards provide that only information applicable to the solution is extracted and processed, potentially decreasing development, delete, and processing overhead.
Targeted data: Due to the targeted feature of the load process, the warehouse contains only information relevant to the presentation. Reduced warehouse content simplify the security regime enforce and hence the administration overheads.
Tools Availability: The number of tools available that implement ETL provides the flexibility of approach and the opportunity to identify the most appropriate tool. The proliferation of tools has to lead to a competitive functionality war, which often results in loss of maintainability.
Weaknesses
Flexibility: Targeting only relevant information for output means that any future requirements that may need data that was not included in the original design will need to be added to the ETL routines. Due to the nature of tight dependency between the methods developed, this often leads to a need for fundamental redesign and development. As a result, this increase the time and cost involved.
Hardware: Most third-party tools utilize their engine to implement the ETL phase. Regardless of the estimate of the solution, this can necessitate the investment in additional hardware to implement the tool’s ETL engine. The use of third-party tools to achieve the ETL process compels the information of new scripting languages and processes.
Learning Curve: Implementing a third-party tools that uses foreign processes and languages results in the learning curve that is implicit in all technologies new to an organization and can often lead to consecutive blind alleys in their use due to shortage of experience.
ELT (Extract, Load and Transform)
ELT stands for Extract, Load and Transform is the various sight while looking at data migration or movement. ELT involves the extraction of aggregate information from the source system and loading to the target method instead of transformation between the extraction and loading phase. Once the data is copied or loaded into the target method, then change takes place.
The extract and load step can be isolated from the transformation process. Isolating the load phase from the transformation process delete an inherent dependency between these phases. In addition to containing the data necessary for the transformations, the extract and load process can include components of data that may be essential in the future. The load phase could take the entire source and loaded it into the warehouses.
Separating the phases enables the project to be damaged down into smaller chunks, thus making it more specific and manageable.
Performing the data integrity analysis in the staging method enables a further phase in the process to be isolated and dealt with at the most appropriate point in the process. This method also helps to ensure that only cleaned and checked information is loaded into the warehouse for transformation.
Isolating the transformations from the load steps helps to encourage a more staged way to the warehouse design and implementation.
Strengths
Project Management: Being able to divide the warehouse method into specific and isolated functions, enables a project to be designed on a smaller function basis, therefore the project can be broken down into feasible chunks.
Flexible & Future Proof: In general, in an ELT implementation, all record from the sources are loaded into the data warehouse as part of the extract and loading process. This, linked with the isolation of the transformation phase, means that future requirements can easily be incorporated into the data warehouse architecture.
Risk minimization: Deleting the close interdependencies between each technique of the warehouse build system enables the development method to be isolated, and the individual process design can thus also be separated. This provides a good platform for change, maintenance and management.
Utilize Existing Hardware: In implementing ELT as a warehouse build process, the essential tools provided with the database engine can be used.
Utilize Existing Skill sets: By using the functionality support by the database engine, the existing investment in database functions are re-used to develop the warehouse. No new skills need to be learned, and the full weight of the experience in developing the engine?s technology is utilized, further reducing the cost and risk in the development process.
Weaknesses
Against the Norm: ELT is a new method to data warehouse design and development. While it has proven itself many times over through its abundant use in implementations throughout the world, it does require a change in mentality and design approach against traditional methods.
Tools Availability: Being an emergent technology approach, ELT suffers from the limited availability of tools.
Difference between ETL vs. ELT
| Basics | ETL | ELT |
|---|---|---|
| Process | Data is transferred to the ETL server and moved back to DB. High network bandwidth required. | Data remains in the DB except for cross Database loads (e.g. source to object). |
| Transformation | Transformations are performed in ETL Server. | Transformations are performed (in the source or) in the target. |
| Code Usage | Typically used for
| Typically used for
|
| Time-Maintenance | It needs highs maintenance as you need to select data to load and transform. | Low maintenance as data is always available. |
| Calculations | Overwrites existing column or Need to append the dataset and push to the target platform. | Easily add the calculated column to the existing table. |
| Analysis |  |  |