Mastering ETL Testing with Chris Thompson and Mike Calabrese

In a land of testers far and widely journey seeking answers, seeking skills, seeking a better way O the Test Illumination Testing Podcast Guiding testers with our nation awesomeness from ancient realms to modern days, they lead the way. Oh, the Testicle Automation Testing Podcast.

Hey. Today we'll be diving deep into the complexities of ETL testing with our guest Chris Thompson and Mike Calabresi from rtts. With over two decades of combined experience in data QA and automated testing, Chris and Mike are going to guide us through the intricacies of an actual data warehouse project plan they shared with me at an RTTS event we did last year. They uncover the challenges of data mapping, exploring different transformation types and discuss the critical role of a comprehensive mapping document. And Chris and Mike also highlight the importance of rigorous data testing to prevent costly errors and ensure accurate decision making. So join us as we explore the critical aspects of ETL testing and data project planning and gain valuable insights from two industry veterans. This is a must listen episode to anyone involved in data testing and looking to optimize their data transformation process. And since this presentation, RTTS has also introduced AI into their ETL process and they'll share how you can do the same in our upcoming Test Guild webinar exclusive this week called Leveraging AI to Simplify and Speed Up ETL Testing. Register now even if you missed the event or can't make it, because I'll be providing the recordings after the event to see AI and ETL in action by going to testguild me aietl. Hope to see you there.

All right, welcome everybody. Welcome to the Test Guild event for Query Search. So today in my segment I'm going to be talking about creating a project plan for a data warehouse project. A little bit about myself. My name is Chris Thompson. I'm a Senior Domain Expert, Data Testing practice military veteran. I spent five years in the United States military flying inside these things called P3 Orions. Seb Hunter I have a Bachelor's degree in Computer Science from the University of Delaware, done successful implementations of QA projects in the data space for well over 15 years. I've been with RTTS for 21 years and I started with RTTS as an entry level test engineer. Coming directly out of college, I worked in numerous fields including pharmaceutical, utilities and retail. And that's me. And here we have Mike. Mike, yeah.

Thanks Chris.

So I joined RTTS as a Test.

Engineer back in 2009. So during that time I've gained over a decade of experience successfully implementing both automated testing solutions as well as Data and ETL validating testing solutions. Technovel, expert on Query Surge, our flagship data testing solution and currently helping clients around the world with their query search implementations. I have a Bachelor's in Science from Hofstra University in Computer Engineering.

All right, thanks Mike. All right, so I'm going to give a little bit of an introduction into ETL testing and specifically for creating a project plan around data warehouse testing. So data testing is an integral part of our development of any data project, including data warehouses, data migrations and integration projects. Bad data from deep techs can cause companies to make decisions that could cost millions of dollars or in health related fields could cost dearly for somebody their lives. One of my first clients was a big pharmaceutical company. Had no idea what data testing was. I was sent there as a functional automation engineer with some background in rational robot for those that remember that tool. And also Mercury windrunner. Well they happen to have Mercury windrunner and I was trying to test Data Warehouse with Mercury Windrunner and yeah, it didn't go too well. I ended up having to use a lot of SQL and I actually did use WinRunner to execute that SQL bring back results into a very antiquated database called Microsoft Access. And that's where I would do the comparisons using minus queries, which we'll talk about here shortly. Queries. We started off using windrunner and using minus queries and we it was very inefficient, took a long time to execute and could not complete our task doing it that way. So eventually we built a utility in VB which eventually became Query Search. But we'll get into that a bit later a little bit more about the reasons why we want to test data is Walmart handles more than 1 million customer transactions every hour. Data is imported in the databases that contain greater than two and a half petabytes of data, equivalent to 160 times the information contained in all the books in the US Library of Congress. Additionally we have Facebook 40 million photos, Twitter with 85 million tweets per day, Google 1 terabyte per hour and eBay 80 terabytes per dev.

So there's a lot of data out there.

