AgileData #23 -Agile Data Testing Techniques, a magical list of testing TLA's

Welcome to the Agile Data Podcast, where Shane and Nigel discuss the techniques they use to bring an agile way of working to the data world. In a simply magical way.

Welcome to the Agile Data Podcast. I'm Shane Gibson.

And I'm Nigel Vining.

And in today's session, we thought we might kick off a bit of a series of podcasts around testing. So Testing for us in the data domain is the bane of our existence. We used to joke in the old days that when you had a waterfall project, no matter how much time you plan for testing, it always ended up being one day, and that was the day before you went live. And then our users would go and test in production for us. So, there's been a lot of work done in testing in the data space, but in my view, it is still a very immature part of our domain, both in terms of tools that we can use, and repeatable techniques and patterns that we should adopt. So Nigel and I thought what we'd do today is we would do a whirlwind tour of all the testing terminology and techniques we've heard of, with a little bit of a micro description on each one. And then in future podcasts, we'll take each one of those and break them out into their own dedicated podcast where we talk them about them in more detail. So what do you reckon, Nigel, is that what we should do?

It sounds pretty good to me, and it's actually topical because this week I have been working on daily data validation checks for a customer, so it's sort of top of mind for me at the moment.

Excellent. So validation, not reconciliation, but we'll get on to that. So let's kick off with some of the agile words that we use. So one of the key things that people are doing when they do agile delivery now is TDD, test-driven development. So for me, that is where we write the test first, then we write some code, and we iterate the code until the code passes the tests.

I agree, that's the— that is an approach that may be actually how I do do it by default. I know the outcome I want to achieve, I iterate until I get that outcome. So I guess that's probably the one I naturally follow.

Right, so the second version is what we call Acceptance Test Driven Development. And for me, that's where we focus less on the data and more on the features. And the reason for that is, you know, it is unusual for a business user to be able to tell us what the acceptance test repeated piece of data is. For them to say, you know, on the 23rd of March at 11 AM, there were 1,329,251 customers. So what they tend to give us is acceptance criteria, which we tend to want to get as part of our user stories. And so those are things like, you know, as a user, I want to be able to so that. And so for acceptance-driven development, for me, that's where we are comparing that we've delivered what we need to deliver to pass those acceptance tests. The last one is behavioral-driven development. And for me, that is understanding the way the data behaves. So we tend to use a rule-based paradigm, a technique based out of Gherkin and Cucumber, where we would write a test that's something like, given blah blah blah and blah blah blah and blah blah blah, then, yeah. So given a load of customer data comes in and the customer data is from the Salesforce system, then we would expect the customer ID to be in a format of blah, blah, blah, right? And so we're writing natural rules language type tests to make sure the data behaves that way. Or given we're loading some data and the data has regions, we expect to see 5 regions. And therefore, when that changes, when the behavior of that data changes, we get an alert, we do it. So for me, that's how I describe applying TDD, ATDD, and BDD in a data world. I think the other thing that we use in terms of terminology a lot is the difference between reconciliation and validation of data. So people often talk about reconciliation, but for us, reconciliation is where we can compare two things and they should equal each other. What's your view on that one, Nigel? Is that your definition of reconciliation?

Uh, yeah, reconciliation. I automatically go to the what we call the left-right pattern. That's where we have something on the left, which is generally a source, and we have something on the right, which is generally a target or a report or an output. And if the numbers on the left match the numbers on the right, we say that's reconciled.

And so that gives us challenges, right? Because does that mean we have to go and hit the Data Factory, that source system that's generating those numbers or that data for us to reconcile it?

That's a good question. Sometimes, or we, the alternative is, is we build in some form of extract into our process that at a point in time we have a record from that source system and it says at this point in time, which might be midnight, there were X number of customers or Y number of dollars or Z number of something. So we effectively pick a point in time and we draw a line in the sand and we say the source system said this was the state of play at that time. And then we can use that record to reconcile our right-hand side to make sure everything's flowed through correctly.

And we'll talk about it more in the podcast dedicated to reconciliation, but we have other lighter techniques, right, where we can say count rows on both sides of the left, right, and see if they equal. So we have some heavy reconciliation processes and some lighter reconciliation processes, and we may want to mix the use of those reconciliation techniques depending on the risk of mutation of data between the left and the right. Yeah.

