Smart Data Testing for Big Data with Bill Hayduk

Welcome to the Test Guild Automation Podcast where we all get together to learn more about automation and software testing with your host Joe Calantonio.

Hey, it's Joe and welcome to another episode of the Test Guild Automation Podcast. Today we'll be talking with Bill all about smart data testing for big data and also all types of automation topics. Bill has been around for a long time. He founded the software and services firm RTTS, a premium pure apply QA and testing organization in 1996. He also under Bill's guidance, RTTS has really successfully consulted hundreds of different engagements of projects over 700 plus corporations from global to mid size to small. So Bill really has a high level view of all the different things that different companies are struggling with in testing and automation. And he's also the leader of the Query Surge RTTS enterprise data validation tool that helps you with ETL testing a bunch of big data testing and it's a unique solution that we don't speak a lot about, but I know there's a great need for. So I'm really excited to have Bill on the show to dive in all about it. So you don't want to miss this episode. Check it out.

The Test Guild Automation Podcast is sponsored by the fantastic folks at Source Labs, the cloud based test platform that helps ensure you can develop with confidence at every step from code to deployment for Every framework, browser, OS, mobile device and API. Get a free trial. Visit testguild.comSourceLabs and click on the exclusive sponsor section to try it for free today. Check it out.

Hey Bill, welcome to the Guild.

Hey Joe, thanks for having me.

Awesome to have you. As I mentioned, really excited to have you on the show. But before we get into it, is there anything I missed in your bio that you want the Guild to know more about?

No, that's pretty much it. I think you pretty much said I'm an old guy. I am. I've been doing this for a really long time and I think the bio is fine.

I guess you calling yourself an old guy makes me an old guy as well because I think we started almost the same exact time you started, 1996. So just curious to know. I'm always loved to know what people have seen at the beginning of their career and what they're seeing now with automation and testing. Have you seen an evolution or where were we before when you started and where do you see us now? Has anything changed? Is anything different?

Everything's changed, right?

Yeah. Right.

Yeah, everything's changed and nothing's changed, so.

Right, exactly. Yes.

I started out using SQA robot in 1994 on a big bank and then started RTTS in 1996. We used SQA for about four years and then decided to open up our viewpoint and started supporting Mercury Interactive in 2000 and then Compuware and then later on the Mercury tools. So when I started it was Mercury, sqa, Segway and Compuh. As you know, Mercury being the market leader. And then in 2003 IBM bought Rational who had already owned SQA. 2006, HP buys Mercury Micro Focus buys Compuare and Segways tools and Microsoft enters the game in 2005. And now we have Tricentis, Smart Pair, a bunch of smaller players and fragmented markets and open source. So seen a lot over the years as you have from, you know, functional automation, performance testing. Then I remember when the security tools popped in. Spy Dynamics, App Scan, HP bought Spy, IBM bought App Scan. And then, you know, then service virtualization and you know, just rolling out more and more functionality as the target platforms changed and grew in complexity.

Yeah, absolutely. It was, people might not like this. It was almost easier back then because when I used Mercury products I had an end to end solution. They all integrated with one another. I had my performance test, I had test test director, you know, winrunner, all this awesome stuff. But now with the open source solutions, it seems like now people are trying to find a framework where you could plug all the open source solutions into a place to have that same type of functionality. When you talk, you've done a lot of different consulting in different companies. Do you see that as being an issue or what do you see being an issue now with open source tools, a lot of people think, oh, it's open source, it's free, no issues, no overhead. Is that truly the case?

Well, obviously with some of the open source tools, they don't have all the functionality of a commercial enterprise solution. That's one of the things. The other is one of the things we're noticing now as we bid on deals is we're writing these really complex RFIs or RFPs that are all around security. So first it used to be, you know, are you going to stick this tool somewhere? Is it going to touch our data? Is it going to, you know, and now it's like the tools you're using, whether it be open source or custom utilities or our software, you know, after Solar Winds, it's like, do you have a Trojan? Or we have to answer. We're answering one right now. That's 392 questions. So open source kind of falls in that also because it's not an enterprise tool that's been vetted by someone's security team and this is not going away, it's getting worse. So now it becomes more and more of, you know, an issue getting tools into these different architectures, especially if they're going on prem or in their cloud. If they're on your environment it's a little bit easier, but otherwise, yeah, the open source could be a problem.

So that's interesting. So you see, I always see it. Vendor tools tend to fit better into enterprise companies, but because of security I would think everyone would be kind of terrified by having an exploit or something. So did you see then it's a hard to sell now for open source tools or do you see normally that companies are more open now to maybe a vendor based tool that may be certified to not have a, you know, vulnerability baked into it or something?

