Automation of Data Warehouse Testing
The group risk and regulatory data warehouse of this Tier 1 Banking client, was critical for corporate decision-making and for predictive analysis. The huge volume of data being loaded into the data warehouse meant that the manual testing of this data was exhaustive and extremely impractical. Data was being fed into the system from various sources, at different times and in different formats, yet consistent processing was required and the current scope of testing was to be preserved. Performance testing through a manual approach was also extremely difficult and testers were suffering from testing fatigue. Critically, the existing release deadlines could not be impacted and testing costs had to be contained.
To combat these challenges, the ACS team recommended that an automated testing approach was quickly developed and implemented.
The team created a reconciliation procedure to perform a ‘Data Comparison’ (either relating to the Total Records Count or Sum of Measure’s values by each and every record of a table moving to another table or one database to another database). This also helped identify anomalies in the transformed or migrated data.
The team created an automation script around data count, data structure, sum of data, union of data and intersect of data to meet the above reconciliation challenge:
- Scripts were created to validate data from source to external layer.
- Scripts were created to validate data from external to staging layer.
- Scripts were created to validate data from staging to relational layer.
The standardization of data at the staging area was a key activity as the staging area contained a summation of all data sources. ACS implemented the business rules at the relational layer and performed the very complex mapping transformation logic over the data therein. Report testing included slice and dice, analytics, trends and huge volumes of processed data.
A data testing framework (DTF) comprising SQL / PLSQL scripts, test cases, test scenarios and sample test data was created. This helped in validating Data Completeness, Data Transformation, Data Quality, End-to-End Integration testing, Regression testing, Performance testing and Business Intelligence Reports validations.
Treasury issuance consistent between financial forecasting, ALM and capital planning applications.
- Automation scripts reduced human errors and increased data quality.
- Automation improved the validation process.
- Automation delivered reduced cycle time (due to automation, issues identified early, fewer defects etc)
- Automation increased re-usability.
- Automation improved data testing coverage because all layers were thoroughly verified.
- Automation provided the capability to test heterogeneous sources of data.