A hell of a lot. So what is a data source? A data source is a pool of data available for extraction. The concept of data source is technologically neutral. It's not associated with any specific technology. The most common data sources are databases, files and XML data documents. But it could be just about anything that we can get our hands on. In my example of the pharmaceutical company, we were doing adverse Events, which is say you took Tylenol, got a, I don't know, a nosebleed or something, and you went to the doctor. The doctor would enter that information into an adverse event system, which would be the source system that would eventually end up in the pharmaceuticals data warehouse. So all that data would be collected and there could be many, many different systems from different states and different hospitals that would eventually need to get translated and loaded into a master data warehouse for that particular company. So what is a data warehouse? It's a collection of data or information intended to support business decision making. Data warehouses contain a wide variety of data that present a coherent picture of business conditions. Data warehouse is a huge repository of electronically organized data, mainly meant for the purpose of reporting and analysis. Most data warehouses are sent data from multiple sources. Such in my example could came from a hospital, come from a doctor affiliate somewhere, so many different places. It could be files come in as files. It could come from an Oracle database source, it could come from proprietary software, all different types of sources. It's a place where historical data is stored for archival analysis and security purposes. All right, so what is etl? And ETL is a term that describes extract, transform and load, refers to data handling process that involves extracting data from outside sources, transforming data to fit operational reporting needs, and then load that data into an endpoint, usually a database. In this case, for today's example, we'll be doing a data warehouse. So why etl? Well, businesses need to load the data warehouse regularly, incrementally, daily and weekly, so they can serve its purposes of supporting business analysis. So basically we take a whole bunch of sources from different places, we put it in the same type of format so that we can run reporting across all those sources. So it's transforming the data into one set standard. Here's an example of that. So on the left side, we have a whole bunch of source systems. They go through an etailed process and eventually get to a target data warehouse. And another example, we have multiple systems that could be different. We have an inventory system, a customer service system, and an advertising system. All sort of asking different questions, right? The came, the paint came off my widget or customer service that would end up in a data warehouse, which then could end up in data marks and then be pulled out by BI tools to say, you know, we have hundreds and hundreds of widgets where the paint is falling off. All right, so the concept of test points or ETL legs. An ETL leg refers to a single ETL process that moves, transforms data between two discrete Points. So from source to target is a test point or a test leg. A full ETL process may have multiple legs. So in my previous slide you saw that there was a data warehouse and a data mart. So each one of those is a separate leg. And we'll show that in the next slide. Test points are usually across single ETL legs. The verification is between the source and the target for that leg. And I'll talk about that in the next slide as well in more detail. An example is an operational source database. Source test point is extracted central and loaded into data web. Testing is conducted across this leg here. So here's an example of the different types of test points. So in the example above, it's a multi. So what we're going to do is we're going to say our data source over here and then we're going to test all the way across to our data mart. So we're going to test all these transformations in one leg and then on the bottom example it's breaking it into its separate entities. And both have advantages and both have disadvantages. But the single approach has, in my opinion, more advantage. In a single leg. We have more tests that need to be created, right, because there's more legs. So the same tables and the same data needs to be tested across each individual ETL leg. The tests are less complex because you could have transformations occurring across multiple legs. And if you were going to test the whole thing as a whole, you would be creating that transformation logic in your SQL to be able to test from source all the way to its final destination. Defects are easier to pinpoint. So you can imagine in the multi leg approach, if there are six or seven legs that occur and you're testing them all in one go and you find a defect or an error or a bug, where in that six or seven leg process did that defect occur? So it's more difficult, you have to dive a little more deeper into the data and you may end up having to create tests for the in the single leg approach anyway just to find that defect. Additionally, for the single leg, execution time tends to be longer. This is a little bit of a disadvantages, but it's not always the case. We have more tests, more tests to execute. Even though they're automated, they still take computational time and hardware to be able to execute those. And in the multi leg, just quickly, there's less tests that need to be created. Obviously tests are more complex, like I discussed. Defects are more difficult to pinpoint, like I discussed. And execution time tends to be shorter because there's less test.

Is it usually, Chris, you would say one or the other. Or could there be sort of a mixed approach here where you, where you'll do both single and multi leg tests?

You can definitely have a mixed approach.

That's what I usually find too in the projects that I work on.

Some of the direct mappings with a particular column doesn't have any transformations at all across all the legs till its final destination. It might be worthwhile just to create a test that just tests those one to one mappings across the entire thing. And that way you don't have to create those simple tests at each leg of the testing process. All right, so now I'm going to talk about a data mapping document. A data mapping document is frequently called a source target map and is generally created in a spreadsheet. This document acts as an essential part of the functional requirements. Following information is contained within the document. So normally a mapping document would contain your source database information such as your source table, source column. This is the same thing with your target, your target table, target column, any transformation logic and any optional types of requirements.

Definitely one of the most important documents when you're doing a data testing project.

Yes, this is it. This is what you need to get to to be able to really test an ETL process correctly. I've been on client sites where they don't even have a mapping document and I have to reverse engineer what they've done to be able to determine what they actually did to come up with a mapping document on my own. Then to get business approval that that is the way it's supposed to be because the development team had done it through verbal techniques of creating what is supposed to happen. You've seen that before, Mike.

