I have already encountered several quotes on ETL on the internet, especially when I began to need to use it in my demands.
I noticed that many are disclosing ETL as a Program / Software, which is not true because ETL is a process of Extraction, Transformation, and Data Loading. We will see below information that will clarify this relationship.
In addition to talking about ETL, I will list the main tools on the market and talk a little bit about Labor Market.
But before we go to the purpose of this article, I would like to contextualize a little of my discovery with ETL.
It all started a few years ago when I was working for a company that provided Logistics and Transportation services, having IT services as an integral part of its portfolio.
As we offered our own technology to manage the stock and transport of customer loads, we had to integrate our systems with the customer’s ERP to launch data related to its operation.
Early on, every process was done by hand, using large data loads (such as immense SQL Scripts), procedures, DBLinks, and so on. It turns out that, over time,
operations increased and we began to realize that the performance of these transactions were degrading our environment, and we were wasting time managing the data loads.
I continued to study a little more and discovered that I was not making advances because of a tool but because of a process, the ETL.
But what is ETL?
ETL is an acronym that stands for Extract Transform Load (Extraction Transformation Loading).
It is a process where we search for data from a source, transform this data into a format that our target database can understand / deliver and deliver the transformed data to this database, offering the benefit of integrating distinct databases (SQL Server with Oracle for example), provide better performance, programmed loads and file generation in various formats and layouts (obeying the data structure that ERP systems understand).
The ETL Process is commonly used for Data Mart, Data Warehouse as well as other systems.
In general, the ETL can be represented by the image below:
In Wikipedia we can see definitions about ETL as follows:
ETL Extract Transform Load is a process that is used by software tools whose function is the extraction of data from various systems,
transformation of such data according to business rules and ultimately loading data generally to a Data Mart and / or Data Warehouse, as well as to a
organization (ie it is not only used for Data Warehouse but for multiple purposes).
Extraction and loading are mandatory for the process, with the transformation / cleaning optional (but they are good practices), since the data has already been sent to the
destination system. It is considered one of the most critical phases of the Data Warehouse and / or Data Mart.
Data warehouse projects consolidate data from different sources. Most of these fonts tend to be relational databases or text file (plain text), and other sources may exist.
Currently, there are several tools in the market to work with ETL of which I highlight the following:
Oracle Data Integrator (ODI);
Microsoft Integration Server (MSIS);
IBM InfoSphere DataStage;
Computer Power Center;
SAP BusinessObjects Data Services;
Pentaho Data Integration (this one has free and very good versions to use).
All tools have the same purpose as collecting data, transforming and delivering the expected result. I often compare them with Cars. Everyone does the same thing as promoting locomotion, but what differentiates one from the other is the “whims” made available.
As for the LABOR MARKET, I analyze that we are in a good moment since Data Science is on the rise.
In this way you can act, for example, as:
Data Analyst, BI Analyst and Systems Integration Analyst.
I end by stating that working with ETL is to be in a very good working area and broad since you can work in many areas. If you’re thinking about it, good luck.
I’ll stay here. If you have any questions, please contact us.
Strong hug.
Eduardo Santana