ETL is the most common method used when transferring data from a source system to a data warehouse. But there are cases where you might want to use ELT. What is the difference between the two? Read on:
Extract, Transform, and Load (ETL) is a process that involves extracting data from outside sources, transforming it to fit operational needs (sometimes using staging tables), then loading it into the end target database or data warehouse. This approach is reasonable as long as many different databases are involved in your data warehouse landscape. In this scenario you have to transport data from one place to another anyway, so it’s a legitimate way to do the transformation work in a separate specialized engine.
Extract, Load, Transform (ELT) is a process where data is extracted, then loaded into a staging table in the database, transforming it where it sits in the database and then loading it into the target database or data warehouse.
ELT should be used, instead of ETL, in these various cases :
- There are big volumes of data
- The source database and the target database are the same
- The database engine is well adapted for that kind of processing, such as PDW, which is great at loading massive amounts of data very quickly
So in short, when using ETL the transformations are processed by the ETL tools, while in ELT the transformations are processed by the target datasource.
More info:
Difference between ETL and ELT
