As the systems within Decision Support started increasing in complexity, testing strategies came ever more complex.
It became obvious that we needed to take a different approach, so our Director instructed me to look at what we could do for Automated Testing. After evaluating various tools and systems for automated testing, it became apparent that most of the off-the-shelf systems available were designed for coding, not for database testing. We needed a system that could handle databases and compare large data sets.
At the time, we had just started using Denodo, a data virtualization tool, and part of that software includes a Java based testing tool that could take a query in SQL or VDP (an SQL like language for use with Denodo) and compare the outputs. What the tool lacked though was a way to generate the test file that was needed, which was written in a very specific way.
When I looked at the file that was required, because of the structure, it struck me that I could store the information in a database table, and use the contents of the table to create a test file, which could then be run through the Denodo test tool. Teaching myself Python, I learned how to access a SQL database table, write the test out to a file, and then call the Denodo test tool t process the file. From here, I was then able to extend the concept out to create a suite of tests, and as a proof of concept, I monitored the data from our SQL Server 2012 and SQL Server 2016 production systems to ensure that the data did not change between the 2 systems during our SQL Server 2016 upgrade project.
Every day, it would analyse approximately 4,000 tables in about one hour. The Denodo test tool was severely limited in its reporting, but we were able to show that the data stayed the same, and where it differed, we were able to investigate and show that this was down to how the SQL Server 2016 engine gave different results to to SQL Server 2012 engine.
I hired a student to take this proof of concept, and he wrote a web based front end, utilizing my Python code to create a front-end that people could use. As more people started using the system, I subsequently hired a full-time web developer to continue this work, which she did admirably.
As we continued to use this Automated System, which people found convenient, we found that the limitations in the Denodo test tool were beginning to hold us back in what we felt we could achieve. Continuing my learning of Python, I started to investigate whether Python could be used to do the data comparison checks, and coming across the Pandas library (https://pandas.pydata.org/), I realized that this could be used be used to overcome many of the deficiencies of the Denodo test tool. Again, creating a proof of concept, I was able to show that I could take the existing tests, with no modifications needed, and run the same tests, often faster than the existing test tool could do the same test, including some tests that it could not handle. I was able to incorporate new types of tests that the existing tool could not do, including range checking, masking sensitive data, and logging out all issues to a database table rather that just the first failure.
This second version of Automated Testing, using Python only, will simplify the backend infrastructure, and give us an overall speed increase of around 18% over our existing solution, with no changes to existing tests needed in his first phase. I completed the proof of concept in early 2020, and this was then taken into production in August of that year. Since then, as the overall architect of automated testing within Vancouver Coastal, I am involved with the direction that we take, offering guidance to the team in charge of Automated Testing. I am currently working on some POCs that will improve the testing framework even further by increasing the depth of the testing that is done to improve output so we know not just what failed, but potentially why it failed as well, increasing the speed that a fix can be implemented.