QA Tutorials

ETL TESTING

SYSTEM TESTING

ETL testing is a process done to ensure that the data is properly loaded from source to destination after the business is transformed accurately. ETL testing involves the verification of data at various middle stages that are being used between source and destination.

What is Data Warehouse?

Data warehouse is a huge collection of information from different sources. This information can be used for decision making and planning. It is a kind of database which is designed for querying, analysis and transaction processing. It helps the company or any organisation to consolidate data from several sources and separates analysis workload from transaction workload. Data is turned in to high quality information to meet the enterprise documenting requirements for all levels of users.

What is ETL?

It is the process of how data is loaded from different sources to data warehouse. It stands for Extract-Transform-Load. Data is extracted from the OLTP database and transformed to match the data warehouse schema and it is been loaded into the data warehouse database. For Example if there is a company it has many departments like Sales, Marketing and HR department. Each department have maintained the data of Employee separately. Some departments will store employee information by the employee’s name and some of the departments will store employee’s information by employee-Id. If they want to check the employee’s history then it will be tedious job. The solution is to use the data warehouse to store information from different sources in a uniform structure by ETL. ETL can transform dissimilar data into uniform structure.

 

ETL Testing Process

The different ETL testing phases are

ETL TESTING
ETL TESTING

 

 

ETL Testing performed in 5 stages:

  1. Identifying data sources and requirements
  2. Data acquisition
  3. Implement business logics
  4. Build and populate data
  5. Build reports

Types of ETL testing:

  1. Production validating testing: This type of testing is done to data as it is moving  to production systems. To support  your  business decision, the data in the production systems should be in the correct order. Data validation option ETL testing automation and management capabilities to confirm that the production systems  does not compromised by the data.
  2. Metadata Testing: It includes testing of data type, data length, constraint of data.
  3. Data completeness testing: data completeness testing is done to assure that the data is loaded in target from the source. Some tests that run are compare and validate counts.
  4. Data Accuracy testing: This testing is done to ensure that the data is  accurately loaded  and transformed as expected.
  5. Data Transformation testing: It verifies the data is correctly transformed according to the business availabilities.

Questions

  1. What is ETL Testing?
  2. What are advantages of ETL Testing?
  3. What are the types of ETL Testing?

 

Facebook Comments
Tags

Related Articles

2 thoughts on “ETL TESTING”

  1. —>what is ETL testing?
    ETL testing is done to ensure that the data that has been loaded from a source to the destination after business transformation is accurate. It also involves the verification of data at various middle stages that are being used between source and destination. ETL stands for Extract-Transform-Load.
    —->what are the types of ETL testing?
    1) Data Transformation testing: It verifies the data is correctly transformed according to the business availabilities.
    2) Production validating testing: This type of testing is done to data as it is moving to production systems. To support your business decision, the data in the production systems should be in the correct order. Data validation option ETL testing automation and management capabilities to confirm that the production systems does not compromised by the data.
    3) Metadata Testing: It includes testing of data type, data length, constraint of data.
    4) Data completeness testing: data completeness testing is done to assure that the data is loaded in target from the source. Some tests that run are compare and validate counts.
    5) Data Accuracy testing: This testing is done to ensure that the data is accurately loaded and transformed as expected.
    —>what are the advantages of ETL testing?
    -Increased query and system performance
    -Timely access to data
    -Enhanced quality and consistency
    Data is essential for businesses to make the strategic business decisions. ETL testing plays a considerable role in validating and ensuring that the business information is exact, consistent and reliable. Hazardous of data loss in production is also minimized.

  2. ETL testing:
    ETL testing: It stands for Extract-Transform-Load. It is a middle stage testing process to validate that the data has been transformed and loaded into the target as expected.
    Warehouse data: is a huge collection of information from different sources and is a technique for collecting and managing data from varied sources to provide meaningful business insights.
    Advantages:
    ETL Testing Advantages:
    1.ETL is used to transfer or migrate the data from one database to another, to prepare data marts or data warehouses.
    2.ETL testing significantly reduces the risk of data loss.
    3. Data quality is essential for informed decision making.

    ETL Testing Types:
    1) Production Validation Testing:
    It is also called as Table balancing or product reconciliation. It is performed on data before or while being moved into the production system in the correct order.
    2) Source To Target Testing:
    This type of ETL Testing is performed to validate the data values after data transformation.
    3) Application Upgrade:
    It is used to check whether the data is extracted from an older application or new application or repository.
    4) Data Transformation Testing:
    Multiple SQL queries are required to be run for each and every row to verify data transformation standards.
    5) Data Completeness Testing:
    This type of testing is performed to verify if the expected data is loaded at the appropriate destination as per the predefined standards.

Leave a Reply

Your email address will not be published. Required fields are marked *

Close