ETL Vs ELT

ELT Process

Extraction, Load and Transform (ELT) is the technique of extracting raw data from the source and storing it in data warehouse of the target server and preparing it for endstream users.

ELT comprises of 3 different operations performed on the data –
Extract
Extracting data is the technique of identifying data from one or more sources. The sources may be databases, files, ERP, CRM or any other useful source of data.
Load
Loading is the process of storing the extracted raw data in data warehouse or data lakes.
Transform
Data transformation is the process in which the raw data source is transformed to the target format required for analysis.

Data from the sources are extracted and stored in the data warehouse. The entire data is not transformed but only the required transformation is done when necessary. Raw data can be retrieved from the warehouse anytime when required. The data transformed as required is then sent forward for analysis. When you use ELT, you move the entire data set as it exists in the source systems to the target. This means that you have the raw data at your disposal in the data warehouse, in contrast to the ETL approach.

ETL Process

ETL is the traditional technique of extracting raw data, transforming it for the users as required and storing it in data warehouses. ELT was later developed, having ETL as its base. The three operations happening in ETL and ELT are the same except that their order of processing is slightly varied. This change in sequence was made to overcome some drawbacks.

1. Extract

1. It is the process of extracting raw data from all available data sources such as databases, files, ERP, CRM or any other.
   Transform
   The extracted data is immediately transformed as required by the user.
   Load
   The transformed data is then loaded into the data warehouse from where
   the users can access it.



The data collected from the sources are directly stored in the staging area. The transformations required are performed on the data in the staging area. Once the data is transformed, the resultant data is stored in the data warehouse. The main drawback of ETL architecture is that once the transformed data is stored in the warehouse, it cannot be modified again whereas in ELT, a copy of the raw data is always available in the warehouse and only the required data is transformed when needed.

Difference between ELT and ETL

Here are the following difference between ETL and ELT:

ELT	ETL
ELT tools do not require additional hardware	ETL tools require specific hardware with their own engines to perform transformations
Mostly Hadoop or NoSQL database to store data.Rarely RDBMS is used	RDBMS is used exclusively to store data
As all components are in one system, loading is done only once	As ETL uses staging area, extra time is required to load the data
Time to transform data is independent of the size of data	The system has to wait for large sizes of data. As the size of data increases, transformation time also increases
It is cost effective and available to all business using SaaS solution	Not cost effective for small and medium business
The data transformed is used by data scientists and advanced analysts	The data transformed is used by users reading report and SQL coders
Creates ad hoc views.Low cost for building and maintaining	Views are created based on multiple scripts.Deleting view means deleting data
Best for unstructured and non-relational data. Ideal for data lakes. Suited for very large amounts of data	Best for relational and structured data. Better for small to medium amounts of data