All ETL processes They consist of three different phases:
1) Extraction.
2) Transformation.
3) Load.
To these three parts must be added the stage of cleaning that, as we have commented, is understood at this time as a separate part of the procedure itself.
Then, we are going to explain the main aspects of the operation of each of the phases, as well as its keys and issues to consider in its approach and implementation.
The different steps of the extraction procedure.
The first part of the ETL procedure is to extract the data from the source system. This stage consists of three steps:
-
Prior needs analysis. Regularly, there is a first stage of analysis where the specific needs of the organization are evaluated in terms of data movement and transformation. In general, you don't start extracting all the information, Instead, you start with the treatment of specific data according to a specific need and then make extensions as they are needed.
-
File identification. Before extracting the data, Identify what type and in what format the source systems are. As an example, whether it is relational or non-relational databases, flat files, complex files, VSAM files, WebServices, Main frame, Excel files, etc. Regularly, data from source systems comes from different formats, to be merged.
-
Data extraction. Depending on the needs detected, the data itself is extracted.
Extraction modes
Simply, there are three different extraction modes. The type of need of the organization is what, regularly, will determine the choice one way or another.
Full excerpt or total extraction
This method is about extracting all the data. In this circumstance, entire tables that can have millions of records are swept.
Incremental statement or incremental extraction
Only what was modified or added is processed in batches. In addition, there may be rows that are eliminated because they are duplicated, incorrect data, etc.
Update notification or update notification
In this circumstance, data is only extracted when an update occurs (as an example, an insert).
These three types of extraction are handled by a module called Capture of modified data (CENTERS FOR DISEASE CONTROL AND PREVENTION).
Keys to the extraction procedure
The ideal extraction procedure is the one that is barely noticeable.
Any organization, company or company must demand that the procedure for extracting data from its source or sources causes the least possible impact. In other words, no problem for workers who work with data and records on a daily basis.
Extracting too large a quantity of data at the same time can slow down and even crash the system. Thus, It is essential to assess very well the needs and scope of the operation to be carried out and, in your case, perform the operation stepwise in smaller blocks and / or on the most appropriate dates and times to achieve that minimum impact.
