According to recent research by Gartner, by 2022, 75% of all databases will be deployed or migrated to a cloud platform. This means that organisations will have a growing need for cloud services and an appropriate supporting stack. With this in mind, it is logical to ask whether a change in 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 accordance with business objectives. Those databases can be data warehouses.
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 the 1970s, many things have changed. Data sources have diversified. Business needs have grown to be more intricate, and 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 more? What about Big Data?
ETL Limitations in the Modern Business Environment
ETL may not be matching your current business needs in several cases:
-
As we have already mentioned, as the result of the ETL execution, you get structured and cleansed data, but you miss loads of unstructured raw data, the type of data needed for your tech and data science departments.
-
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".
-
You transform the whole volume of data that you might not even need in the future. You end up spending lot's of resourses and time on what may not be used.
-
ETL happens on-premise.
-
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, which 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.
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 than ETL, which you need to take into account.
Considerations
When choosing between ETL and ELT, the most important thing is to outline the goals you want to achieve. Neither ETL nor ELT are universal approaches to solve all your challenges, but applied together and used correctly, you will be able to make the most of your data and data insights.
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 can also load data into SQL by using Dapper ORM for creating batch queries, which will relieve developers from a significant portion of programming tasks.
Extract-Load-Transform scenarios within the platform happen 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 organizational 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.
