I've been looking for resources for our data team to apply best practices for testing SQL pipelines (we use DBT) but have not found anything. How do you test SQL pipelines? What patterns, tools and best practices would you recommend? Any good reference material you know of?
Ask HN: How do you test SQL?
1–10 of 322 posts
Re: Ask HN: How do you test SQL?
#2Excellent question. Not sure why it's getting no traction.
For my own use-cases, I usually test this at the application level and not the DB level. This is admittedly not unit-testing my SQL (or stored procs or triggers) but integration-testing it.
Re: Ask HN: How do you test SQL?
#3In 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.
Re: Ask HN: How do you test SQL?
#4If you're using dbt, dbt tests are a good start: https://docs.getdbt.com/docs/build/tests
You can hook up dbt tests to your CI and Git(hub|lab) for data PRs.
Depending on your needs, you can also look into data observability tools such as Datafold (paid) or re_data (free)
Re: Ask HN: How do you test SQL?
#5Gitlabs has their guide up, I love it and use it all the time. I've been doing data engineering in a small team for about 4 years, helping hospitals with data and hopefully making it easier to understand. Something that is overlooked or undervalued in my opinion, have stark distinctions for separating out technical and business logic tests. It makes it easier communicating what's happening in the event something is 'wrong' vs wrong, and it's easier to bring a non-technically inclined team member up to speed. Also, I think it's good to learn from the SaaS side of things and not bloat up or overengineer with infrastructure as data engineering is the latest development flavour. Keep it simple. Great expectations is a great tool however I think small teams should take really hard looks at their needs and see if a simple orchestration engine and SQL testing is enough. A centralized source for testing is great, however infrastructure isn't free even when it is you are paying for it with you and your teams time.
Re: Ask HN: How do you test SQL?
#6Excellent question. Not sure why it's getting no traction. For my own use-cases, I usually test this at the application level and not the DB level. This is admittedly not unit-testing my SQL (or stored procs or triggers) but integration-testing it.
With ORMs you can get pretty close to this being unit testing for the DB though.
Re: Ask HN: How do you test SQL?
#7Abstraction layer between the query you write and the one that gets executed. This way you can mock the schema, run the query on the mock to assert some condition x.
A ref() concept like dbt's is sufficient. When testing, have ref output a different (test-x) name for all your references.
Re: Ask HN: How do you test SQL?
#8There's pgTAP for Postgres [1], the same approach probably is valid for other databases.
Here's [2] a slide deck by David Wheeler giving an introduction into how it works.
[2] https://www.slideshare.net/justatheory/unit-test-your-databa...
Re: Ask HN: How do you test SQL?
#9Gitlabs has their guide up, I love it and use it all the time. I've been doing data engineering in a small team for about 4 years, helping hospitals with data and hopefully making it easier to understand. Something that is overlooked or undervalued in my opinion, have stark distinctions for separating out technical and business logic tests. It makes it easier communicating what's happening in the event something is '…
Could you link to the specific guide you're referring to? I see a couple on quick search -- perhaps this one? https://docs.gitlab.com/ee/development/database_review.html
Re: Ask HN: How do you test SQL?
#10Excellent question. Not sure why it's getting no traction. For my own use-cases, I usually test this at the application level and not the DB level. This is admittedly not unit-testing my SQL (or stored procs or triggers) but integration-testing it.
With ORMs you can get pretty close to this being unit testing for the DB though.
With not complex data for sure, I think SQL testing is so much easier once you start having hierarchies and many to many relationships start piling up.