ETL and ELT are processes for moving data from one system to another. Both processes involve the same 3 steps, Extraction, Transformation, and Loading. The fundamental difference between the two lies in the order in which the data is loaded into the data warehouse and analyzed.
What is ETL?
ETL has been the traditional method for data warehousing and analytics. It is used to synthesize data from more than one source in order to build a data warehouse or data lake. First, the data is extracted from RDBMS source systems, which is the extraction stage. Next is the transformation stage, where all transformations are applied to the extracted data, and only then is it loaded into the end-target system to be analyzed by business intelligence tools.
What is ELT?
ELT involves the same three steps as ETL, but in ELT, the data is loaded immediately after extraction, before the transformation stage. With ELT, all data sources are aggregated into a single, centralized repository. With today’s cloud based data warehouses being scalable and separating storage from compute resources, ELT makes more sense for most modern businesses. ELT allows for unlimited access to all of your data by multiple users at the same time, saving both time and effort.
Benefits of ELT
Simplicity: Transformations in the data warehouse are generally written in SQL, which is the traditional language for most data applications. This means that anyone who knows SQL can contribute to the transformation of the data.
Speed: All of the data is stored in the warehouse and will be available whenever it is needed. Analysts do not have to worry about structuring the data before loading it into the warehouse.
Self service analytics: When all of your data is linked together in your data warehouse you can then easily use BI tools to drill down from an aggregated summary of the data to the individual values underneath.
Bug Fixes: If you discover any errors in your transformation pipeline, you can simply fix the bug and re-run just the transformations with no harm done. With ETL however, the entire process would need to be redone.
