4 Architecting IoT Cloud
215
Extract & Load
Transform
(applied in target
database directly)
Source database
Target database
Transform
Source database
Target database
Extract
Load
ETL
ELT
Fig. 4.29 A simple example of ETL and ELT
4.6.3.1 ETL (Extract, Transform, and Load) and ELT (Extract, Load,
and Transform)
As discussed above, one of the key differences between data lakes and data
warehouses is the way they process data. Let us examine the three stages – E, T,
L (see Fig. 4.29):
• Extraction – Reading and collecting raw data from one or several data databases
and routing it to a temporary repository
• Transformation – Converting, filtering, cleaning, processing, and aggregating
the extracted data from the previous stage. Finally, structuring the output into
a specific form to match the structure of the target database
• Loading – Writing the structured and converted data from the previous stage into
the target database or data warehouse
ETL (Extract, Transform, and Load) occurs within a data warehouse while
ELT (Extract, Load, and Transform) occurs within a data lake. Generally, ETL
is a continuous process that occurs using a clearly defined workflow. First, ETL
extracts data from similar or diverse data sources. Next, the data is cleaned,
enhanced, transformed, and finally stored in a data warehouse or in a database.
On the other hand, with the ELT approach, once data is extracted, loading begins
immediately, and all data are transferred to a consolidated data storage area.
However, transformations are performed in the target system. In other words, instead
of transforming the data before it is written in the database, in ELT the process of
transforming the data is completed by the target database. Therefore, ELT minimizes
the processing on the source since the transforming is done in the target system.
215
Extract & Load
Transform
(applied in target
database directly)
Source database
Target database
Transform
Source database
Target database
Extract
Load
ETL
ELT
Fig. 4.29 A simple example of ETL and ELT
4.6.3.1 ETL (Extract, Transform, and Load) and ELT (Extract, Load,
and Transform)
As discussed above, one of the key differences between data lakes and data
warehouses is the way they process data. Let us examine the three stages – E, T,
L (see Fig. 4.29):
• Extraction – Reading and collecting raw data from one or several data databases
and routing it to a temporary repository
• Transformation – Converting, filtering, cleaning, processing, and aggregating
the extracted data from the previous stage. Finally, structuring the output into
a specific form to match the structure of the target database
• Loading – Writing the structured and converted data from the previous stage into
the target database or data warehouse
ETL (Extract, Transform, and Load) occurs within a data warehouse while
ELT (Extract, Load, and Transform) occurs within a data lake. Generally, ETL
is a continuous process that occurs using a clearly defined workflow. First, ETL
extracts data from similar or diverse data sources. Next, the data is cleaned,
enhanced, transformed, and finally stored in a data warehouse or in a database.
On the other hand, with the ELT approach, once data is extracted, loading begins
immediately, and all data are transferred to a consolidated data storage area.
However, transformations are performed in the target system. In other words, instead
of transforming the data before it is written in the database, in ELT the process of
transforming the data is completed by the target database. Therefore, ELT minimizes
the processing on the source since the transforming is done in the target system.