Sure thing. Thinking back, the "data issues" I've encountered roughly fall into two buckets - something changed in the source data, and something broke in the code.
My first reservation with tests is that the vast majority of issues fall into the first bucket. Audits are a solution to the source data change problems (though trying to anticipate the myriad ways in which source data can change feels almost impossible, such that audits end up becoming more of a "lets not fuck it up THAT way again"), but Tests are not. Yet writing and debugging tests takes as much or more effort.
My other reservation is that generally, I see a lot of VERY basic tests that only check for a limited, very simple subset of possible errors. So even for data issues that fall into the latter bucket, the tests aren't catching any issues. Things like adding something to a where clause that references a column from a LEFT JOINed table or using "= TRUE" instead of "IS TRUE" generally isn't something that test cases or asserts will catch (in my experience).
I understand that ensuring code quality requires redundant layers of checks, so tests should act as an additional safety net for developer skill and code reviews. But I also think that teams have limited bandwidth, and I would prefer efforts be focused on higher ROI activities than writing tests.*
*Which is not to say I write SQL without testing it. I prefer to think about each change I am make as requiring a unique set of checks, based on what could go wrong with the specific changes I'm making, and manually test outputs accordingly. ie run with old and new versions of the code, and comparing to see if row counts change (or don't change) as expected.