Ed Elliott shares a few techniques for testing ETL processes:
Reconciliation is the process of going to your source system, getting a number and validating that number on the target. This ranges from being easy to impossible, so you need to decide what to reconcile on a case by case basis.
In its simplest form, we can go to a source system and find out things like how many records are to be copied, sum up totals and run other aggregations that we can then validate as correct (or not!) on the target system.
Ed has put together a thoughtful approach to validating data loads regardless of the source.
Ed Elliott has started a new series on testing ETL pipelines:
We test in production, this means we have monitoring and do things like have phased roll-outs using feature flags, or we roll-out to select customers first, prove it then roll it out to everyone else. Testing in production doesn’t mean hacking around getting some process to work. We don’t test “on production” (hacking), we test “in production” – while we are in production we are continually testing, and if anything goes wrong, we have alerts and can deal with it.
Testing pipelines feels difficult because there are so many moving pieces, but if you design for testability (e.g., being able to tee off samples of data, send test records through, etc.), things get easier.
Pamela Mooney shows us how we can find broken code on our SQL Server instances:
Before we approached our last major SQL Server upgrade, I was curious about what might break. Yes, I had used the DEA to check our code against deprecated or discontinued code. But I am talking about code that might not have been used in some time and would break because objects no longer existed, or other things like that. So I wrote these scripts to refresh the sprocs, views and functions in our (non-production) environment.
Pamela’s motivation was to handle code which breaks during an update. You can also use this to see what you can probably deprecate—if the view doesn’t work, it can’t be in use. That means either someone should fix it or drop it.
Shane O’Neill has a public service announcement:
Now scripts are notoriously hard to test, I’ve written about how I’ve done that before but, honestly, if you really want to know then you need to check out Jakub Jares ( blog | twitter ).
Knowing how difficult testing scripts are, the first thing I decided to do was take the functions in the script and split them out. This way they can be abstracted away and tested safely.
I also didn’t want to take on too much at one time so I choose a random function,
GetPreviousTag, and only actioned that one first.
Shane also found a bug in the first script, underscoring the importance of good tests.
Regardless of the method that you decided to use, at the end of the replays, you will have two distinct sets of tables containing the workload analysis data, sitting in different schemas in the same database or in completely different databases.
WorkloadViewer will let you visualize performance over time, as we have seen for a single workload analysis, but this time it will be able to show you data from both workloads, so that you can compare them.
This sort of production load testing is both important and difficult; WorkloadTools makes it easier.
Ed Elliott shares some tips on load testing a database:
Testing database performance is hard and takes a great deal of work to probably not even do particularly well. Instead of thinking about how you can load test a database think about how you can drive the application.
For instance, if you have a web app then use JMeter to simulate load. If you have a “fat app”, then you might need to write some code to call specific workflows through the application.
Click through for good advice, particularly around what you should not do.
Andy Chambers takes us through how to test an event-driven system:
Each distinct service has a nice, pure data model with extensive unit tests, but now with new clients (and consequently new requirements) coming thick and fast, the number of these services is rapidly increasing. The testing guardian angel who sometimes visits your thoughts during your morning commute has noticed an increase in the release of bugs that could have been prevented with better integration tests.
Finally after a few incidents in production, and with velocity slowing down due to the deployment pipeline frequently being clogged up by flaky integration tests, you start to think about what you want from your test suite. You set off looking for ideas to make really solid end-to-end tests. You wonder if it’s possible to make them fast. You think about all the things you could do with the time freed up by not having to apply manual data fixes that correct for deploying bad code.
At the end of it all, hopefully you’ll arrive here and learn about the Test Machine.
Check it out. Testing these types of system is certainly possible, but can be a bit difficult because of the additional layers of complexity.
Shane O’Neill walks us through an oopsie moment:
In modifying the template, I forgot to take out the original
Itblock and just put myItblock inside it.This lead to my block “pass by deafult” failed
(as it should cause of that typo) but the original, parent block it was in “does something useful” passed!
Click through for a demo and explanation.
Rob Sewell wants to generate a workload against AdventureWorks using Powershell:
For a later blog post I have been trying to generate some workload against an AdventureWorks database.
I found this excellent blog post by Pieter Vanhove thttps://blogs.technet.microsoft.com/msftpietervanhove/2016/01/08/generate-workload-on-your-azure-sql-database/ which references this 2011 post by Jonathan Kehayias t
https://www.sqlskills.com/blogs/jonathan/the-adventureworks2008r2-books-online-random-workload-generator/
Rob turns these into multi-threaded workload generators. If you’re looking at generating stress on servers, you might also look at PigDog, developed by Mark Willkinson (one of my co-workers, so I have seen the look of joy on his face when he brings SQL Server to its knees).
John Mount points out that you don’t need special infrastructure to perform unit testing in R:
There seems to be a general (false) impression among non R-core developers that to run tests,
Rpackage developers need a test management system such asRUnitortestthat. And a further false impression thattestthatis the onlyRtest management system. This is in fact not true, asRitself has a capable testing facility in “R CMD check” (a command triggeringRchecks from outside of any given integrated development environment).By a combination of skimming the
R-manuals ( https://cran.r-project.org/manuals.html ) and running a few experiments I came up with a description of howR-testing actually works. And I have adapted the available tools to fit my current preferred workflow. This may not be your preferred workflow, but I have and give my reasons below.
Food for thought for any R developer.
Kevin Feasel
2019-08-13
ETL, Testing
No Comment