I have a numer of tests that I run across a DB after an ETL load to validate the dataset
Each test is a negative test (ie: should return zero rows)
Select 'COLX' , table1.colx , table1.coly , table1.colz , start_date, end_date FROM Table1 where end_date <start_date ;
Select 'COLY' , table2.colx , start_date, end_date FROM Table2 where end_date <start_date ;
Select 'COLZ' , table3.colx , table3.colz , start_date, end_date FROM Table3where end_date <start_date ;
you can set up a proc that uses dymanic sql, store your select statements in a queue table, call them with a select and consume and use the system variable activity_count for each statement to check for rows returned, if actvity_count > 0 the test fails...