I've definitely seen that, Chris.

Anyway, here's an example of a segment of a mapping document. We're going to get into this a little bit in more detail. Some of the transformation types that we see and this is not all inclusive but direct maps which are just one to one mappings where the column and target or the column from the source to the target goes through no transformation whatsoever. It's just a direct pull. Select the column and row type. This is where we might do some filtering to bring only specific columns of specific rows over into the target translation. This is where we translate one thing into another. So we could have say in the source side we could have state names like New Jersey, New York, etc. And in the target we have NJ and NY. So that would be a translation. Translating the name into its abbreviation. In this case, lookup is where we take a particular item. It's similar to translation, but we look it up in another table to figure out what the value should be. Transposing is where we take columns and turn them into rows or rows into columns. We don't see it very often, but I have seen it in the past, so we put it on the list. Field splitting here. An example field splitting is take in the source side we have a full name column where Chris Thompson is in that field. And we're going to split that into two columns, one for first name and one for last name. So it would be first name Chris, last name Thompson. So one column into two field merging is exactly the opposite saying the source side we have Chris Thompson, but it's in two columns, one for field, one for last name. And we put them together in the target side calculated and derived fields. These could be very, very complex to relatively simple. Take for example a sales database. And I want to aggregate into the data warehouse particular values say for the month of August. All sales dollar amount. All the sales dollar amount for a particular month. We will calculate that and load that into the target. And that would have to be tested to make sure those calculations are correct.

You know, some of these seem more complex than others and I have a feeling that's going to become important.

Absolutely. How'd you guess, Mike? So some testing methods, so I'm not going to go into them all or in any detail, but there's the, what we like to call staring compare. So we run some SQL or we export stuff into Excel and we look at it side by side and make sure the fields were migrated correctly. Obviously this is very error prone to human error. Also, how much data can you really test doing it that way? The second method that I'll talk about is the minus queries. So this is where you take the source minus target and you end up with a result. And the result would be the difference between the two. However, if there are columns in the target and you do source minus target, you will not see those columns or rows. You would have to do to do it correctly, you got to do source minus target and then union target minus source to get the full difference. This is highly time consuming and taxing on hardware. Double the work practically is double the work. So query search comes in to play here. Automation with query surge. So this is bulk data verification testing sample sizes up to 100%. We have lots of clients that do that. We have management of our test assets within query search. It stores all your, all your tests. We call them query pairs, but Query search stores them all. And you could also group them together into what we call test suites. Test scheduling. So say every day at midnight an ETL batch job occurs and new data ends up in my data warehouse. And I just want to test that data, but I want to execute it when the ETL completed. Then I could schedule to say, run query surge at 3 o' clock in the morning and run this group of tests so that when I come in in the morning at 7, I have instant results waiting for me. Persistent access to test data. Query Search stores in its own database all result sets so you can go back in time to see what occurred on previous executions of your tests and robust reporting to see how many tests passed, how many failed over time. An automated data testing approach with Query search can significantly improve coverage, organization and efficiency when compared to the previous mentioned manual testing techniques. All right, enough with Query surge. Let's move on to. All right, so what we came here to do is to show you a simple process of, for a simple project of creating a project plan. So some of the things that we're going to need is we're going to need to gather some project documents. So that mapping document, of course the most important one, we're going to have to gather any other supporting requirement documents, data model documents, we're going to have to estimate the time it's going to take us to review all that documentation and determine the number of test engineer resources, determine the number of ETL or test legs, determine the number of cycles or releases. Additionally, which is the, probably the hardest part of this whole process is determine the complexity of each mapping. So we have low complexity, which this is no standard, it's just what we think is the best approach is low complexity has no transformation logic, they're all one to one mappings or have very minor transformation logic, including a change to data types from source to target, such as a varchar being converted to an integer, selected row filtering and minor translations. We also have medium complexity, the transformation logic, including translations, joins across tables, field splitting and field merging. And then we have our high complexity transformation logic including major translations, multiple joins, calculated or aggregated fields, transposing derived fields, and match and merge. We also need to know, of course, search is installed and configured for the project and does the lead or test engineers require training. Alright, so I'm going to now jump over to our mapping document for today, which comes from our query search tutorial. So this is available for anybody that downloads a query search Trial, they will get the documentation for the tutorial as well as this mapping document. So there's, you know, a bit of a story of this tutorial. Sample companies coming over to a data warehouse. We have two, two companies, Z City and Exmart. And that data is being detailed into a MySQL target data warehouse. For today we're just going to do the project plan for this leg here. The Z City part being ETL over into the target data warehouse. Here is the data model for Z City. Very complex. What do you think, Mike? Three whole tables.

