Question

Explain the ETL process in three or more paragraphs. Be sure to include the definition of ETL, what happens in each stage and what the final result is. Be sure to spend a significant amount of effort explaining the T stage.

Answer 1

ETL process

Many organizations having different types of huge amounts of data. They are choosing a different platform to store those huge amounts of data. But nowadays along with this those huge amount of data integrated into a single place is very important. So, the organization needs ETL means Extract, Transform, and Load.

Extract means collect data from the different source systems into the staging area. Actually, in this process, the data is reading from the database and collect those data. The extraction satisfies the record with source data. Extraction takes care of that kind of data which is spam. Along with this, it can remove duplicates and check data types. The extraction is done by some methods (Ful Extraction, Partial extraction without update notification, Partial extraction with update notification)

Transform is nothing but the process of converting the extracted data from its previous form. Because data coming from the source is raw and not usable therefore need to clean and map together So, we can perform a customized operation on data. For example: suppose there is one table where information about an Employee, and here the first name and last name of that employee is in a different column so, it can possible to concatenate them before loading. In basic transformation cleaning the data, avoid or remove duplicates like this but in advance transformation consist of selecting certain rows or columns knows as Filtering. Splitting a single column into multiple columns know as Splitting. If there is such type of scenario as the first three columns in a row are empty then that time rejects the row from processing such type of Data validation is done in transformation. In this, there is using rules and lookup tables for data standardization.

Load Is the process of writing the data into the target data warehouse database. Now here a large amount of data need to be loaded in a relatively short period so that load process should be optimized for performance. The main thing is that if some problem is occurring and load failure then there is a need for a recovery mechanism so that restart the process from the point of failure without data integrity loss. So that to check some points, to check that combined values and calculated measures. To ensure that the key field data is neither missing and not null.

So, yes ETL provide clean, and filtered data structure along with this gives us data delivery capabilities, data transformation capabilities, data modeling, data management capabilities.