ETL detective work

Scott Andery, QualiTest Group, shares a tale of ETL testing’s strengths and pitfalls.

So, it’s early in the morning, and as I drink my strawberry peach iced tea, I notice that last night’s ETL shows no sign of the strawberry peach iced tea you purchased yesterday from the store. Panic! Horror! How could this have happened? What went wrong? Fortunately, the backup ran in verbose mode and none of the latest staging files have been erased yet, making the ETL testing much easier.

As the name implies, we begin by determining which ETL step went wrong, the E (extract), the T (transform) or the L (load). But first, I go to my pocket from when I showed up yesterday and check the receipt: maybe the bottle scanned wrong and a different iced tea got manually entered. Or maybe a double smudge altered the UPC code so as to match a different product. My receipt shows a description that has the right iced tea brand and flavour. Well, I guess that I need to keep on digging.

We know that the data got entered correctly at the store – my receipt proves that. The only scenario I can think of where data does not enter the store’s database would involve a power failure, but I know that there wasn’t one. I decide to work backwards. The load log looks clean. There’s still a backup copy of the most recent run that got loaded into the data warehouse. I search through the file and cannot the item in the send side of the load. Well, that eliminates the possibility that a chunk didn’t copy or became malformed, which has been known to happen. What’s next? Again, I work backwards.

Maybe something went wrong during the transformation. ETL transformations are not as pretty as a butterfly emerging from a chrysalis, but the idea is similar; what emerges is a thing of beauty ready to take flight, in this case into the data warehouse. Many rules can get applied which transform the raw sales data. As a result, an item in a person’s sales, or the entire sales of a person could get scrubbed. Technically, a register’s entire daily sales might not process correctly (like if they logged in oddly). Fortunately, we have a small log of transformation errors, which I can read to see if records of my purchase got wiped out here. Nope, that wasn’t it. There are a few seasonal items that were miscoded that transform dropped because the description field was empty. I make a note of it because that is the kind of mistake that can be easily fixed in the future.

So that leaves two possibilities; either something went wrong during the extract, or the store never logged the sale in the first place. Which is more likely? It is more likely that something went wrong during the extract, which is when the daily sales get copied to the centralised system from where the backing up occurs. If that’s the case, there should be some record of a file not copying or not copying completely. I check the log and see no errors. Every file seems to have copied, although I see a few small discrepancies in file size. There are no big discrepancies to suggest that a file got truncated. But a few files do seem to be smaller, and it is still possible that something could have gotten miscoded. I have seen a few cases where data changed because a chunk of data got cached and rewrote over the next chunk of data to come down through the pipeline. At least that’s my working theory. I should be able to do a text search on the description field from my receipt on what got extracted to the centralised system.

So I check that, and come up short. It was never there. How can this be? We’ve got the E, the T, and the L, and none of them show any sign of this iced tea I’m drinking. I remember that I bought it yesterday. Wait, was it yesterday? I pull out my receipt. Yes, it was yesterday according to the receipt. Could the date on the receipt be wrong? No, that’s crazy talk. I’m missing something here. Think, think, think. What am I missing? Then, I see it. The one thing that points out for sure where the mistake occurred. Right there on the receipt, I see the name of where I bought my iced tea, a gas station I stopped at yesterday while running around doing errands. I did not buy it at our store, or any of our other stores that feed into our data warehouse. I got my drink at a gas station. Well, there’s only one thing I can do now. And that is to fix the blank descriptor fields for the seasonal merchandise I noticed earlier. I do a little more research and discover that the fields will process correctly in the transformation if I stuff some spaces into the name field – they can still look blank to the customer, but they cannot be zero-length or transform will toss the entry. A week later, we come up with a better solution: we create new codes for marked downs that track to specific prices, with descriptions like “MISC MDSE $5”. A few months later, our store decided to increase the size of our markdown section, based on the BI of visible markdown sales, which are now properly tracked.

Edited for web by Cecilia Rehn.