Yeah, yep, generally we start at a very high level. We just look at a summary of what's on the left compared to a summary of what's on the right. And if that's generally green, we don't always go to a more granular level. We say, hey, the topmost numbers match, we're pretty happy with that. We don't go down to the next level.

And then when we compare that to validation, so within a data platform, we are constantly changing data. We are changing what it means, we're changing how it's stored, we are filtering and subsetting. So we might start off with a million rows and for a particular reason we'll end up with 100,000. And so that means we can't reconcile because the left never matches the right, but we still need to validate it. So we talk about techniques we can use to validate that when we're doing something bad to the data, we can validate that expected result is consistent and doesn't mutate over time.

Yeah, and that's the— that's what I've been concentrating on this week, actually. You're right, we can't directly compare left to right, but what we can do— and this case in point, it was basically tracking sales per day for an organization. Now the thing of sales per day is they change constantly, so actually the validation checks became to check that the averages weren't moving too fast. So today's sales were in line with what yesterday's were, what they were last week and last month. So we're basically over time, we're checking a trend that the dollars are moving where we expect, and they weren't suddenly halving between one day to the next or doubling between one day to the next, because we know that it should progress in a reasonably linear fashion. So we're using actually a moving average by day over a trailing period to validate data. So that's slightly different but a reasonable approximation for that.

Yeah, so you're effectively, you're applying some anomaly detection to validate the data's fit for purpose. Another example is when we talk about data quality metrics or data profiling of the data. We often want to validate that the data has no nulls in a table that we're receiving or a table that we're creating. So we write tests to validate it, right? We use data quality profiling to tell us we've got a problem, but we should really be writing tests to tell us when that behavior happens. So again, data quality and validation testing for me kind of goes hands in hands, right? Because it's identifying the data is not valid, it's not fit for purpose for what we expect it to look like. I think one of the other things that we need to talk about in one of the podcasts is this idea of testing techniques we can use when we lightly couple our data platform or lightly decouple it. So we talked a while ago in one of the podcasts about this idea of Pac-Man. Pac-Man achieves a task, some data comes into Pac-Man, Pac-Man does some stuff to it, the data exits Pac-Man. And what that means is we can use some techniques that are likened to sensors. So between each of the Pac-Man, we can write sensors that sniff the data as it goes past. And those sensors can have a set of rules or tests that are related to them. So imagine if we had a sensor that sniffed data and said, identified where that data had nulls in a record. So rather than writing that test every time we have a table to say, is there a null, or writing a test and applying it, we could have a sensor that says whenever data moves between moving parts and they're like tightly coupled or decoupled, sniff it, and where you see nulls, raise an alert, right? So it's a technique we can use to write the test once and apply it many as part of our data pipeline or our data supply chain, rather than having to remember to apply that test. So I think, you know, let's talk about that in one of the podcasts, about that emerging practice and how hard it is and whether it's a good idea or not.

Yeah, I agree. I actually, that one resonates with me. That's quite a nice one because you're right, we write it once, we effectively just mark it as something we want to do every time we run a process, and it just happens magically. You know, it's a nice pattern and it's a common sense one.

So one of the other challenges we have is once the data has been made fit for purpose within a data platform, we then have a bunch of reporting or visualization or analytical tools that consume that data and typically do something with it. And those tools are typically decoupled. They're either doing some form of DirectQuery where they're grabbing the data out of the data platform on demand, or they're grabbing the data on a repeatable basis and storing it within the tool itself. But regardless of those techniques, that data is persisted either in memory or on disk within that BI tool or that visualization tool, and those tools have a lot of capability to change that data after the fact as well. But if we just look at that, that first thing around consumption, you know, if I have a report that's pulling data out of the, out of the table on the data platform, how do I know that that tool is actually bringing the data back properly? How do I know it's not got something in that query to bring it back that's restricting the number of rows? How do I know, you know, that I have 1,000,001 customers in my final consumable table and that when it's going back into my Tableau dashboard, Tableau is actually seeing 1,000,001 customers? And that's a bit of a problem for testing, isn't it?

Yeah, what actually also came to mind, something I've seen recently, is If the underlying data layers or the consume layer that the tools are plugging into haven't been thought out for safety of use, and what immediately springs to mind is non-additive measures turning up in your consume layer, you end up with a situation where the tool applies an average on an average or an average of a field that can't be averaged, and it's all very easy, but straight away you've let the user do something wrong because you haven't protected them in some way. Um, I say that's what always worries me, presenting something in the consumer layer that the tool can get wrong without any real warning or consequence.

