I am developing an ETL process that retrieves business data from a single database into a data warehouse. The application does NOT use NHibinate, Linq to Sql or Entity Framework. The application has its own data access classes that generate the necessary SQL statements to execute the CUID.
As you can see, developers who write code that creates their own SQL can easily be mistaken.
I would like to write a program that generates test data (Arrange), than to execute the ETL process (Act) and check the data store (Assert).
I do not think this program is difficult to write. However, I am worried that in the past my company tried to do something similar, and as a result, a small part of uncontrolled unit tests appeared, which constantly fail due to many new changes in the database schema as new functions are added.
My plan is to write an integration test that runs on the build machine, and not any unit tests to ensure the ETL works. Test data cannot be completely random, because business logic determines how data is loaded into the data warehouse. We have a special development tool that generates new data access classes when changing the database definition.
I would like to receive feedback from the community on providing me with recommendations on writing such an integration test that is easy to maintain. Some ideas I have:
Save the backup test database in a version control (TFS) control, developers will need to modify the backup database when changing data in the source or data store.
Developers must support test data, although the test program (C # in this case) is manual. This program will have a basic framework for the developer to generate their test data.
When the test database is initialized, it generates random data. Developers will need to write code to override certain randomly generated data to ensure that the test passes.