You could take a look at the open-source dbt framework. They have a good implementation of unit testing for sql

https://docs.getdbt.com/docs/build/unit-tests