Incremental(Delta) Load Test

Hi all

It is my first post in this forum, we have recently started to implement a dimensional data warehouse for our organisation and I am the main person in charge of implementing the ETL jobs. We use SAP BO DataServices as the ETL tool. My question is what is the best and efficient way of testing Delta load (whether Dimension or Fact tables)?

As each table has got its corresponding SQL script (as mapping rules), it is easy to test the Initial load. We just check the target table in DW against its SQL script in a test case (written and run in MS SQL Management Studio). However testing the Delta load is time consuming and not straight forward as Initial load testing.

For each job usually there are different tables which contributes in loading data into DW table. In the Delta load job, I have a condition for each source table to see if the record modified Date is greater than the last ETL load, the record should be picked up and contributed in the load, otherwise not.

To fully test the job, I should insert and update a record into each source table, run the job and see whether the record has been passed through (affected the target record) or not.

If we have only 4 tables as the source of a job, we need to have at least 8 test cases to test the Delta load. One test case for Insert a new record in each table and one test case for update an existing record in source table. 4 * 2 = 8 test cases.

That's why I found out testing the Delta load tedious and frustrating. I'm wondering if there is any better way to fully test the ETL job for Incremental load, rather than the way I explained above.

Thanks for your help in advance.
Tootia