I would split your question into two parts:
What needs to be tested?
You have already covered some critical scenarios. Besides these following should also be covered-:
-Ensure that all expected data is loaded into target table.
-Compare record counts between source and target.
-Check for any rejected records
-Null, non-unique or out of range ...
How complicated is the ETL logic? Are you bringing over every record? Do you transform the data? I would create targeted test data to test the logic itself.
Once you know what you know exactly which kind of data is brought over and exactly how the data is transformed you can absolutely automate it. Start with an empty database, insert to source for your ...
There are lots and lots of tests that can be carried out in ETL and data warehouse testing.
It helps if you have a set of prioritised requirements as to what you want to try and test.
The usual 3 'functional' checks are:
Have you checked the data coming in is ok? (garbage in garbage out)
Often times using the information from data profiling and looking ...
As a general principle, testing is about risk/reward, which comes down to cost in terms of money/time/resources (people/machines) in various combinations.
The test coverage curve will be some sort of log curve , where the most benefit in testing occurs in the initial set of tests.
The first 500 tests give 62% coverage, the next 500 tests only give an extra ...
This will depend on a number of factors, including: time/budget, data quality policy of your organization, data quality of the source dataset, complexity of each layer, etc.
A general guideline is, as with any testing, to ask yourself "what could go wrong here?" at each step.
For example, you could do data integrity at 1 and 2. But if you had a low budget ...
The best way to ensure the completeness and correctness of data is by testing it in SQL.
Can you try creating a database on your machine and use import\export wizard in SQL server and import the source and target file in two tables? I think then you will be able to validate the data transformation and ETL logic
I am also curious to know if there is any ...
Starts the project with run the jobs manually.
Check all the source data is loaded into target or not.
Write SQL queries to compare source and target data.
Review the column mapping document.
Check surrogate key is generating unique value or not.
Check surrogate key value is not null.
Check primary key value is not null.
Check the count of source and target ...
I'd say it depends on what sort of testing you're doing, and how much testing of the sprocs will have been done by the time they get to you. If you think of the system in terms of a program, testing the sprocs is similar to unit testing; they're the simplest thing that can be called. So you would generally mock up calling the sprocs in one case, and in the ...
Without knowing how complicated your stored procedures and Web APIs are, it is hard to know whether my environment is similar to yours.
Your tests serve two purposes: to find bugs, and to narrow down where the bug might be. If someone changes the Web APIs to use the stored procedures in a new way, and the change is buggy, it may be unclear whether the ...
I believe this article gives a good overview of the different data warehouse testing that should be applied:
We are using this method in almost every data warehouse project.