ETL Testing Steps

ETL Testing Steps

Testing process and methodology in normal Manual/Black Box testing is a bit different from ETL testing. Like Black Box testing, ETL testing process also involves steps which seem similar but most of us might be having some sort of grey area in understanding the flow.

Test Data Preparation always plays a vital role in any type of testing; be it manual, be it ETL testing. Selecting the proper set of test data makes the testing effective. Though in many cases, it is noticed that the source data from the existing system is fed into the staging area and then towards the DW area subsequently. In those cases, the possibility of making the exhaustive test data becomes a bit difficult sometimes but we can proceed by fetching different combinations of data using sql queries.

For example, we can use a subset of production like data into the test environment to make ourselves familiar with the nature and variations of the data. This can be done confidently when we can understand both the source and the target tables, attributes, their data types etc.

During Test Execution Phase, as per the mapping sheet, the business transformation logic which we first encounter is the Generic rule which normally describes the overall data population logic in both Initial and Incremental Loads. This plays a vital role in understanding the overall logic from the top of the subject area development. As tester it is mandatory for us to validate the data extraction logic very carefully. The overall logic includes description of relationship between different logical/physical entities and with some complicated conditions(as it seems sometimes) in between them.

Data transformation logic for different attributes in target tables can theb be started to be verified once the generic business rule validation is accomplished successfully.

Since some of the database tables contain several millions (may be billions as well in some cases) of data, sometimes it may become helpful for the tester to extract a small dataset from the database by following the generic rule as per the mapping sheet. It will help in validating the attributes in a quick fashion. End to End test ting in ETL can be done only after the validation of the attributes are performed.

Some more care should be taken by us if we are required to do the cube testing afterwards.The following points can be the steps to validate the cube results.

Verification whether the data from the data warehouse/data mart is mapped & designed correctly in the Cube/reports.
Verification of all the measures and measure groups (including derived measures, aggregations).
Verification of all the dimensions (including Slowly Changing Dimension), attribute hierarchy.

For any discrepancy found during the testing of the cubes/reports, the same scenario should be created and validated with respect to the database tables.

Finally comes the Reports Testing. Drill Down/Drill Through report validations are done normally.

This can be done considering the following ones:

Verification of the layout format of the reports per the mock-up document,
Verification of different options/buttons, prompts and filters present in the report,
Verification of drilling, sorting,
Verification of the Export functionality.