I think very many of the developers or you know, programmers and developers and tester testers are totally into open source but it's the security team which is, you know, the gatekeeper. So yeah, they're going to have an issue with almost anything you introduce into their environment. Now I don't know if some of the other people you've spoken with have mentioned this, but it's a big issue now when you're bidding on jobs.

That is interesting. I've been trying to push a security podcast and security testing for a few years now and it's really, really been difficult. And I just think testers need to get involved because it's not only is it compliant a hot topic, but even security testing in general is so hot. Such a great skill to have. I don't know why it's. It's so hard to get people interested in it. I know. Are you seeing that as well or Tesla's just like it's out of my domain.

You know, again, back when Spy Dynamics and Appscan and those guys were competing against each other once they became mainstream with HP and IBM, I thought we'd see a much bigger push on the testing side because you know, they're basically application security testing and it fell into the domain of the testers but we never saw like wide scale adaptability of those tools. We've actually gone to an open source tool called O WASP Zap which is pretty good, but we don't have a lot of expertise in it. We have like two guys that know how to use it because again, our customers aren't demanding it even if we mention it. They're like yeah, that's not a part of testing team, huh?

Yeah, that's interesting. I know like at rtts you've done a lot of different implementations. One of them is application security testing. So out of all the different buckets, what do you have the most demand for then? Is the automation testing, the performance testing, mobile application testing. You do all this. What seems to be your most in demand type of service? I guess.

Well, two, it's the functional test automation and then the space we moved in called automated data validation, ETL testing. So those are the two biggest ones we see. We saw performance testing drop quite a bit because a lot of it's been commoditized. Everybody does it. They think, you know, it's not really a skill when you talk with a lot of business people. They think it's fairly straightforward and easy to do. But we know that that's not the case. Yeah, yeah, it's. It's interesting.

Right, well, that's a switch.

But we've seen performance testing used to be about a third of our overall revenue and now it's dropped to like a tenth.

I could see that because once again I have multiple online events. I tried running a perf guild every year and it just doesn't have the, you know, the amount of people that would join an automation conferen. So it's interesting you say that. So I rebranded as Software Reliability Engineering as well because that seems to be. Performance testing is kind of, seems almost to be rebranded, almost as sre. I don't know if you've heard of that, but.

No, that makes sense.

Kind of interesting. Yeah. So you did mention something that's interesting and it's data automation or, you know, automation testing of a big data or a BI reporting. It is a trend I hardly hear of on my podcast, but I know a lot of people struggle with. So I guess before we get into it, like what is that? What is etf? What kind of testing are you talking about when you say data testing?

So when companies are moving data from all their different sources to their data warehouse, whether it be on PREM or in the cloud, basically what they do is they use what's called an ETL tool, Extract, transform and load. What that tool will do. There's a position called ETL developers. They'll use the mapping document. Mapping requirements, which are like functional requirements for data, is saying, you know, okay, you. Right now in our sources, you know, first name and last name are in the same field. In our target, we're going to split them into two different fields. That would be a mapping requirement. So what they'll do is they'll, they'll either extract and load. If it's just a migration of data, we're going to take this table here and put it into our new data warehouse. Or if they're going to do some kind of transformation, whether it be splitting up fields or aggregating fields or aggregating numbers, they'll do that and input it in. So now this is the, the data store that your bi and analytics tools are sitting on top of that your C level people are using to make executive decisions. So if that data is mocked up, if there's inaccurate or there's rows missing, there's a problem there. You know, if McDonald's is basing a new hamburger with jalapenos on it in the Southwest based on numbers, as they got in those numbers wrong, their bet's going to be off by a couple million dollars. So what data validation ETL testing makes sure of is that think of it as functional testing for data. You're basically with a combination of performance. You're basically chasing the data from your sources through the transformation, if there is any into the target data warehouses and validating that the data is okay, it made it there correctly. Now, one of the interesting things that Gartner put out is that the average company has about a hundred different data sources that go into one data warehouse. Now how many, some of these big. How many data warehouses or big data lakes like Hadoop or NoSQL that some of these big companies have? We have no idea, but we know they have. They don't have one, they don't have two. They have 30, 40, 50 data warehouses or big data lakes. So there's a lot of data moving in and it's growing exponentially.

So we talk about performance testing or security testing. A lot of times people don't think it fits in the tester role. Do you think there's a lot of need for this. But a lot of people don't have it because they don't know where it fits within the software development life cycle. Or do you normally see testers that this is part of their statement of work is that, you know, they always focusing on data.

