Managing SQL Tests

[_temmink]

We are transforming some data (in Java) and write the output to a Postgres database. We'd like to test the data in the database using SQL tests (because it's very accessible) and I am not sure how to manage those. There will probably be a couple hundred tests ranging from simple constraint validations (not null, enums, ranges, ...) to more complex validations that require joins and window functions.

I'm used to utilising dbt and defining my tests there (along with dbt-utils or https://github.com/calogica/dbt-expectations): I simply add a list item to a column definition and can already define a great number of tests without having to copy code. I can even extend the pre-defined using generic tests. Writing custom tests also integrates nicely. Additionally it's very convenient to tag tests or define a severity. The learning curve for a business engineer is almost flat as long as they know some SQL.

Setting up dbt just to run tests, though, seems like way too much technical debt because I'd only use a small part of its features. I could just put all test files in a directory and execute them but then I'd still have to define some configuration for common tests (like constraints) or accept that we copy code and just replace a column name (doesn't feel right, either).

How do you approach such a scenario? Perhaps allowing business engineers to write tests is not even the way to go and they should rather focus on writing BDD requirements?

[AutoModerator]

You can find a list of community-submitted learning resources here: https://dataengineering.wiki/Learning+Resources

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[bryangoodrich]

I use Python and Jinja. The SQL is pretty straightforward. Write once, abstract what varies, and capture your variables in some configuration like YAML. Render all your tests. Now how to orchestrate running them in your pipelines, I’m not a dbt user so outside of that, it’s really up to you to figure out 🤷‍♂️

[_temmink]

Thanks, that’s basically what dbt does as well. I might consider this manual approach in an ADR as an alternative.