How to Perform ETL Testing Using Informatica PowerCenter Tool

It is a known fact that ETL testing is one of the crucial aspects of any Business Intelligence (BI) based application. In order to get the quality assurance and acceptance to go live in business, the BI application should be tested well beforehand.

The primary objective of ETL testing is to ensure that the Extract, Transform & Load functionality is working as per the business requirements and in sync with the performance standards. 

Before we dig into ETL Testing with Informatica, it is essential to know what ETL and Informatica are.

What You Will Learn:

What you will learn in this ETL tutorial:

In computing, Extract, Transform, Load (ETL) refers to a process in database usage and especially in data warehousing that performs:

Informatica PowerCenter ETL Testing Tool:

Informatica PowerCenter is a powerful ETL tool from Informatica Corporation. It is a single, unified enterprise data integration platform for accessing, discovering, and integrating data from virtually any business system, in any

It is a single, unified enterprise data integration platform for accessing, discovering, and integrating data from virtually any business system, in any format and delivering that data throughout the enterprise at any speed. Through Informatica PowerCenter, we create workflows that perform end to end ETL operations.

Download and Install Informatica PowerCenter:

To install and configure Informatica PowerCenter 9.x use the below link that has step by step instructions:

=> Informatica PowerCenter 9 Installation and Configuration Guide

Understanding ETL testing specific to Informatica:

ETL testers often have pertinent questions about what to test in Informatica and how much test coverage is needed?

Let me take you through a tour on how to perform ETL testing specific to Informatica.

The main aspects which should be essentially covered in Informatica ETL testing are:

Classification of ETL Testing in Informatica:

For better understanding and ease of the tester, ETL testing in Informatica can be divided into two main parts –

#1) High-level testing

#2) Detailed testing

Firstly, in the high-level testing:

In a nutshell, you can say that the high-level testing includes all the basic sanity checks.

Coming to the next part i.e. detailed testing in Informatica, you will be going in depth to validate if the logic implemented in Informatica is working as expected in terms of its results and performance.

To sum up, we can say that the detailed testing includes a rigorous end to end validation of Informatica workflow and the related flow of data.

Let us take an example here:

We have a flat file that contains data about different products. It stores details like the name of the product, its description, category, date of expiry, price, etc.

My requirement is to fetch each product record from the file, generate a unique product id corresponding to each record and load it into the target database table. I also need to suppress those products which either belong to the category ‘C’ or whose expiry date is less than the current date.

Say, my flat file (source) looks like this:

(Note: Click on any image for enlarged view)

Based on my requirements stated above, my database table (Target) should look like this:

Table name: Tbl_Product

Prod_ID (Primary Key)Product_nameProd_descriptionProd_categoryProd_expiry_dateProd_price
1001ABCThis is product ABC.M8/14/2017150
1002DEFThis is product DEF.S6/10/2018700
1003PQRSThis is product PQRS.M5/23/20191500

Now, say, we have developed an Informatica workflow to get the solution for my ETL requirements.

The underlying Informatica mapping will read data from the flat file, pass the data through a router transformation that will discard rows which either have product category as ‘C’ or expiry date, then I will be using a sequence generate to create the unique primary key values for Prod_ID column in Product Table.

Finally, the records will be loaded to Product table which is the target for my Informatica mapping.

Examples:

Below are the sample test cases for the scenario explained above.

You can use these test cases as a template in your Informatica testing project and add/remove similar test cases depending upon the functionality of your workflow.

#1) Test Case ID: T001

Test Case Purpose: Validate workflow – [workflow_name]

Test Procedure:

Input Value/Test Data: Sources and targets are available and connected

Sources: [all source instances name]

Mappings: [all mappings name]

Targets: [all target instances name]

Session: [all sessions name]

Expected Results: Message in workflow manager status bar: “Workflow [workflow_name] is valid “

Actual Results: Message in workflow manager status bar: “Workflow [workflow_name] is valid “

Remarks: Pass

Tester Comments:



#2) Test Case ID: T002

Test Case Purpose: To ensure if the workflow is running successfully

Test Procedure:

Input Value/Test Data: Same as test data for T001

Expected Results: Message in the output window in Workflow manager: Task Update: [workflow_name] (Succeeded)

Actual Results: Message in the output window in Workflow manager: Task Update: [workflow_name] (Succeeded)

Remarks: Pass

