“ETL” and “ELT” are used somewhat interchangeably in our day-to-day use of the terminologies–in parlance, it’s still quite common among data folks to use “ETL” when referring to ETL or ELT.
However, the approaches taken by the two acronyms are distinctly and critically different.
Both approaches will extract (replicate) data from a source system using one of three main approaches: direct database connection, flat-file extraction or API. Both approaches will load data into a data warehouse and both approaches will apply some sort of transformation logic. The main difference is when and thus how the transformation is applied.
On ETL
In the older and more traditional “ETL” approach, the transformation is done after the data is extracted into a temporary location (a "staging area") and before the data is loaded. The full ETL process almost always requires a developer, and often requires a custom application to handle each step.
Since few people in an organization have the ability to read or modify the application, this makes the business logic in the “ETL” process harder to understand and also harder to modify.
On ELT
In the last decade, with the proliferation of APIs and the need to access data from cloud-based applications, “ELT” was born.
In this approach, the goal is to enable more users to apply their own business logic by replicating data from the source in the same format and then applying the transformation, post-load. This approach improves the speed and ease of making changes to the transformation logic, as this is often done in the database using SQL or Python and reduces the need for developers.
Is ETL Still Relevant?
If you’re building an application on an OLTP database, you will almost always choose “ETL,” as you can control changes to the data at the row level. If your goal is analytics, and you are using an OLAP database, you will almost always choose “ELT,” unless you have a specific use case where specific data needs to be cleansed or transformed before the load.
Managing PII (personally identifiable information) is an example of the ETL use case. Your business may require need to hash or remove the PII before loading it into your database and this would mean choosing and building ETL over ELT.
