Skip to content
Related Articles

Related Articles

Improve Article

Difference between ELT and ETL

  • Last Updated : 30 Jan, 2020
Geek Week

1. Extraction, Load and Transform (ELT):
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:

  1. 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.
  2. Load:
    Loading is the process of storing the extracted raw data in data warehouse or data lakes.
  3. 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.

2. Extraction, Transform and Load (ETL):
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:
    It is the process of extracting raw data from all available data sources such as databases, files, ERP, CRM or any other.
  2. Transform:
    The extracted data is immediately transformed as required by the user.
  3. 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:

ELTETL
ELT tools do not require additional hardwareETL tools require specific hardware with their own engines to perform transformations
Mostly Hadoop or NoSQL database to store data.Rarely RDBMS is usedRDBMS is used exclusively to store data
As all components are in one system, loading is done only onceAs ETL uses staging area, extra time is required to load the data
Time to transform data is independent of the size of dataThe system has to wait for large sizes of data. As the size of data increases, transformation time also increases
It is cost effective ans available to all business using SaaS solutionNot cost effective for small and medium business
The data transformed is used by data scientists and advanced analystsThe data transformed is used by users reading report and SQL coders
Creates ad hoc views.Low cost for building and maintainingViews 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 dataBest for relational and structured data. Better for small to medium amounts of data

Attention reader! Don’t stop learning now. Get hold of all the important CS Theory concepts for SDE interviews with the CS Theory Course at a student-friendly price and become industry ready.

 

My Personal Notes arrow_drop_up
Recommended Articles
Page :