Tester Comments: Workflow succeeded

Note: You can easily see the workflow run status (failed/succeeded) in Workflow monitor as shown in below example. Once the workflow will be completed, the status will reflect automatically in workflow monitor.

In the above screenshot, you can see the start time and end time of workflow as well as the status as succeeded.

#3) Test Case ID: T003

Test Case Purpose: To validate if the desired number of records are getting loaded to target

Test Procedure: Once the workflow has run successfully, go to the target table in database

Check the number of rows in target database table

Input Value/Test Data: 5 rows in the source file

Target: database table – [Tbl_Product]

Query to run in SQL server: Select count(1) from [Tbl_Product]

Expected Results: 3 rows selected

Actual Results: 3 rows selected

Remarks: Pass

Tester Comments:

#4) Test Case ID: T004

Test Case Purpose: To check if sequence generator in Informatica mapping is working fine for populating [primary_key_column_name e.g. Prod_ID] column

Test Procedure: Once the workflow has run successfully, go to the target table in database

Check the unique sequence generated in column Prod_ID

Input Value/Test Data: value for Prod_ID left blank for every row in source file

Sequence Generator mapped to Prod_ID column in the mapping

Sequence generator start value set as 1001

Target: database table- [Tbl_Product] opened in SQL Server

Expected Results: Value from 1001 to 1003 populated against every row for Prod_ID column

Actual Results: Value from 1001 to 1003 populated against every row for Prod_ID column

Remarks: Pass

Tester Comments:

#5) Test Case ID: T005

Test Case Purpose: To validate if router transformation is working fine to suppress records in case the product category is ‘C’ or the product has got expired.

Test Procedure: Once the workflow has run successfully, go to the target table in database

Run the query on the target table to check if the desired records have got suppressed.

Input Value/Test Data: 5 rows in the source file

Target: database table – [Tbl_Product]

Query to run in SQL server: Select * from Product where Prod_category=’C’ or Prod_expiry_date < sysdate;

Expected Results: no rows selected

Actual Results:  no rows selected

Remarks: Pass

Tester Comments: (if any)

#6) Test Case ID: T006

Test Case Purpose: To check the performance of the workflow by recording the workflow runtime.

Test Procedure:

Input Value/Test Data: Workflow has run successfully

Start time of workflow in monitor

End time of workflow in monitor.

Expected Results: 2 min 30 secs

Actual Results:  2 min 15 secs

Remarks: Pass

Tester Comments: Considering the test as ‘Pass’ in case the actual run duration is +/- 10% of expected run duration.

#7) Test Case ID: T007

Test Case Purpose: To validate data at target table column level in order to ensure that there is no data loss.

Test Procedure: Once the workflow has run successfully, go to the SQL Server.

Run the query on the target table to check there is no data loss.

Input Value/Test Data: Workflow has run successfully

One sample record from source flat file.

SQL Query: Select Top 1 * from Tbl_Patient;

Expected Results:

1 row returned

Prod_ID (Primary Key)Product_nameProd_descriptionProd_categoryProd_expiry_dateProd_price
1001ABCThis is product ABC.M8/14/2017150

Actual Results: 

1 row returned.

Prod_ID (Primary Key)Product_nameProd_descriptionProd_categoryProd_expiry_dateProd_price
1001ABCThis is product ABC.M8/14/2017150

Remarks: Pass

Tester Comments: Considering the test as ‘Pass’ in case the actual run duration is +/- 10% of expected run duration.

Benefits of Using Informatica as an ETL tool:

Informatica is a popular and successful ETL tool because:

Some useful Tips to assist you in Informatica ETL testing:

Conclusion:

So, we have seen in detail, some of the sample test cases that can be used as a template to cover ETL testing in Informatica. As I mentioned earlier, you can add/remove/modify these test cases depending on the scenario you have in your project.

As I mentioned earlier, you can add/remove/modify these test cases depending on the scenario you have in your project.

The Informatica PowerCenter is a foundation for any data integration activities.

You can easily perform script-free automated testing of data copied to test, dev or production environment, and that is the reason why PowerCenter is the most popular ETL tool nowadays.

Recommended reading => ETL vs. DB Testing – A Closer Look at ETL Testing Need

About the author: This is a guest article by Priya K. She is having 4+ years of hands-on experience in developing and supporting Informatica ETL applications.

Feel free to post your queries/comments about this ETL tool.