So at work we would run tons of tests against the real service with a real database, seeding thousands of schemas to allow for parallel testing of tests that change state.

This takes 3 minutes, 1 if you use tmpfs. It only takes <10 seconds if you dont run writing tests.

These actually cover most real world use cases for a query-engine we maintain.

Unit tests have their place for pieces of code that run based on a well defined spec, but all in all this integration or component-level testing is really what brings me the most value always.