And with a lot of the tools that we use in the data space, they're, they're starting to adopt this idea of being able to embed or call tests within within those tools. But the BI tools, not so much still, right? I don't know of many tools, if any really, where I can write a test that when a dashboard is being rendered, I can validate that the numbers on the dashboard are correct, that they meet my— they're valid, they meet my expectation. So I think tooling-wise, that's a pretty light area. And maybe before we do a deep dive on that podcast, we might need to go and do some research on the on the market to see whether actually any of the new breed of these tools are dealing with that for us. I think another one that I hear a lot, and I don't write code, so I don't really know a lot about it, is this concept of linting. What do you understand linting as?

Sure, so we, under the covers of agile.data.io, we use linters and formatters effectively to keep our code clean, readable, and syntactically correct. So we use a formatter, in this case we use Black for Python, which whenever we commit code, we run the Black library over it. And all it does is it checks that all the indentation is correct, spacing's correct, and the code's laid out nicely to a a standard template. This has benefits for people coming along downstream reading the code. The code is presented in a pretty format, I guess. And it also identifies any formatting issues. The other one is we make light use of Flake, which is a linter. A linter is effectively a library that we run over our code and it looks for syntactical or construct errors. So the code may be formatted correctly, but it may have errors that are unforeseen. You might be adding two things that on the surface it looks like you're adding them, but you're actually concatenating them together. So it can help find syntax and construct errors that maybe aren't obvious and would pass tests but possibly shouldn't. So they, they help keep us safe and keep our code readable.

So it's not testing that the data is valid or reconciled, but it's It's testing that our code meets our standards and it has a chance of running and executing successfully. Exactly.

Because you haven't missed anything obvious. That is correct. Yep.

Cool. Wish I had that in words. That would be great. So let's talk, before we finish off, about some anti-patterns. So where we see testing behavior being less than desirable. So I remember a project that we both worked on. Before we started agiledata.io, where there was a testing team of 8 people that weren't part of the delivery team. They sat in their own part of the floor and they read the requirements that were written, which was a bit of an issue because we were following an agile way of working. So, our requirements were light, specific for the task that we're going to achieve in that iteration. Constantly evolving and changing as we learned new things. And then they would create these massive Excel spreadsheets of the tests, a list of tests they were creating. Each of those tests were written in code, so that was impressive. So they were code-based tests rather than clicky-clicky GUI point-and-click human testing. But that code base had to be run manually. Unfortunately for the testers, it tended to have to be run outside of standard working hours when the delivery team were doing all their hardcore work. And then every time something major changed, all those tests had to be completely rewritten and completely re-executed. So, those 8 testers were very, very busy. But I'm not sure that they were really that efficient or effective. So, for me, that was an anti-pattern on how we do testing, where we have a separate team, they're working off documents rather than conversations, they're out of sync, they're always late to the party, and every change is massive and kills them. So, for me, that's probably an anti-pattern that I see time and time again.

Yeah, the Excel one's interesting. Funnily enough, that's something else I've had experience with this very week. I find a lot of testers naturally gravitate to Excel because it's a known quantity and it's the first place they cut and paste data. And then they put a— if column A equals column B, put a tick, otherwise put a cross and format it red. And they tend to automatically adopt that because it's a nice easy way that they paste data and they get all the red rows and then they pass them back. But you're right, for all the reasons you expressed, it's a very manual, non-repeatable process and you have to keep doing it over and over. So I tend to take the approach that, um, if they come up with all their logic to get their test data, I would then generally drop that into a repeatable scheduled tool or process to actually run it for them automatically every time maybe code's released or every day to check the overnight numbers to try and take it out of the spreadsheet realm and put into something a little bit more enterprise level, I guess.

Yeah, and so automation helps, right? It helps reduce the cost of rerunning tests. Which means we feel more comfortable running them earlier, running them more regularly.

More often.

