Ask HN: How do you test SQL?

1

Flyway

#MySQL Tools #Development #Online Services 57 social mentions

2

data-diff

I did data engineering for 6 years and am building a company to automate SQL validation for dbt users. First, by “testing SQL pipelines”, I assume you mean testing changes to SQL code as part of the development workflow? (vs. Monitoring pipelines in production for failures / anomalies). If so: 1 – assertions. Dbt comes with a solid built-in testing framework [1] for expressing assertions such as “this column should have values in the list [A,B,C]” as well checking referential integrity, uniqueness, nulls, etc. There are more advanced packages on top of dbt tests [2]. The problem with assertion testing in general though is that for a moderately complex data pipeline, it’s infeasible to achieve test coverage that would cover most possible failure scenarios. 2 – data diff: for every change to SQL, know exactly how the code change affects the output data by comparing the data in dev/staging (built off the dev branch code) with the data in production (built off the main branch). We built an open-source tool for that: https://github.com/datafold/data-diff.

#Data Dashboard #Databases #Analytics 7 social mentions

3

Sqitch

Yup, same. Last time I set this up I used Sqitch¹ for migrations, which encourages you to write tests for each migration; caught a lot of bugs early that way, all in a local-first dev environment. Worked especially well for Postgres since plpgsql makes it easy to write tests more imperatively. ¹: https://sqitch.org/.

#Development #Online Services #MySQL Tools 17 social mentions