https://www.guru99.com/etl-vs-elt.html
What is ETL?
ETL stands for Extract, Transform and Load. In this process, an ETL tool extracts data from different RDBMS source systems then converts the data as applying data transformations (computation, concatenation, etc.) and then loads the data into it. Data Warehouse system. ETLs are flows from “source” to “target”. During ETL, the conversion engine handles all data changes.
So, what is ELT?
ELT is another method to approach data movement tool. Instead of converting data before writing, ELT allows the “target system” to convert first. The data is first copied into the “target” and then converted there. ELT is often used with No-SQL databases like Hadoop, Data Appliance or Cloud Installation.
Difference between ETL and ELT
ETL and ELT differ in the following:
| ETL | ELT | |
|---|---|---|
| 1. Process | Data converted from server staging is then transferred to Data warehouse DB | Data is still in the DB of the Data warehouse |
| 2.Code Usage | Used for: -Information intensive calculations-Small amount of data | Used for very large amounts of data |
| 3. Data transformation | The transformation is done in ETL server / staging | The transformations are done within the “target system”. |
| 4. Load time | The data is first loaded into staging and then loaded into the “target”. Need much time | The data is loaded into the “target” only once then converted. Faster |
| 5.Time of transformation | The mandatory ETL process requires the “Tranform” process to complete. As the data size increases, so does the conversion time. | . In the ELT process, the speed never depends on the size of the data. |
| 6. Maintenance time | The maintenance needs are very high because it needs to select data to load and transform | Maintenance needs are very low because data is always available |
| 7. Complexity in the beginning | At the beginning stage made very easy | In order to implement the ELT process, it is necessary to have a very deep knowledge of specialized tools and skills |
| 8. Support Data warehouse? | The ETL model is used for on-premise, relational and structured data | Used for cloud infrastructure that can support structured and unstructured data sources |
| 9. Support Data Lake | No support | Allows using Data Lake with unstructured data |
| 10. Complexity | The ETL process only loads important data, as determined from the time of design | This process includes all the development from output-backward and loading of relevant data |
| 11. Cost | Very high cost for small and medium businesses | Low input costs when using online software as Services Platforms |
| 12.Lookups | In the ETL process, both the Facts and Dimensions tables need to be available in Staging | All data will be available because Extract and Load are performed in just one action |
| 13. Aggregations | The complexity increases with additional data in the dataset | The power of target platform can process a significant amount of data quickly |
| 14 Calculation | Overwrite existing column or need to flag and push to “target” | Easily add calculated columns to existing tables. |
| 15.Maturity | ETL has been used for over 2 decades. It has good documentation and is easy to practice | The concept is relatively new and quite complex to implement |
| 16.Hardware | Most tools have separate, relatively expensive hardware requirements | The cost of cloud computing hardware is not a big deal |
| 17. Support data fee structure | Mainly support data relational structure | There is support available for unstructured data |
Overview:
- ETL stands for Extract, Transform and Load while ELT stands for Extract, Load, Transform.
- First, ETL loads data into the staging server and then goes to the “destination” while ELT loads the data directly into the “target”.
- The ETL model is used for on-premises data, structured and relational data, while ELT is used for structured and unstructured data sources on extended cloud systems.
- ETL is mainly used for small amounts of data while ELT is used for large amounts of data.
- ETL does not provide Lake Data support while ELT does provide Lake Data support.
- ETL is easy to implement while ELT requires appropriate skills to implement and maintain.
Reference: https://www.guru99.com/etl-vs-elt.html