What is the difference between ETL and ELT?

It’s a perennial debate: ETL or ELT?

‘E’ is the extraction of data from sources, ‘T’ is the transformation of data, and ‘L’ is the loading of the completed dataset into its destination. ETL transforms data before loading, while ELT transforms it afterwards.

But there are more differences between ETL and ELT than the order in which they are performed. Your choice between ETL or ELT will affect the data storage, processing, and analysis of your data pipeline, so it’s important to think it through before implementing a solution. Here, we’ll discuss the differences between the two methodologies.

ETL and ELT data pipelines

There’s a big difference between ETL and ELT in data warehousing. For example, OLAP (Online Analytical Processing) warehouses can only accept SQL-based data. If you’re using an OLAP destination, ETL may be more suitable since it transforms unstructured data into SQL data in an interim location before loading.

Additionally, ETL can reduce the transfer of sensitive data, such as email addresses and IP addresses, since these can be redacted in the interim stage.

On the other hand, ELT in a data warehouse involves cloud-based platforms, such as Google BigQuery, Snowflake, and Amazon Redshift. These types of data warehouses have the compatibility and flexibility to handle the storage and processing of data in an elastic manner, meaning the interim location isn’t required. ELT in a data warehouse involves cleaning, enrichment, and transformation inside the warehouse itself. The advantage here is that raw data is stored indefinitely, and can undergo multiple transformations.

ETL vs. ELT: pros and cons

There are many advantages and disadvantages between the two approaches. We’ve summarized the main ETL and ELT differences as follows:

ETL pros and cons:

1. More efficient with system resources, since it requires less processing and storage space to handle structured data.
2. Typically faster loading speed because records are only transformed once.
3. Usually lower availability, since data must be transformed in an interim location.
4. More difficult to debug, due to the gating of data earlier in the pipeline.
5. Easier to comply with regulations, since sensitive information can be removed, masked, or encrypted before it is loaded into the destination.
6. More rigidity, because structured data requires that the transformation process be altered to support new queries or analyses.

ELT pros and cons:

1. Less efficient with system resources, since the entire unstructured dataset is loaded and manipulated in the destination.
2. Typically slower loading speed due to additional transformations.
3. Usually higher availability, since all data is loaded into the data warehouse which means unstructured data can be accessed immediately.
4. Debugging is easier, due to all data available and retained indefinitely.
5. More difficult to comply with regulations, since laws such as GDPR and CCPA may prohibit the storage of sensitive information in the cloud.
6. More flexibility due to the availability of unstructured data and a large number of data formats.

When should you use ELT and when should you use ETL?

When considering ETL vs. ELT, the tools and type of data you have available are important in forming your decision. There is no hard and fast rule. But, generally speaking, situations involving large amounts of data and a cloud-based destination such as Microsoft Azure or Snowflake are more suited to ELT, whereas those with smaller amounts of data and legacy sources are more suited to ETL.

Another key question to answer is: which is more important – availability of the data as soon as possible, or a secure and compliant pipeline? ELT generally tends to suit the former, while ELT is usually better suited to the latter. The key is to carefully examine your sources and requirements so that you can make an informed decision.