Research and Policy Analyst · EI Data Conversion
Proven against SAS
The new process had to give the same results as the SAS programs it was built to replace. Every step was tested against the SAS output, and the final outputs were compared value for value: every value was identical.
The ladder of tests · 1
Every step, each raw input
Every step, on each raw input, was compared with the SAS output before the next began.
The ladder of tests · 2
The final outputs
Compared as written: nothing rounded or tidied first, and blanks compared as blanks.
The ladder of tests · 3
Every value identical
Every value in the final outputs matched the SAS output.
One difference · 1
Two rows share a key
Two rows carry the same key. Only one of them can stay.
One difference · 2
SAS keeps the first row
SAS reads the rows in order and keeps the first one it meets.
One difference · 3
No “first” in parallel
The new engine splits the work across many cores, so either row may be kept. The test reports the difference.
One difference · 4
The fix: number the rows
Each row is numbered as it is read, and the lowest number is kept. The test passes. I decided the rule; the agent wrote it; the test proved it.
Tested before moving on
Each step was compared with the SAS output, on each raw input, before the next one began. An error was found close to where it was made.
No tolerance at the end
The final outputs were compared as written: nothing rounded or tidied first, and blanks compared as blanks.
Match first, speed later
The code was made to behave as SAS does, then made faster. The tests were run again after every change.
In more detail. SAS applies rules that are easy to miss: how missing values sort and compare, the order rows are read in, how a value is carried from one row to the next, and which row is kept when two share a key. A database engine that works in parallel does none of this by default. Each rule was reproduced on purpose, then proven by the tests.
An explanatory reconstruction of how the results were checked. The example is drawn without data.