I am managing testing for a consulting firm, we have a dedicated testing team that performs ETL testing using Microsoft Azure technologies.
We are planning to automate regression testing and wondering if anyone in the club has done something similar before or can give some thoughts/direction??
What we have done:
So far we have managed to automate the regression testing by creating store proc but again it’s time-consuming and bit manually and works on specific cases.
What I am looking for:
I am looking for a solution by which if we provide table schema details, the process should be able to write SQL queries and performs the below checks:
Record count comparison between source and destination table
Bit of data reconciliation check between source and destination table
When I did this kind of check in the past it was all via hand-written SQL. In the general case I can’t see how the tool would be able to know for a given step:
The input table or tables (e.g. if 2+ tables were concatenated to make the output)
The output table or tables (e.g. if data is being split into different output tables based on some business logic, including into some kind of reject table for bad inputs)
Which field or fields in the inputs should be summed, ditto in the outputs.
I am not sure if there is any specific tool that would help you simplify the process and ease the SQL query writing and executing the given requirements. But here are a few things that you might give a try to make the process easy.
Ensure Correct Formatting for the Query
Specify the SELECT fields instead of using SELECT *
Avoid Correlated Subqueries if not required
Limit the results obtained by the query
If not required, remove The DISTINCT Clause
Resist Any Functions in Predicates
If possible, you should avoid OR, AND, NOT operators