Yea I do that via dbt by setting up a mock data tables in database and using a macro to use those as sources/refs when run in test mode.

However what we are doing here isn't 'unit testing' its a black box integration testing. When I write equivalent code in scala, i just test the logic via unit tests,

eg: logic to filter out some orders that don't qualify, I extract method in scala code and just test that logic as part of development lifecycle. There is no dependency on a database.

Analog here is using selenium or some ui testing framework to test if button turns blue if order exceeds limit. Thats not unit testing.

> I extract method in scala code and just test that logic as part of development lifecycle. There is no dependency on a database.

Unit testing seems to depend on where the unit of code is which is being tested. At the middle tier, you may mock out parts of the code so the tests aren't reliant on external sources (apis, databases, libraries, etc.).

It seems that unit testing database code would happen at the database layer: it's still a unit test as the test isn't dependent on external sources.

> It seems that unit testing database code would happen at the database layer:

Its not database code though. "Orders over 100$ should be marked as vip" is domain logic.

Database code like interactions with database, connection pools, primary/secondary switching ect yes they should be tested with a database.

I was going to say the same. With the prevalence of embedded databases, and how cheap it is to stand up a container with non-embedded options, build time testing of queries has never been easier.