ETL Alternatives: ELT (Extract, Load, and Transform)

by Katherine VasilegaJanuary 30, 2011
Sometimes, after extracting data, you need to transform information inside a target database, rather than before uploading the data.

Featured image credit

 

Several approaches to data integration

With plenty of data integration strategies to choose from, most organizations look for an ETL (Extract, Transform, and Load) data integration tool. However, depending on the particular business needs, a company may require a different method for their data integration initiative, such as data federation or real-time data integration.

Today, I will talk about the ELT (Extract, Load, and Transform) approach, also known as in-database integration. With this process, most of the data transformations occur after the information has been loaded into the target database. The order of transforming the data is the main difference between ETL and ELT.

Various approaches to integrating data (Image credit: TDWI)

 

How is ELT different?

ELT leads to some other major differences:

  • ETL operates at the integration server level, while ELT operates at the infrastructure level, using SQL and related procedural features of the relational database to optimize performance.
  • Transforming the data after it has reached the target helps to minimize costs, because software licensing and development costs in data warehousing initiatives can be much larger than the costs of infrastructure.
  • The ELT approach leverages the convenience of virtualization and cloud computing, which helps to speed up processes.

Choosing the right method is quite subjective, and people tend to stick to what they know. However, you can begin by carefully analyzing the requirements related to functionality, volumes, performance, and costs of both technologies.

Architectural options on where to perform data transformation (image credit)

For more on ELT, see this documentation from Oracle or this book from IBM.

 

Further reading


The post is written by Katherine Vasilega; edited by Alex Khizhniak.