So, if these scenarios have to be considered for test cases, here is the list.
The Effective Data gets created or updated whenever there is an Insert/Update operation from the source record.
New record creation and the effective Start data being created.
A record with Update operation to update the effective Start data (with the system time or session run time) and update of the effective End date.
This can be done along with the above two as well.
Record getting Creates/Updated based on the Business Key or the Composite Key.
There will be more test cases, based on the business/functional/technical aspects of the composite key and the logic around it.
Is there a new unique number getting generated by the ETL process or the DB process whenever there is an insert or an update?
What is the max value accepted by the Surrogate Key? What happens after the max value is reached? Is the system still generating the unique values?
This can be split into multiple test cases based on the answers and the business scenarios around it.
You can suggest the development team to use the database’s auto generated increment key option here (if they are not doing it already).
Verify the overall run time of the SCD Type 2 process and match that to the acceptable run times by the business.
This case is usually ignored by development and business teams but this is an important test which should be done to get the expected time line of the overall process run and completion and also the time taken for pulling the historic data for reporting purposes.
Missing Data loads and record level operations
This test case will be applicable when you have a batch or a scheduled process which will keep on checking for the data changed (CDC) and try to get the updated from the source system to get the data refreshed and available for business as soon as possible.
If there is a run which is happening now, then are the records which were changed during this run updated in the next run?
How are the records being flagged for the processing?
This will also lead to further questions on data re-processing, exception management and operation data updates.
There are other generic test cases which will be applicable for SCD Type 2. These can be done before the above test cases or after them based on the need.
Verifying if all of the Type 2 SCD fields really should be Type 2 SCD?
This can be done with cross verifying the requirement document and the data model.