Modern ETL vs. ELT: Benefits & Considerations

When ETL is not enough, it is time to consider ELT. Read about the main differences and business applicability.

Polina Zyaparova

Polina Zyaparova

According to a recent research by Gartner, by 2022, 75% of all databases will be deployed or migrated to a cloud platform. This means that organisations will be having a growing need of cloud services and a suitable supporting stack for them. With this in mind, it is logical to ask whether a change in the way how companies process data is inevitable, and whether new approaches need to be adopted.

What is ETL?

A quick recap: ETL - Extract, Transform, Load. A traditional process for data manipulation in an organisation. Originally, it became a popular tool in 1970s, and still is a key part in data-driven organisations and enterprises. ETL is used in the movement and transformation of data collected from multiple data sources and loading it to the target databases, in accordace with business objectives. Those data bases can be data warehouses.

etl

The data that enters a data warehouse is already integrated and cleansed, so at the end what you receive is a ready-to-analyse data for reporting and decision-making purposes. In some cases, ETL can be a lengthy process with a transformation stage taking days and even weeks to be finalised. And this is a pain point #1 that we will further address.

You already could have guessed that since 1970s, many things have changed. Data sources have diversified. Business needs have grown to be more intricate, tech capabilities have significantly advanced. Is ETL able to efficiently handle data from a new-wave of sources, like IoT (Internet-of-Things), geolocation data, logistics, log data, Social Media, sensor data and much-much more? What about Big Data?

ETL Limitations in the Modern Business Environment

ETL may not be matching your current business needs in sevaral cases:

  1. As we have already mentioned as the result of the ETL execution, you get a structured and cleansed data, but you miss on loads of unstructured raw data, the type of data that is needed for your tech and data science departments.
  2. When you execute an ETL you already need to know beforehand how you will be using this data, which is not always possible to accurately predict, and takes away the element of research and "data exploration".
  3. You transform the whole volume of data that you might not even need in the future. You end up spending lot's of recourses and time on what may not be used.
  4. ETL happens on-premise.
  5. The faster your data grows and gets diversified, the heavier it will be on ETL to handle it. The speed will gradually slow down, the quality of data will deteriorate, what may lead to inaccurate analytics, lost opportunities, and revenue.

What is ELT?

ELT - Extract, Load, and Transform. Same actions, but a different order, a modern alternative to a traditional ETL.

elt

Unlike ETL, ELT can import and export both structured and unstructured data from multiple sources, including, for example, IoT sensors. ELT takes all the benefits from the cloud: scalability and flexibility, and is able to work with data both from on-premise and cloud-data warehouses. ELT works with Data lakes, a Big Data storage repository, which are perfect for high-volume, diverse data sources for the following purposes:

  • to perform Big Data analytics.
  • to store raw and unstructured data long-term.
  • to unify data sources.
  • to train Machine Learning and AI.

Cloud data lake will serve both your business and the data teams as your organisation will be able to keep both unstructured & structured data.

The main difference between ETL and ELT is that after the data gets extracted from its sources, it gets loaded in its raw state into a data warehouse or data lake. It means that later on you can choose what you need to transform, thus, the transformation stage happens much faster, and the data loading happens faster since there is no need to wait for the transformation step to execute. Though, at the same time it means, that the analysing stage will happen much later in ELT compared to ETL, and this you need to take into account.

Considerations

When choosing between ETL and ELT, the most important thing is to outline the goals that you want to ultimately achieve. Neither ETL nor ELT are universal approaches to solve all your challenges, but applied together and used correctly, you will be able to take the most of your data and data insights.

etl / elt table

Frends takes into account these considerations and supports both ETL and ELT processes.

Our integration platform frends has in-place ready-made capabilities for ETL integration scenarios and can process data in almost any format, including JSON, XML, CSV, Flat File, Fixed Width, EDI. In addition, built-in mechanisms to prevent the most commonly known vulnerabilities attacks are in place, and can be easily executed. Frends is also able to load data into SQL by using Dapper ORM for creating batch queries, what will relieve developers from a significant portion of programming tasks.

Extract-Load-Transform scenarios within the platform happens when, for example, a Customer is running in distributed and segregated networks, and the data is supposed to be processed, filtered and enriched close to the persisting of the data. In large corporations this means messaging exchange across organization boundaries. Business critical data and business rules verification in addition to machine learning and artificial intelligence will happen as close as possible to the master data.

Frends supports the process of data analysis and advanced BI by providing access to commerical plug-and-play connectors.