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 ...
Updating to reflect exit criteria for testing. You can look at MSDN article - Adventures with Testing BI/DW Application: On a crusade to find the Holy Grail
Critical areas to focus are
Validating the ETL Scenarios for full, delta pull
Testing with Production Data for multiple regions, multiple data sets
Validating DataMarts, Reports results for the ...
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 ...
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 ...
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 ...
[Disclaimer: This product is created by my company]
We have a product call QuerySurge that allows you to fully compare data in the source tables with the data loaded into the data warehouse. Compared to other approaches, this seems to be the best automated method for validation.
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.
I found this to be a useful, though high level article about data validation.
It's more about how to approach the testing than specific tests, but it does contain some specific tests and has a lot of useful information:
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 ...