I'm in a similar boat with my current project, but am probably fairing better than you (as described). I do work in the ERP space... so not unlike you describe

I'm in a similar boat with my current project, but am probably fairing better than you (as described). I do work in the ERP space... so not unlike you describe, 100s of highly normalized tables (the biggest of these systems I worked with had just over 1000 tables). I'm currently working on a greenfield system in this class and the database development workflow is specifically designed to help make things sanely testable. So, first the application is designed to be very granularly modular and each module handles its own database tables... and each module is fully testable by itself. Yes, there can be/are dependencies between modules, but only the direct dependencies only ever have to be dealt with... when unit testing in the module itself, it's usually a pretty small slice of the database that needs to be handled. So what that means is any one component's test suite only needs to set the initial state for itself and its dependencies. What I do then is individual functional test can take that starting DB state, do it's work in a transaction, and then roll the transaction back at the end of the test. This way the only initial state created at the start of the process necessarily has to be dealt with. My "integration tests" will walk the business process statefuly, using only the initial DB state for dependents setup and walking the business process of creating all the data the module itself handles. Finally, unit test starting data is only ever defined in the component defining the tables which are to be loaded. This means that when dependents have their own tables, reference's are made to load that component's test data rather than trying to redefine a fresh set every time it shows up in the dependency tree. Anyway, as said earlier, this kind of code development testing discipline isn't common amongst DBA types (the best to be able to figure out a good methodology for it) while a lot of application developers that have the testing discipling avoid the DB like the plague. So it never gets done until it absolutely has to be done. And they you end up ad hoc'ing it together.