DATA VALIDATION TESTING

What are things that has to be tested

• Whether source table/tables are present in Source schema?--include while creating the tc
• Whether target tables/tables are present in Target schema?--include while creating the tc
• If the data is extracted and loaded to staging tables, Whether staging table/tables are present in Staging schema?--include while creating the tc
• Also the three tables that are mentioned above should have correct data structure as that while creating the tables, for instance, id should be integer or long and name should for char/var as per the requirement.--include while creating the tc
• After the above checks are done, we have to verify that whether the ETL jobs that are created by developer are present at right location in Informatica.--include while creating the tc
• We must do the data validation once the job is executed.Data Validation is done through SQL query
• We have to check the count in target, count must be same as that source if no transformation rule is applied.--include while creating the tc
• Also in Data validation query, we must ensure that target tables has perfect data as that of source tables.--include while creating the tc

We must prepare source query with transformation as target query will have data after the transformation.

Source query with transformation 

MINUS 

Target Query

UNION

Target Query

MINUS

Source Query with transformation

Conclusion

1. We are checking the existence of the source tables and its structure
2. Also the same logic is applicable for staging as well as target tables, so we have to check the existence of staging and target tables.
3. Data Validation query must check count of rows of Target/Source and data is perfect as that of Target/Source.
4. Query must be created as exact as possible so that our main task of Data Validation is done perfectly.