EJ Strat via Unsplash
Extract-Transform-Load vs Extract-Load-Transform
They are both data integration methods used to transfer data from one source to a data warehouse. Although the aim of their method is similar, they differ.
ETL is the process of moving data from multiple sources to bring it to a centralized single database. The raw data is EXTRACTED from the source, TRANSFORMED on a separate processing server and LOADED into the target database.
The reason why Transform occurs before Load is due to the data that has been Extracted need to conform to the data regime of the target database. For example, there are some data warehouses that can only accept SQL-based data structures.
The ETL method ensures compliance in a sense that the data Extracted is Transformed in the correct data form to the target database. If the Extracted data is not Transformed correctly, it will not move and load into the data warehouse successfully.
ELT does not require the raw data to be transformed in order for it to be loaded. The raw data is loaded into the data warehouse and the aim of transformation, data cleaning, etc occur in the data warehouse.
As the data is left in its raw format in the data warehouse, this allows for different types of transformation and analytics to occur.
ELT is fairly new to the tech industry, with its development occurring due to scalable cloud-based data warehouses. Therefore, as years go on and more companies adopt cloud infrastructure, you can see the ELT process also becoming more popular.
|Discovery||Has existed for 20+ years||Fairly new to the data integration methods|
|Extract||The raw data is extracted using API connectors.||The raw data is extracted using API connectors.|
|Transform||The raw data is transformed on a secondary processing server.||The raw data is transformed inside of the target database.|
|Load||The raw data has to be transformed before it is loaded into the target database.||The raw data is loaded directly into the target database.|
|Time||Data transformation caused the ETL process to take a lot of time||Data transformation is done in parallel – making it time effective|
|Privacy||Pre transformation of data before it is loaded can eliminate Personally Identifiable Information (PII)||This requires more privacy standards|
|Costs||Using a secondary processing server can increase costs||Costs less due to simplified data stacks|
|Structure of Data||Structured||Can be Structured, semi-structured, and unstructured|
|Size of Data||Typically for smaller datasets||Typically for larger datasets|
|Dataset needs||Complicated transformation||Speed and efficiency|
|Re-queried||Because the data is already transformed before it enters the target database, it cannot be re-queried.||Yes, as it has not been transformed yet|
|Data Lake Compatibility||No||Yes|
If you want to determine which data integration process you need to use, that is down to your team’s needs.
Yes ELT is fairly new and has more advantages, but it may not achieve what you need.
Figuring out you need will help you figure out which process to use.
Nisha Arya is a Data Scientist and Freelance Technical Writer. She is particularly interested in providing Data Science career advice or tutorials and theory based knowledge around Data Science. She also wishes to explore the different ways Artificial Intelligence is/can benefit the longevity of human life. A keen learner, seeking to broaden her tech knowledge and writing skills, whilst helping guide others.