Data Warehouse/ETL Automation testing

Community Discussion Automation Test
#1

Hi All,
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:

  1. Record count comparison between source and destination table
  2. Bit of data reconciliation check between source and destination table
#2

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.