> Personally, I've never seen SQL unit tests/asserts demonstrate value in practice.
Do you mind expanding why? As someone currently looking for ways to integrate standard software engineering practices into (analytical) SQL code, I am curious about your reasoning.
> The vast majority of tests are simple uniqueness or non-null tests that would only catch the sloppiest mistakes
But don't they still happen? Perhaps they're not useful now, but may reveal clear problems when the original implementation is stretched in ways that weren't anticipated -- which is a common argument in favor of testing.
To add to this, I've been keeping an eye on sqlmesh and I like the distinction they make between testing [1] (asserting logic) and auditing [2] (validating expectations on the data).
Their builtin audits go beyond "not-null" and "unique", including statistical checks, for example.
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.
I don't disagree with anything you said, but I think you are underindexing on the importance of being a resilient data organization. Few things reduce customer trust like having the same error happen multiple times, especially when consumers are the ones identifying the problem.
Additionally, changes in the source data are quite common and very hard to detect. I agree that changes in code are another source of the issue, but that is usually under control of the data team, which is a totally different type of error compared to things outside your locus.
Automated statistical tests don't work because variance is common even in stable data streams; these add too much noise. It's critical for an organization to be able to patch a hole after it's been found, and this is where I think SQL tests shine. They need to be really easy to implement and monitor, but they serve a critical purpose for ensuring continued operational quality and downstream trust.
Hm I think you and I might actually be saying the same thing, just using different terms? Tests as I understand them (based on reading the article shared by the person I was responding to[1]) run on mock data, rather than real source data. Those are the ones that I feel have limited ROI.
I definitely agree there is a lot of value in tests that check against actual source data (which that person's link refers to as audits[1]).
Ah yes, I think we are in agreement :). Understanding terms of art becomes harder every year - the differentiation between tests and audits makes sense but adds so much friction as a noob that I wish they had just stuck to prior art.
> clear problems when the original implementation is stretched in ways that weren't anticipated -- which is a common argument in favor of testing.
On this point in particular, I'm not sure of this is happens very often in data engineering (especially with data transforms), since I don't think tables experience the type of scope creep the way app components or APIs might. Once a table is shipped, almost all every subsequent change is either adding/removing columns (which I think should be written in a way that means it's impossible to change the grain), fixing bugs (in which case tests are not relevant), or internally refactoring for performance (tests can help, but usually only cover very basic issues).
The latter case is actually one where I think automated, generic testing would be helpful, but I'm not aware of any existing tools make easy? Ideally, I would want a test suite to run new and old versions of the code in parallel, and confirm that outputs are unchanged.
Do you mind expanding why? As someone currently looking for ways to integrate standard software engineering practices into (analytical) SQL code, I am curious about your reasoning.
> The vast majority of tests are simple uniqueness or non-null tests that would only catch the sloppiest mistakes
But don't they still happen? Perhaps they're not useful now, but may reveal clear problems when the original implementation is stretched in ways that weren't anticipated -- which is a common argument in favor of testing.
To add to this, I've been keeping an eye on sqlmesh and I like the distinction they make between testing [1] (asserting logic) and auditing [2] (validating expectations on the data). Their builtin audits go beyond "not-null" and "unique", including statistical checks, for example.
[1] https://sqlmesh.readthedocs.io/en/stable/concepts/tests/
[2] https://sqlmesh.readthedocs.io/en/stable/concepts/audits/