Well, that's what we didn't know when we actually launched back in 2012, probably 2006, we got an assignment at a large pharma and they said we're trying to use Windrunner, Windrunner, go back to 2006 to. Yeah, to do data validation. So we were like, well, what's data validation? We didn't really know. So they said, well, you know, we're moving data from source files and mainframe over to a new data warehouse and we want to make sure that the data gets moves over correctly. Well, windrunner wasn't architected for that. So our guys built a Java utility that did this and over time they built it to more scalable Java utility. And, you know, then we decided to take a look at the market and that's when we realized that there's a ton of people out there in the marketplace that are doing data testing. But what they're really doing is they look at their source, they look at their target, they use a SQL tool like Toad or Squirrel or whatever tool they use, and they pull the data out and dump them to two Excel files and they put them on two monitors and the testers look through two spreadsheets for one test that could have a million rows and at the time a million rows and 200 columns. So imagine going on two different monitors, going left, right, across and down, looking for data anomalies in a million rows. Now we see billion rows for one test and there could be 15,000 of these. So what we realized is people were doing it, but they were doing it what we would call manually. It wasn't really manual, they were automating it, but dumbing into Excel and hoping that they could find stuff. And they couldn't. They were. So this is where we got the idea. But we were not first to market. The Informatica was already there. There were some other players.

Nice. So do people usually contact you, say, hey, look, we have all this data and we need to somehow validate it?

Yes.

Can you help us or. That's how it works.

Yeah. So we basically built two sites because this became such a big part of our business. And we built a site specifically for data validation and ETL testing. And as you know, the whole Hadoop thing exploded and everybody wanted to get into Hadoop. They realized that there's a lot of problems with their data and that's how they, you know, tripped across us and Tricentis does it and Informatica and others. And like there's probably 12 other smaller players. And then there's a lot of the big sis have frameworks like that also do this. So it's, it's a big movement right now and it's kind of getting wrapped up in, in the whole data ops movement, which is that we call DevOps for data, which is again, the same movement that's going on in the application development space. Which is how do I get quality at speed? And the way you get quality at speed is you remove human intervention and you try to not make testing become a bottleneck. You make testing become fully automated so that the code that's being used can make smart decisions on whether to promote something. Well, it's the same in the data space also. So that's been a big movement we're seeing on both the functional automation side and on the data testing side as being DevOps. DevOps for data. DevOps for applications. Really hot right now.

Where do you see the most demand for this? Because a lot of times I think when I data now, I think of AI machine learning, even in more data. Does this have any overlap with AI machine learning? Is this some other data?

Well, the machine learning hasn't hit this realm yet as it has like functional automation. But it's getting there. But it definitely plays in every industry. Our top industries happen to be financial services, insurance, pharmaceutical, but consumer goods, as I mentioned, like hamburger places. There's tons of think who's really using a lot of BI and analytics. You go on, you're an old guy like me, so you probably don't use TikTok, you're probably on Facebook. And with the weird thing you're going to find out is you go on Facebook or you go on any retail site, if you've done some searches on Google and then go over there, all of a sudden things that you were searching on start popping up. Well, the retailers are really, really big into data, into data and analytics. So they're also a big candidate. And some of the bigger retailers are using this kind of software to make sure their data is accurate. Again, if you're betting everything on the information you're looking at in your BI and analytics solutions, that data better be accurate underneath that, otherwise what are you doing? Right?

Absolutely. So now we touched on the importance of testing data within business intelligence reporting. Testing. I think we touched on ETL testing. You mentioned DevOps continuous testing. Can you explain that a little bit more? Is that when I guess every quarter or every hour, someone congesting data from all these different sources. And so as part of your pipeline, you're just continually validating it.

Yep, that's exactly what will happen. So say you have a solution like Informatica Power center, which is one of the big ETL tools out there. So you're going to be promoting this data. Data streaming in from everywhere, as we all know from your website, from various other sources. Like I said Garner said, the average company has about 100 data sources into their data warehouse. If it's continuously coming in and if, if the ETL tool is continuously processing this data and pumping it into a staging area, obviously you don't put it right at the prod. Hopefully, then you would automatically launch a tool like this, which would automatically validate and test all your data and tell you whether you should promote it to the next phase, which may be prod or not, or whether you should roll back and take a look at the CTL process. And now sometimes it's an ETL process, could be something like Jenkins, which is a build process, could be anything. But it's automated as part of the pipeline. Yes, it's part of the continuous integration, continuous delivery, continuous testing pipeline that is part of making sure that all this goes on. If we. Especially with the amount of data which is growing, like I said, IBM says 98% of the world's data has been produced in the last two years and it's growing even faster now. If this is going on all the time, you have to test it all the time. You can't wait for the testers to come in at 9 o' clock in the morning to do it, or even if they're worldwide, it just doesn't work. So that's. So it's becoming really, really big. DevOps in for data.

