Each organization categorize testing types by their own way based on the testing practice or testing strategy build @ organization level. It holds true for ETL testing also. Sometimes, for larger projects / programs, it vary from client to client. Generally, below are the main types of testing that are covered under ETL testing:
- Reconciliation testing: Sometimes, it is also referred as ‘Source to Target count testing’. In this check, matching of count of records is checked. Although this is not the best way, but in case of time crunch, it helps.
- Constraint testing: Here test engineer, maps data from source to target and identify whether the data is mapped or not. Following are the key checks: UNIQUE, NULL, NOT NULL, Primary Key, Foreign key, DEFAULT, CHECK
- Validation testing (source to target data): It is generally executed in mission critical or financial projects. Here, test engineer, validates each data point and match source to target data.
- Testing for duplicate check: It is done to ensure that there are no duplicate values for unique columns. Duplicate data can arise due to any reason like missing primary key etc. Below is one example:
- Testing for attribute check: To check if all attributes of source system are present in target table.
- Logical or transformation testing: To test any logical gaps in the. Here, depending upon the scenario, following methods can be used: boundary value analysis, equivalence partitioning, comparison testing, error guessing or sometimes, graph based testing methods. It also covers testing for look-up conditions.
- Incremental and historical data testing: Test to check the data integrity of old & new data with the addition of new data. It also covers the validation of purging policy related scenarios.
- GUI / navigation testing: To check the navigation or GUI aspects of the front end reports.
In case of ETL or data warehouse testing, re-testing or regression testing is also part of this effort. Their concept / definition remains the same.
