Course Overview

ABOUT ETL AND DATA WAREHOUSE

What is Data warehouse?

The Data Warehouse is nothing but the Database where we are collecting the historical data from different sources and storing those data in the form of a table which can be used for the analysis purpose. We can select any Database as our Warehouse (e.g. Teradata, MySQL, SQL Server, etc). A Data Warehouse is a Database which is being constructed by integrating data from multiple heterogeneous sources. It is designed for the query and analysis not for the transition processing. It support decision making and Analytical Processing.

What is ETL?

ETL comes from the concept of Data Warehouse only. ETL stand for Extract Transfer Load. ETL is a process where data is being extracted from the sources, transfer and loaded to the destination. Data can be extracted from the OLTP database and transformed to the target data warehouse area. Also there are many data which are being extracted from the Non-OLTP system like text file, spreadsheet, etc. ETL testing usually refer to a process of validating the data, verifying and also qualifying data while preventing duplicate data or records and data loss.

Example

Suppose we have CUSTOMER table in the OLTP and DIM_CUSTOMER table in Data Warehouse.

The developer develop the ETL program which retrieve the data from CUSTOMER table to DIM_CUSTOMER table.

If there are 10,000 records available in current build and new required load is 3,000 records. So After Loading it’ll be 13,000 by ignoring duplicate records.

Meaning of ETL
Extract

• Extract the relevant data.
• Extract can be of two Types:
  
  
  • Homogeneous: When the data are extracted from the same type of source then it is called as Homogeneous.
  • Heterogeneous: When data are extracted from the different type of source then it is called as Heterogeneous.

Transformation

• Transformation is a set of rules which is generate and modify or pass data.
• Transfer data to the data warehouse by using some rule or key where key is one or more data attribute which uniquely identify an entity. There are different type of key like primary key, forging key, alternate key, surrogate key, composite key, etc.
• Before transferring the data from the source cleaning of data happen. In Cleaning does the omission and identify the error fixing. This improve the data quality and solve the problem of source system.
• Transformation can be of 2 Types:
  
  • Active: In an Active transformation the number of row that is created as output can be changed once a transformation has been occurred.
  • Passive: In case of Passive transformation the number of row that is created as output cannot be changed once a transformation has been occurred.

Load

• Load the Data into Data Warehouse.
• Load are of two Types:
  
  
  • Incremental/Delta Load: Keeping the Previous Data New data will be Loaded by ignoring Duplicate Records.
  • Full/Initial Load: All data will be loaded freshly and next time if the new data comes then the old data will be replaced

Student Review/Feedback

1. Urbanpro: https://www.urbanpro.com/hyderabad/prakash/reviews/7733086
   
   
2. Google : https://g.page/r/CQ1PnQcgptn1EAQ/review
   
   
3. Justdial : https://www.justdial.com/Hyderabad/Digital-It-Training-Hub-Behind-Kphb-Bus-Stop-Beside-Kalamandir-Kukatpally/040PXX40-XX40-190218123755-M8E2_BZDET
   
   
4. Sulekha : https://www.sulekha.com/digital-it-training-hub-and-placement-kukatpally-hyderabad-contact-address
   
   
5. Website Testimonials : https://digitalhubtech.co.in/testimonials/