Absolutely. It's worked for a company. And we used to get data drops like at a certain time at midnight, and when you come in, you had to make sure the data was all correct, it was imported correctly and everything was working correctly.

Right. Think of that at the petabyte stage.

Yeah. Now I couldn't wrap my head around it. So where does that fit in when someone's doing functional testing? Does like an automated test, like with Selenium or whatever tool ever integrate with Query Surge? Or is Query Surge just a different tool that's used at a different step within the pipeline? Do they ever integrate one another?

They can. So let's say you're using a tool like SAP, right? And you're customizing it, you're functionally testing it, plus you're getting data feeds and it's all part of the whole quality process. I mean, you won't use Selenium to kick off Query Surge because one's playing at the interface level and the other one's playing at the data level. But they could be running in the same testing environment or same testing time through the same group. So, yeah, it's another. Think of Selenium playing at the top level and you Know, a data testing solution plan at the bottom level.

Nice. So I guess another trend I've been hearing a lot of for some reason is an uptick in ERP testing and ERP testing from a functional. But I would think that ERP testing, because you mentioned ERP testing also, it was basically made with data in the background. So is that something you see as a use case for query surge as well?

Yeah, absolutely, that's one of our use cases. So ERP or any kind of enterprise application testing, whether it be a financial trading system or anything, but definitely the ERPs like PeopleSoft, like SAP or even Salesforce, all that. Again, if you're making bets on your data, you got to make sure the data is accurate. And regardless of whether it's an ERP system or a trading system or a pharma or anything else, data's coming in from all these feeds. Mainframes, your website.

You know, external vendors, internal applications feeding into especially we want to tested PeopleSoft, it had 83 separate feeds in from just internal systems and it had like five feeds from external systems. How do you know that the developers have all that data coming in and it's accurate? And especially when you're doing like something like people software, it ends up being payroll. What happens, Joe, if they cut you check and it's got two too many zeros in it? Well, that's probably a good thing.

Yeah, absolutely.

But. But if you have one less than, it's not a good thing.

Right, Right, yeah. I'm thinking if you do any type of research like health care and things like that, your data has to be spot on for sure. So since you've been doing this for many years, is there anything people could do to, to help make it so the data is more reliable? Is it just because of all the integrations all the way, people always changing the code? It's just the nature of development that you need to have a tool in place to do the validation because something's eventually going to get mucked up.

I mean, it's the nature of the beast. I mean, something's going to get mucked up. Obviously, again, all these different points, all these different sources are coming in. Typically you'll see that, you know, in like go back to the PeopleSoft example I gave. A lot of those feeds were owned by development teams that were focused on where those feeds were coming from. They were writing custom code to do this, but they knew that there was never any testing really going on. So they were, they would do some sampling because again, the data sizes were so large, they were just hoping that, you know, they would catch whatever happens. They'll be real happy when you come in with a tool and go, hey, you know, this particular column seems to be always be messed up. Can you check in the ETL code? They fix it, they're real happy now all of a sudden, you know, you've cleaned up their data. So there's not a lot you can do without using some kind of data quality tool. You know, push shift to the left as far as you can to validate the data.

So we're talking about large amounts of data here at some point. So with the tool like query search, do you actually validate every piece of data? Is it just, are you doing sampling about what they AI, machine learning, type slant, where you know, statistically, you know what the bell curve is of what, what it's going to turn out to be?

That's a good question. So the way I like to phrase it is a typical data warehouse box will be not one machine, but it'll be like some kind of 20 servers with four core CPUs on each one totaling petabytes of memory. And they'll use, they'll put query surge on a server with 32 gigs of RAM and quad core. So how can we test all the data that they have in these massive, you know, rack of pizza boxes with one little server? So the answer is no, you can't test everything. But what you can do is test every mapping. So think about it from a functional requirements perspective, right? So in a functional requirements, you could test every requirement, but you might not test every different permutation of, of that requirement to make sure it's there. It's kind of like that. On the data side, you can test what they call mappings, which are like data requirements. Again, you know, this data is going to go into these fields and it's either going to be aggregated or it's going to be exactly the way it is, or it's going to be split or it's however you're going to do it, or it's going to be totals and sums. You can follow each one of those and test them and test every mapping to make sure that all the mappings are correct. Can you test every data? Probably not, unless you built a system exactly the same size as your data warehouse or big data system. So now here's where, and you pointed at this a little bit. Here's where your machine learning and AI will start coming into play here. How do you guess which mappings and which data in those mappings, how many you need to give you 100% confidence. You pretty much have closed the gap on any data issues. That's where we see it going.

