Running sqlite in memory as a test db speeds up your test runner as crazy. You can do this if you use an sql query builder library, because it can translate your queries to the specific database.
Of course most people who have complex queries are probably not using sqlite and so may not care about testing the database.
That is by installed instances. Whether that translates to the amount of developers is not so clear.
SET SESSION synchronous_commit TO OFF;
(Update: I just looked in our test code, you can also replace the CREATE TABLE commands with "CREATE UNLOGGED TABLE" to disable write-ahead logging.)
There are possibly other tricks?
I slightly disagree with the tech debt comment, though. If you get a huge speed up, it may be worth paying for the occasional bug, depending on the circumstances. Or you could do both, and only run the test on Postgres occasionally.
Or along similar lines you could divide it such that developers can test things locally on their machines with SQLite, but once it gets pushed into CI (and passes code review etc.) it's tested against the heavy db.
I meant in situations like parent comment where you're using an ORM such as hibernate that supports multiple databases, you can test as much of the non-DB specific stuff with SQLite in-memory and then do a separate batch of tests with DB specific behaviour.
Snowflake, on the other hand, is just special.