Both ELT process and ETL process start with the extraction of data from the source(s). However, during the ETL process, data is transformed using a different software before it is loaded to a warehouse while data is loaded before it is transformed in the ELT process. EtLT is a process where a little bit of transformation is done to the data before it is loaded into a data warehouse for example if we needed to censor sensitive data.
The benefits of using ELT include
- Works well with the OLAP (Online Analytical Processing) which focuses on large historical datasets due it already having a structured form.
- Faster access to raw data and there is no time spent transforming data.
- The transformation of data is mostly completed in one environment.
- Data can be transformed in multiple ways for different use cases.
- All transformations are completed in the warehouse so errors can be debugged easier.
The drawbacks of working with ELT include
- Sensitive data would need to be loaded first so there is an increased risk of breaking compliance rules.
- Data transformations of larger datasets can be more expensive.
- Raw data needs to be loaded before it is validated.
Overall ETL will be better suited if you have a need for real time data or are working with sensitive data.