Absolutely. So would you be baking that in? Actually, before we get into that, we mentioned Query Surge a few times. I don't think we actually really officially went over what is Query Surge. So what exactly is Query Surge? And then we'll dive into a little bit more about some maybe features that can help folks with.

Right. So Query Surge is our enterprise application that was built off that utility that I talked about at the pharma that we developed. We launched at Oracle open world in 2012. We have now have almost 200 corporate customers in 40 different countries. What Query Surge will do is automate ETL testing or big data testing, which is NoSQL or Hadoop data stores BI report testing. It does data migrations, it does full DevOps compatibility, and it does your ERP and enterprise applications. So it automates the process for all the testers listing very similar to Selenium automating functional. It just has a concept of two points, source and target. So it's going to look at the source and it's going to look at the target and it's going to do a compare. Now, we have wizards that can. If it's a migration and there's no transformation at all, and you're just moving either columns or full tables from source to target, you could just point them at it and visually test the whole thing and you're done. But for the most part, that's not the way it works. For the most part, there's transformations. When the transformation logic gets in there, then you have to write some SQL to make it work.

As I see, how hard is it to get up and running? I know sometimes people create Excel spreadsheets with all these crazy macros to try to get the information. It's like, what are you doing? That's insanity. So it's. At some point you may have to write some SQL, but it seems like it can handle a lot of that out of the box.

Mm. It can handle probably 70% of the data, but the meat and potatoes might be the transformation, so you have to do it. How hard is it to get up and running? We have a tutorial built into it that takes about an hour to go through. So if you know SQL and you know the data world, you pretty much be up and running in an hour. If you don't know that we had to take a step back for one of our Big SI partners and we actually built enterprise training, self paced training and certifications on Moodle which is I guess the biggest open source education. Yeah education system out there. My kids actually used it in high school, so it's pretty good. So you could do total self paced and then we have certifications probably in a week. If you've never done it before, no different than if you've. You're a manual tester who has never done selenium. And how long would it take you to get up to speed into learning? Probably a week. Not counting, you know, your coding skills. So similar.

Nice. So I would think there's a huge demand for this. Tester should definitely know how to test data and using different tools like Query Search could definitely help. Speaking of big data, just random thought popped in my head because Query Surge is running through a lot of these records. Does it keep any type of statistics or analytics or bubble up any type of insights for you? Like hey, your developers stink at I don't know, some issue. They always tend to mess up that it analyzes just, I don't know, I'm just curious to notice.

Yeah, it has all kinds of reporting. Not only does, you know, look, search through every single row, every single data to do a comparison that you do to see if there's problems, but it looks for anomalies in columns too. So an analysis to see if maybe the same mistake is being made over and over again because it's in this particular column, so does a lot of that. We have a lot of built in reports and it's getting more and more complicated to the point where we basically have what we call ready for analytics. So if you want to connect to it with Tableau or Microsoft Power BI and write your own stuff and your own reports, you know, have at it.

Okay Bill, before we go, is there one piece of actual advice you can give to someone to help them with their big data testing efforts? And what's the best way to find or contact you or learn more about Query Search?

Right. We've. So one of the things we've worked on is making sure that any information anyone needs is on our website. So query surge.com we have a free tutorial, we have free downloads every three months. We let any tester that wants to into our our self paced training and content portal so they can learn through all our self paced courses and take the certifications for free and then they could post them on LinkedIn. So pretty much all the information you could ever want is out there. Even pages explaining what etl testing is and what DevOps for data are and how to get started and everything. So I think we've spent a we had to take a big step back and spend a lot of time making sure that all information was out there.

Thanks again for your automation awesomeness. The links everything of value we covered in this episode. Head on over to test guild.com.

And while you're there, make sure to click on the Try for Free Today link under the Exclusive Sponsor section to learn all about Sauce Lab's awesome products and services. And if the show has helped you in any way, why not rate it and review it in itunes? Reviews really help in the rankings of the show and I read each and every one of them. So that's it for this episode of the Test Guild Automation Podcast. I'm Joe. My mission is to help you succeed with creating end to end full stack automation awesomeness. As always, test everything and keep the good. Cheers.

Thanks for listening to the Test Guild Automation Podcast. Head on over to testguild.com for full show notes, amazing blog articles and online testing conferences. Don't forget to subscribe to the Guild to continue your testing journey.