ETL vs. ELT: What's Your Choice?

The pros and cons of ELT, and why we adopted it at DelightRoom

Did you know there are various approaches to collecting, processing, and preparing data for analysis? Today, I want to talk about the two main operational models for data pipelines: ELT and ETL. Specifically, we'll look at why DelightRoom chose the ELT approach and what benefits we expect from it.

What is ETL (Extract — Transform — Load)?

In the context of data processing, ETL refers to the traditional method of Extracting, Transforming, and Loading data. Just as the names suggest, each step consists of 'extracting' data from the source, 'transforming' it into the desired format, and 'loading' it into the final destination.

However, the crucial point here is the 'transform' stage. ETL processes the data before storing it in its destination. This is a process designed to selectively store only the data we need and utilize our storage efficiently.

ETL has the following advantages:

• Efficient use of storage space: Since only strictly necessary data ultimately piles up in our destination data store, you use storage space efficiently.

• Security and compliance: During the transformation process, it is easy to remove sensitive information from the original data or comply with various regulations like GDPR and HIPAA.

• Proven technology: Given its long history, a lot of relevant knowledge has accumulated so far, making it easier to find tools and experts.

However, it also has the following disadvantages:

• Tricky maintenance: As the types and volume of data grow, customizing and maintaining the transformation process becomes increasingly complex.

• High operational costs for the transform stage: Processing massive amounts of data requires significant computing resources during the transformation phase. This requires a separate system, which drives up operational costs accordingly.

• Low flexibility: Because the final format of the data is already defined during the transformation stage, it is difficult to respond when a different format of data is needed later.

What is ELT (Extract — Load — Transform)?

Unlike ETL, ELT is a method where data is extracted, immediately loaded, and then transformed. This approach has recently started to gain more attention alongside the advancement of modern cloud data warehouses.

The advantages of ELT are as follows:

• Simple system architecture: Because data transformation takes place in the data warehouse, you have fewer worries about complex networking or computing resource management.

• High flexibility: Since you retain the original data, it can be reprocessed whenever needed.

• Outsourcing the load process: Even if the data isn't in a perfectly clean format, you just load the raw data as-is and handle the transformation internally, making it easy to outsource the loading process. Especially when you need to fetch data from various sources, you can save the costs of directly implementing and maintaining the extract and load parts.

On the other hand, ELT also has the following disadvantages:

• Security and compliance: Because all the raw data is loaded first and transformed afterward, you might end up loading data that doesn't meet the regulations you need to comply with.

• Inefficient use of storage space: Because you pre-load and use more data than what is actually strictly needed right now, you inevitably use up more storage space.

Why did DelightRoom adopt ELT?

DelightRoom initially used the ETL method. Over time, however, we felt the complexity and operational costs of the transformation layer increasing. Additionally, as the data volume grew, we encountered several issues during the transformation and loading processes, such as traffic costs, time consumption, and network transmission failures.

To solve these problems, we decided to adopt the ELT approach. The ELT method fundamentally loads data first and processes it within the data warehouse as needed. Thanks to this, the number of elements to manage has decreased, and we gained more freedom and flexibility in data modeling.

Given the nature of a small startup where engineer resources are incredibly precious, being able to outsource the EL (Extract-Load) part was also a crucial factor. By using services like Fivetran, we minimized custom development and stably loaded data from various sources. Or, depending on the third party, if they have a feature that pushes data to our data lake (AWS S3), we actively utilize it to simplify our pipeline.

Since we have already ingested all the data, transformation can be performed within a single data warehouse. At this point, with the help of tools like dbt, we can define the data modeling and transformation processes with consistent code (SQL + a) and handle documentation and change tracking in a standard way. We will introduce this in more detail in a separate post.

In summary, by spending more on storage space, we achieved a triple win of reducing development costs and operational complexity while gaining flexibility. In modern cloud environments, storage is quite cheap, so this tradeoff was definitely worth taking.

Conclusion

The ELT method presents one answer to the complexity and cost issues of modern data environments. It is a huge help in increasing data management flexibility, cutting costs, and reducing system complexity. However, it might not fit every situation, so you need to carefully weigh the pros and cons. Gradually experimenting with it on a part of your pipeline is also an option. If you are running a data-centric business and are still sticking to the ETL approach, I highly recommend trying out ELT!