In SQL-heavy ETL pipelines, I normally don't test the SQL queries by themselves, but do black box & performance testing to verify that the output of a certain batch job matches what I expect (automated acceptance testing).

This is easier if you have the same input every time the tests run, like a frozen database image, because then you can basically have snapshot tests.