Testing in Data warehouse

Data Warehouse stores huge amount of data, which is typically collected from multiple heterogeneous source like files, DBMS, etc to produce statistical result that help in decision making.

Testing is very important for data warehouse systems for data validation and to make them work correctly and efficiently.
There are three basic levels of testing performed on data warehouse which are as follows :

1. Unit Testing –
   This type of testing is being performed at the developer’s end. In unit testing, each unit/component of modules is separately tested. Each modules of the whole data warehouse, i.e. program, SQL Script, procedure,, Unix shell is validated and tested.
2. Integration Testing –
   In this type of testing the various individual units/ modules of the application are brought together or combined and then tested against the number of inputs. It is performed to detect the fault in integrated modules and to test whether the various components are performing well after integration.
3. System Testing –
   System testing is the form of testing that validates and tests the whole data warehouse application. This type of testing is being performed by technical testing team. This test is conducted after developer’s team performs unit testing and the main purpose of this testing is to check whether the entire system is working altogether or not.

Challenges of data warehouse testing are :

• Data selection from multiple source and analysis that follows pose great challenge.
• Volume and complexity of the data, certain testing strategies are time consuming.
• ETL testing requires hive SQL skills, thus it pose challenges for tester who have limited SQL skills.
• Redundant data in a data warehouse.
• Inconsistent and inaccurate reports.

ETL testing is performed in five stages :

• Identifying data sources and requirements.
• Data acquisition.
• Implement business logic’s and dimensional modeling.
• Build and populate data.
• Build reports.