Yep. I remember, again, there was a pattern I saw in one project, and it wasn't an anti-pattern because it had some of the tenets of automated testing, some of the tenets of being able to adapt to change. But this one was one of the team members was the tester. He was a very experienced tester, and what he'd done is he'd built a testing toolkit effectively. We talked a while ago about the old days where you had your diskette in your top pocket with your sample code. So he had one of those. And so what he would do is he would take the design documents and he would write all the ETL code again. So the delivery team, the data engineers, would write the code, and he would write the same code but write it differently. And his tool, and then he would schedule their code to run through the scheduler overnight and his code to run through his scheduler overnight. And then he would automate the comparison, the reconciliation of the left-right of both those runs. So you could quickly, when you walked in the morning, get a view of where it matched, where it reconciled, and where it didn't. Challenge then was, who's right? Was it purely a coding error on his part or the data engineer? Or again, was it— because we were running an agile way of working— was it the fact that the engineer had discovered some issues with the data as they applied the rules? They had gone and had a conversation with the team and the stakeholder and the product owner to make a call, and therefore they changed the code and the documentation was out of sync. So again, you spend a lot of time going down the rabbit hole of for the ones that flagged red, why were they red? But, you know, one of the patterns or techniques out of that was automated, which was great, right? It did save time from that point of view.

I see. I do remember that pattern, and I do remember that guy, and it was a work of beauty. It was slightly misplaced, but it was a pattern. But the fact it was based on documentation that was always becoming stale because of evolving things, it was its only downfall. But yeah, no credit to that person, it was a pattern.

But also, there was twice as much effort, right? Because we weren't writing tests, we were writing code, and the tests were automated.

It was a very elaborate test approach. And you're right, most of the rabbit holes were due to requirements that have moved on between the time that test was created and run.

I think the last one is an interesting one that I thought was a good pattern actually. So what happened was as the team evolved their agile way of working, the BA-type skills within the team, the data analyst-type skills, started doing more and more of the data modeling. So as part of the early exploration of data and understanding the information product that needed to be delivered, they started doing some exploratory work on the data and some exploratory work on what a light, light work on what a data model might look like to support the outcome that was required. And as they got those skills, they started writing tests in Jupyter Notebooks. So they would take the data and quickly model in Jupyter Notebooks to figure out where it fitted. And then as part of that, they started to see where the data was mutating against wasn't valid for what they were doing. So they then started writing tests for that. And then when that, that light version got handed across to the engineer, because there was still a semi-pipeline process, the engineers would go through and write their code and harden it and do all the good stuff. And then the analyst-type people would then come back in and effectively repoint their Jupyter Notebooks at the new data source. I mean, there wasn't a one-for-one point, right, because the model had morphed slightly as they went through, as always, as the expectation of the model hits the real data. But that mutation of requirements didn't— or change of requirements didn't happen as often as it used to because the requirements have been developed in line with the data. So they would repoint the Jupyter Notebooks at them with a little bit of rework and then rerun them. And so again, it was a form of testing early, right, and then reapplying those tests. So I thought that was a good way of working for the maturity of the team. I thought it was creative and added value, right? Reduced their time, helped them adapt to change, and gave them a better quality data outcome.

Yeah, I think that's probably a nice example of, I guess, a mixture of test-driven development and requirements test-driven development, or sort of iteratively smashed together.

Yeah, so we would call that a combination of TDD and BDD, so test-driven development and behavioral-driven development, because that's the one, um, complying, you know, applying a couple of the concepts from both of those. Uh, wasn't really acceptance-driven because, you know, it wasn't based on the user stories or the acceptance from the product owner of what good looked like. But that's okay. So I think that was a whirlwind tour. So we talked about TDD versus ADTDD versus BDD, so we might deep dive on that in another podcast. We talked on the difference between reconciliation and validation, and how we use a technique of left-right for reconciliation and how we use Gherkin and rules-type frameworks for validation. We talked about some of the ways we could be really innovative and decouple through the concept of sensors to write ability to test for certain or validate certain things repeatedly. We talked about some of the things like linting that help us have better quality code. And we talked about the challenge of BI and visualization and analytical tools being decoupled or lightly coupled to the data and how hard that makes it for us with those types of tools to to reconcile and validate end-to-end from the Data Factory out to the consumer. So I think we'll pick one of those next and spend another half hour deep diving into one of those on the next podcast.

Sounds fantastic. Thank you.

Excellent. All right, well, catch everybody later.

And that, data magicians, was another Agile Data Podcast from Nigel and Shane. If you want to learn more about how you can apply agile ways of working to your data, head over to agiledata.io.