I mean, it seems like a lot, but you know, given the time frame we have, maybe it's all we can do right now.

I agree. So we have three tables, purchase sales and sales item. And we have these MySQL data warehouse, which has dims. Now we're talking, now we're talking, right? Dims and fax, a little bit more advanced of a schema. And here is our lovely Z City mappings for our data warehouse. So we have mappings which is just a mapping number, a source table, the source column, the source type transformation logic, the target table, target column and target type. And we also have some comments if there's additional logic that needs to be applied and example of what the data looks like. So we're just going to go through each one of these and talk about the translation type that is occurring as well as what we think are low complexity, medium complexity and high complexity. So we'll look at the first one, Mike. We're looking at auto sequence with the target table Customer dim. It's an id. Customer id. It's also a primary key, Mike, and it's an integer. So what kind of translation type do we think that could be?

You know, I'm kind of leaning towards low complexity here is where we're just incrementing things here.

Right. It's just an auto sequence. So we're just going up 1, 2, 3, 4, 5, 6, 7 on the target side.

I'm going to say between low and medium. I can't decide. I don't know.

Let's see what I guessed. Well, I said translation median. Mike, what do you think? You know what, now I'm thinking about.

That kind of makes sense. We'll have to apply some tests to make sure that the auto sequence is being done properly. We're seeing this as a primary key too. Right. So we're going to make sure there's no duplicates in our, in our test. So we're going to have to get.

A little bit more complex than just.

A straight count and direct map. So I agree.

All right, good, because I was going to override you anyway.

All right, so now we got 1, 0, 1. This is a direct map. So this is pretty simple. It's a varchar to a varchar. And so I'm going to guess low. It's a direct map. Sure. It's not a high one?

No, it's definitely not high.

No, you're right. It's a direct map. All right, so now we go into something a little bit more interesting. So here we have the example that I've been talking about during the slide presentation is the full name field in the source column in the target data warehouse. It's two columns, first and last. And so we extract the data customer dim first from source purchaser full name by splitting full name by the space and taking the first substrate. All right, what do you think, Mike?

Well, so we have a full name column. That means the whole name is in on the source side. And then we're going to split out the the first name here. Huh. So we definitely have some transformation logic. I'm going to say that's, that's a medium. I mean, there is some logic here, but maybe it's not that complex something that we can code around here.

That's what I got. Field splitting and medium. And similarly for the last name field, it's going to be the exact same thing. Field splitting medium. All right, so what about the purchaser date join field being a direct map? Except there is a join, Mike. There's a join on the customer dim table to the date dim table on the customer dim join date id, date dim ID and pull date dim date.

Well, I mean, you say direct map, I almost only think low complexity. But you're adding some complexity here with this, this join. So I'm going to categorize this. I'm going to guess medium here.

Yeah, we got a medium. All right, so additionally, I would continue on going through this process with Mike and eventually we come to an agreement on everything. For these next few, I will pull up something a little bit more complex, which is the one down here, actually. Purchases are bill same. So on the source system we have columns for both the billing address and right here, the shipping address and the billing address. However, you will notice in the data warehouse we have a single table that contains all the addresses. And then on the customer dim table we have the shipping address ID and the billing address ID. So this has to be ETL'd over and it's a Little bit more complicated of a thing to do. So there's some logic here. And I won't read it. I don't want to bore anyone. But if anybody wants to read it, take a look at our tutorial data. But obviously this is a bit more complicated than your standard one, as we're. We are doing one thing for one way and if the build bill and shipping address are the same and something different, if they're both different. So what do you think, Mike?

Well, since you haven't done it yet, I'm gonna guess this is high complexity, but also because of the fact of this transformation logic that we see here looks quite complex. Developing a test around it I think will be pretty involved. So I'm going to guess high here.

Well, let's agree on it's a high then. I mean, if we were going through.

This document ourselves, I was saving it for you, right now it's still white. I didn't highlight the whole thing. That's okay. All right. It's high, honey. Oh, no. All right, and additionally, to save some time, we've gone through this together and we've come up with our results across the board for this entire table, as well as these other tables that I did not hide the thing for. So we have three tables from the source side coming over in the data warehouse. And we've figured out the complexity for each one. And now I can move on to the next part of the project plan creation. All right, so now back to the creation of the project plan. We now have some information on our complexity of our tests. I additionally pre filled out some information as well. So we have estimated four hours to review the documentation. What do you think, Mike? You think that's good?

