Data Warehouse/ETL Automation testing

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
1 Like

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.
1 Like

Hey Mate,

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

Here is an article on Building An End-to-end Data Warehouse Testing Strategy maybe it can be of help!

I hope that will ease the entire task.