I'm trying to set up a test framework for (SQL) datasets that get updated daily. I want to write assertions that involve comparisons between two tables–for example, the sum of column
visitors in the
customer table, grouped by day, should be within 5% of the total in the
transactions table (but might not line up precisely due to noise in how each table is populated.)
These tests are hard to specify via constraints, but easy to run queries against. For example, one option would be to write queries that insert failing cases into a logging table, and have unit tests that run the queries then check whether the logging table is empty.
How do you approach tests like this? Do you use specialized tools, or standard unit testing frameworks?