Yeah, I think so.

I'm gonna be the lead and I selected Mike to be the test engineer. So. All right, so now back to the creating the project plan. We now have some of the numbers that we need. So for reviewing the documentation, I'm going to put that at four hours, the number of test engineers. Mike's going to be the test engineer for today. So I have one. I will be the lead. The number of ETL legs. It's just one. There's no intermediary legs. The number of release and cycles, we've estimated at 4. We have 7 low complexity tests, 21 medium complexity tests, and 8 high complexity tests. All right, so we now have the information we need to create a project plan. We're not going to create it from scratch. Query search is building a tool called the query search. Project plan estimator tool. It's built in alchemy, also known as Survey Gizmo. This is in beta format. This is the first time it's being publicly displayed. But it will automatically create our project plan for us based on the information that we've already got and we could provide it. So I'm going to put my name very quickly in here. I'll use my RTTS email and Mike, I am going to be the lead.

You got it, Chris. It's your show.

So I click next. Next. The project estimator inputs. It gives you some basic information of what you need to do. We've already discussed most of it, so I won't go into any detail. We are doing the Z City data warehouse project.

Estimated total times to review project documentation and assets. We said four was the total number of test engineers out of one. How many test legs? We said one. Estimate total number of releases. We said four was the total number of low complexity. I believe we said seven. And the medium complexity? 21. And how many highs do we have? Mike, what was it?

Eight.

I think you're right. I think it was eight. Does query search need to be installed and configured? No, for me it's already installed. Already configured. Does the test lead require training? No, I am an expert. But Mike is not an expert so he needs training. You could also set a start date, but we'll just leave that blank for today and then I'll click next and these calculations automatically occur. This is based. These numbers and calculations are based off of query search and RTTS is best practice and what we find occurs on client sites and for our customers. So we go through different phases. Our first phase is test strategy and here are all the tasks associated with doing a test strategy. Analyze the documentation, define ETL legs, define environments, define release schedules, define high level project schedule, define defect workflow. Right. Test strategy document and any query search training. And then it breaks it out into how many hours for the lead and how many hours for the engineer.

It seems again of work. A lot of work there, Chris.

I do have a lot of work on that stuff. Phase two is test planning. Identify data validation test. This is where we identify. We sort of already done that to some extent during our previous talk when we talked about the mapping document. Define and assign resources. In this case it'll be easy because I'm going to assign everything to you, Mike.

Well, so far you've got most of the work, so I'm feeling pretty good.

That's soon to change. To find a detailed schedule, install Corey Surge and create a test plan. Oh yeah, you're right. I do have a lot of hours going on there. It's the next page though, Mike. This is where it changes the test design. Create low complexity tests, create medium complexity tests, Create high complexity tests, verify queries, create query pairs and test suites and review and oversight. I only have eight hours in this one, Mike.

Looks like I got a lot over here.

I don't know if my 8's accurate though. I may have to change that calculation based on who's doing the work underneath me.

Yeah, well, in this case you can probably take that number down and then.

Execution analysis phase four. Similarly, execute query pairs of tests suites, create median collected test queries, create high complexity, Verify queries, create query pairs and test suites and review and oversight. And when I click this next button, it'll create a high level project plan for query search with some basic terminology, an overview, project assumptions, all the tasks we just talked about, along with all the hours associated with each phase. It then would add up all these different phases for each of the lead and the test engineer hours and Looks like you got more work than me.

Yes, so it worked out that way.

Resource in days rounded up. So we have 16 days for me, 21 days for the engineer, and in total it's going to take. The engineer is going to be the roadblock here. So that's going to take 21 days or 4.2 weeks. And that is about it. This tool will eventually be available to the public and to our customers. From querysearch.com expect more information to come in the coming months.

And it's having gone through putting these together, I think, you know, estimating the timelines are one of the hardest things to do, especially at the outset of a project. So hopefully we learned some things today from Chris and his insight here onto how to sort of break down the mappings and the translations and transformations that you'll be seeing. And you know, this tool definitely will help with that.

Thank you Chris and Mike for your ETL awesomeness. And once again, you don't want to miss this upcoming webinar on how to use AI with ETL to make it even better for testing. All you need to do is go to testguild me aietl and for everything of value we covered in this episode, head on over to testguild.com a505 all right, so that's it for this episode of the Tuskill Automation Podcast. I'm Joe. My mission, as always, is to help you succeed in creating 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.