5

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?

Improve this question

3 Answers 3

Reset to default
11

Been there, done that.

First let's make something clear:

  • That's not unit testing. Unit testing is about code. You are not runnning the tests after a code change to test if code alterations introduced a bug or unwanted behavior. Instead you want to run some routines at the end of a business day to see if some business performance indicator has met some predefined goal or is between some predefined threshold. If the data doesn't comply with the 5% rule, that's not because a bug in the software.
  • What you are really doing is monitoring your business performance based on the data records.
  • That could also be made to detect suspected deviations.

What I have made is this:

  • I've programmed some routines (the language is not important) and scheduled them in a crontab to run every date at a certain time.
  • That routines populate a table.
  • That table is used by a web app to render a report.

I have not used any specialized software so I cannot recommend you one.

You can integrate those reports into any portal or any Balanced Scorecard software that allows ad-hoc conectors to customize dashboards.

Improve this answer
1
  • Your database engine may also support scheduled tasks. If it's easy to write them in SQL, then do that. Easiest is almost always the right answer. But beware of false laziness.
    – kevin cline
    Apr 12, 2016 at 7:58
1

You seem to be labouring under a misapprehension about unit testing.

Good unit tests adhere to FIRST principles:

enter image description here

Whilst this may well be a valid test of your business rules, it clearly isn't a repeatable test since the source data can change over time. What you can do is write unit tests to create various data scenarios and check that your business rules fire correctly when the data is in a specific state.

N.B. Repeatable also implies that each test is responsible for creating, interrogating and then clearing down the data so that the database is left in a consistent state. A self-contained transaction is one way you can achieve this.

Improve this answer
0

If it's easy to do it with queries, do it with queries. It sounds like the whole thing should be a stored procedure. As others have said, I don't think a unit testing framework will be much help to you here.

Improve this answer
3
  • It isn't that unit tests aren't that much help, it is that what the OP is describing isn't unit testing in its pure form.
    – Robbie Dee
    Apr 13, 2016 at 10:23
  • Undeniably true, but that doesn't help the guy fix his problem. Hold on a minute, and he'll be back with another problem for which unit testing might be the answer :-)
    – bbsimonbb
    Apr 13, 2016 at 12:31
  • ... but that doesn't help the guy fix his problem - neither does wrapping the script in a stored procedure. The OP is looking to set up a test framework. Yes, stored procedures might be a cog in the machine but they're a means to an end - not the end in itself.
    – Robbie Dee
    Apr 13, 2016 at 13:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.