A testament to committment

Something very inspiring happened the other day.

We were in the middle of a test iteration (read that check iteration) comparing our understanding and expectation of the numbers to the ones we were handed by the developement team. I’ve got to confess that after a while, things become a bit monotonous and it is quite easy to lose focus.

That Thursday was one such day; humdrum of the typical activities was bearing down on me when a colleague’s email, requesting help, caught my eye. A new lookup view I had created, had caused her to get animated about a previously matching set of numbers that now no longer matched; and were resulting in many additional rows of data that she did not expect.

To elaborate, I had created a ‘fresher’ version of a UK postcode file that allowed us quick lookup into a typically ‘Geographical’ categories associated with a postcode. These, typically in the education sector, are County, Region, Local Education Authority and Country; There are others but are insignificant in the current context;

The SQL queries that my colleague wrote returned different set of Local Authorities using the new lookup than it’s older equivalent. It js worth noting that I used ‘Local Authorities’ in the last sentence, but ‘Local Education Authority’ before that. These are two different things (those interested can read all about it here and here), however the Local Education Authorities are merely called ‘Local Authorities’ within the education sector. It took her only a gentle reminder from me to ensure she had used Local Education Authorities rather than Local Authorities (as defined in the postcode Geography) and all was good.

What happened next was a testament to my colleague’s total committment and dedication to her work.

After about 10 minutes, I could see her restless; and no doubt she started telling me what she’d tried and how she couldn’t work something out.

She was trying to find out – for herself – ‘why’ the old lookup wasn’t good enough, by investigating what, and by how much, the new lookup was – well, new! I had informed her earlier that while investigating one of the issues she had previously mentioned, I had noticed that there were some new postcodes in a certain region which our postcode file had not picked up and therefore, resulted in an ‘Unknown’ region.

I then decided to speak with the development team to find out if they had a newer version which we could use – hence the resultant new lookup.

My colleague then tried to compare the two.

Initially by doing simple counts:

select count(distinct postcode) from <oldPostcodeTable>;
select count(distinct postcode) from <newPostcodeTable>;

She found out that the <newPostcodeTable> had about 189K more postcodes than the old one

So she compared like so:

   old.postcode as OLD
 , new.postcode as NEW
 from <newPostcodeTable> new
 left outer join <oldPostcodeTable> old on new.postcode = old.postcode

Surely, it did give her all new postcodes with many (~ 189K) NULLS; But she wanted only those that weren’t in the old one!

I reminded and demonstrated this scenario using Venn diagrams whilst informing her of the EXCEPT clause which she could use to get this:

 select distinct postcode from <newPostcodeTable>
 select distinct postcode from <oldPostcodeTable>

As a true tester, she argued on why she cannot achieve the same using the approach she initially took – that of joins; This time, I decided to allow her to do it for herself and suggested that there was no point of using the “old” vs “new” postcode as there were *no* ‘old’ postcodes that had a corresponding ‘new’ postcode; She then took up the challenge and returned back with the same 189K output with the following query

   old.postcode as OLD
 , new.postcode as NEW
 from <newPostcodeTable> new
 left outer join <oldPostcodeTable> old on new.postcode = old.postcode
 where old.postcode is null;

On the face of it, this query isn’t wrong. It does bring back exactly what she required; but seems a bit overkill to first select everything based on join conditions (in this instance – bring back everything even if it doesn’t match) and then discarding that doesn’t match. EXCEPT on the other hand would compare and display only those that don’t match – thus, in my mind, producing result in a single pass; therefore being quicker.

There was yet another way of achieving what was required – using a sub-query

select distinct postcode
 from <newPostcodeTable>
 where postcode not in (select distinct postcode from <oldPostcodeTable>);

I then ran all these three side by side to check its execution time (I could have easily gone into Execution Plan, but that, in this instance, was not necessary). The results were:

EXCEPT query ran in 3 seconds
NOT IN query ran in 9 seconds
JOIN query took 22 seconds to complete!

Thats good learning!

Now personally I’m not a great fan of EXCEPT when it comes to finding exceptions; but I don’t discount it out altogether, it has it’s place where it is the best alternative; this was one of them.

Anyways, upon showing the three ways of achieving the same result, my colleague was then surprised to see the timings on each of them; Immediately the advantage of using one technique over the other was apparent to her, especially when she compared the timings in multiples of the fastest one.

All of a sudden the monotonous afternoon became a rewarding one for my colleague and inspirational for me.

Inspired by my colleague’s steadfastness, persistence and her incredible hunger to learn new things; not just learn, but practice and be good at it! In my humble opinion, this is what makes good testers. I know, if I had been in her position, I would have taken things for granted merely on someone(trusted)’s word and would have continued with my work without wanting to know the why’s or how’s etc; but not anymore.

Perhaps the most important reason why this is inspirational is because this colleague of mine is new to Testing, had little to no SQL skills about 6 months ago and is 64 years old!

Whats more is that she is extremely keen to advance her SQL skills and I’ve committed to provide and all help and support I can. There’s no way you can’t!

And just yesterday, just before she left for a well deserved holiday, she said, “You know, before I joined this team, I couldn’t wait to get out of the workplace for the holidays. Now, whilst I’m happy that I’m going, I can’t wait to get back!”


Tip: Automated Checks in Data Warehouse

Over the last month or so, I have been assisting my manager in recruiting ‘technical’ testers. Apparently, testers with experience in testing Data Warehouse(s) (for simplicity’s sake lets call them Data Warehouse testers) are rare. Going by the candidates who have sent in their CV and/or interviewed with us, I can certainly say, good data warehouse testers are indeed few and far between.

One of the things I had noticed, in fact, almost trait amongst most testers we interviewed, was that nearly all of them depended heavily on row counts to determine whether the data had reached its destination successfully. There’s nothing wrong with the counts, but I resent it being the only way to check the data traversal.

I remember a tester, Peter Morgan presenting his experience of Data Warehouse Testing at a BCS event a couple of years ago and from what I can remember, his team had collaborated with the development team in automating a lot of checks – something I was very pleased to hear since I had been doing precisely that on a couple of projects myself. I also recollect reading something about his presentation but do not have the link to his interesting experience.

So, when I realised that there hasn’t been much (read that ‘any’) technical stuff on my blog, and in the eternal hope that this those interested testers might benefit from or even add to my own learning by sharing their ideas, there was an opportunity to write a (or may be a series of more than one) quick post that show how I try to incorporate automation checks, using simple examples, to assist me and supplement my testing activities when testing data warehouse.

I hope they are not too extensive to bore you to death or put you off in this aspect of testing. I’m hoping to write these posts as individual items that are candidates for automation check rather than picking up eligible items in order of how the data warehouse projects progress.

Data Warehouse projects are ideal candidates for implementing automated regression checks. As much as possible. The data volumes are huge, typically over a couple of millions of rows of transactional data. The bigger the business of the customer, the larger and complex the data set it.

The most basic, yet the most important of all checks is verifying whether the source data matches the target data. Between the source and target, there are numerous stages that a data might go through – something that the Extract-Transform-Load, or the ETL process. As a tester, I normally tend to mimic this ETL process that allows me to check data to its lowest (or desired) granularity. And because we can automate it, my preference is to do these checks on 100% of data and not just a sample set.

So, as a quick tip using a very simple example, let me show how I would try to reconcile data from source table to the target table.

Lets assume the following source table – table1

ID Val
1 10.10
2 10.12
3 12.12
4 13.44
5 1.66
7 1.66

that after the ETL process gets into the target database table – table2 – as

ID Val
1 10.10
2 10.12
3 12.12
4 13.40
5 1.66
6 1.00

Whilst these are too simple an example to be realistic, I just wanted to convey the idea behind the tip I wanted to share.

In most cases, the first check that follows the ETL is to do a count so a tester would write something like

select count(*) from table1;

to check the source row counts and

select count(*) from table2;

to check the target row count.

Now, both these would return the value 6 as the output – which would indicate that total rows in = total rows out. This doesn’t mean it ends here. It doesn’t even mean the ETL has worked. So what next?

The most common answer I have got is to do a minus or an except query. By this, a tester would check what is in one table thats not in the other. So, it would be implemented as:

select id, val from table1
select id, val from table2

or the following using T-SQL

select id, val from table1
select id, val from table2

the output would be

ID Val
4 13.44
7 1.66

This only tells me what the source data was that did not make it (or made it incorrectly) to the target. It does not tell me what it turned up into the target table as. I do the except other way around to get this information. So, using T-SQL it would be:

select id, val from table2
select id, val from table1

which give me

ID Val
4 13.40
6 1.00

Again, this tells me of those items that were delivered into the target table that did not match the source. It doesn’t tell me what the expected (or the actual source) data was that was transformed this way.

I would need to use both these individual set of data, marry them to see the whole picture of what exactly is different and where. The example we’ve used is a simple two column one with just two ‘differences’. Scale this up to millions of rows within a table that has tens of columns (typically, these could be anywhere from 5 to 30 columns. Or more.)

I think there is an efficient way to get this information in one go.  Here’s how.

Lets look at what we have. The tables have two columns, an integer ID and a corresponding decimal VAL.

I could use the ID field as an identifier and then use the VAL fields as expected and actual values to see how they match/differ.

In order to see both expected and actual values together, we need to combine the two sets of data into one. I can achieve this using the UNION. Also, because the VAL field is decimal, I could add them to a 0 (zero) without altering the original value. So, I have:

     t1.id    as id
   , t1.val   as expectedVal
   , 0        as actualVal
from @table1 t1


     t2.id    as id
   , 0        as expectedVal
   , t2.val   as actualVal
from @table2 t2

we can then sum up the expected and actual values, like so:

   , sum(comb.expectedVal)  as 'Expected Value'
   , sum(comb.actualVal)    as 'Actual Value'
from (
             t1.id as id
           , t1.val as expectedVal
           , 0 as actualVal
        from @table1 t1
             t2.id as id
           , 0 as expectedVal
           , t2.val as actualVal
        from @table2 t2
     ) comb group by comb.id

This gives us the following output:

ID Expected Value Actual Value
1 10.10 10.10
2 10.12 10.12
3 12.12 12.12
4 13.44 13.40
5 1.66 1.66
6 0.00 1.00
7 1.66 0.00

Finally, we aren’t really interested in the ones that match. It’s the differences that we want to concentrate on, so, just limit the output to the differences by adding the following condition at the end.

having sum(comb.expectedVal) <> sum(comb.actualVal)

Thus giving us the exceptions as:

ID Expected Value Actual Value
4 13.44 13.40
6 0.00 1.00
7 1.66 0.00

Now, that is more valuable information for the tester and developer that assist both in understanding the root cause of the discrepancy – and possibly fix it at the earliest convenience.

If you’ve reached this far, I thank you for your patience. I hope this has helped you.

I’d appreciate your feedback, comments, critique on this. Please share if you have found this useful!


I love numbers; I have been good at remembering numbers, be it times table, telephone numbers, even bug numbers when I was in charge of a team that supported a significant number of clients from Australia to the US.

I still remember the name of the scripts (and screens) of an ERP product  I used to work on. The modules ACP for Accounts Payble, ACR for Receivable, SLS for Sales, PUR for Purchase, GLD for General Ledger and each one had progressive screens numbering from 1101, 1102 etc. The most dreadful and feared of them was ACR1112 (I’m sure people who’ve worked on BAAN/MK might recognise them – from over a decade ago!) that played the most important role in currency conversion when we implemented EURO for many clients.  I even remember the rounding problems while handling 4-6 digit currency exchange rates that created currency differences that led to mismatches in many GL batches being posted! We decided to get around the differences first by multiplying by 100, doing the calculations and then dividing by 100 when saving/posting it!

It is important to remember small things like these in our jobs. They go a long way in helping us deal with slightest of discrepancies we might notice.  I started noticing, or rather, got interested in finding out discrepancy since a very young age (related post here) and continue to do so even today; so much so that it almost embarks almost upon being an obsession, i.e. to have the numbers to be perfect.

This may be my number bias (might consult James Lyndsay), but I use that to my advantage; and I think most testers do.

In my current job, I’ve been working on reconciling numbers based on how the business try to see the data. The only difference is that with hundreds of thousands of transactions that run into millions of pounds, the business user is not really concerned with a discrepancy of a few pounds;  I am.

The Analysts keep telling me that if the numbers are ‘close’, it is acceptable, which I can see a point for them, but the tester in me just does not agree with not matching the numbers or matching just about.

It is essential, especially in the data warehouse testing, to test for the correctness of the data to the lowest granularity possible/available. As they say, “Look after your pennies, and pounds will look after themselves”.

My argument (also) is, If I have tools that allow me to compare vast amount of numbers (data) to the desired precision in an ‘acceptable’ timescale, should we not use it to ensure we have the precise result? Yes, there is a certain degrees of freedom one can take – simplest being rounding a 6 decimal number to 3 decimal places (or two if the amount is monetary). As a student of Statistics I’ve always known that every number is important.

In the world of finance, these decimals are ever so more important. Rounding errors across hundreds of thousands of transactions gives you potential discrepancy of millions of pounds! The more complex the rules, the worse it can get.

In my current work, I have to split an amount received into different components. Each component is then allocated a percentage share based on the type of transaction. Each share goes in a ‘pot’, which in turn may have further sub-pots that are dependent on eligible number of days.

On the face of it, the rule might look simple (and it has been made simple for obvious reasons), but if the expectation is to allocate say a hypothetical figure of £100*, then the allocation could be shown as:

£10 – split as £6, £4**

£20 – split as £14 and £6

£30 – split as £15 and £15

£40 – split as £10, £10, £10, £10

I would expect to see £6, £4, £14, £6, £15, £15, £10, £10, £10 and £10 and not 10 transactions of £10 each.

Multiply this by thousands of transactions generated daily over a number of years, each resulting in unknown number of splits and add to this the complexity of having the fields storing the values up to 6 (or even 8) decimals place, multiple currencies that have fluctuating exchange rates, with the dreaded rounding errors a big possibility and you are in hours of head scratching/pulling exercise (wonder if I’ve lost my hair that way?) while you script, debug, test, fix, debug, test your code before the script can be useful in testing it on a large scale.

It is a software development life cycle on its own!

Its only when your script is being run for real that you get a chance to fine-tune to the exceptions that the production data invariably throws.

Some of the scripts I have put in have been unchanged for quite some time (albeit they underwent at least half a dozen iterations), but as the time goes by, there is always some data that will slip past; some that will force me to tweak the script; there’s always an exception to the business rules that you’re never made aware of…

Whilst this may not (necessarily) point at the shortcomings of the scripting, they definitely give an insight into the data quality issues – at times highlighting the shortcomings of the application itself; at times user behaviour  (on how they use the system and/or enter data) ; thus contributing – continually – in lessons learnt activity.

It is only when you see at least (and I say this very very conservatively) a fair few runs over a period of time that you would see every transaction, its splits and the numbers that can easily add up to the higher level of analysis that gives you a feel of how small numbers end up skewing the numbers to a ridiculous proportions.

I remember one of my first tests ended up sending shivers in my spine when I found a difference of approximately £1.2 million between expected and actual values! This happened because for a start I’d rounded a field to incorrect number of decimals and more importantly, because of the incorrect splits! Even when you know how important it is, it was definitely a lesson learnt –

When testing a data warehouse, 1+2+3≠2+2+2!

If you’ve managed to reach this far, thanks for reading; I’d appreciate all your comments, feedback, critiques.  Thanks!

* Actual amounts are never so rounded
** Neither are the actual splits when you consider percentages and division operations on a monetary amount.

Another way to test? Visualise!

One of the challenges in testing a data warehouse is pin-pointing the source of discrepancy (I prefer calling an inconsistency in the expected and actual value as a ‘discrepancy’ rather than a bug, more on that some other time…).

Generally emotions run high in many teams when someone whispers (or cries out) “bug” amidst a team of developers.

Personally, I like them; Hey, I’ve been one. I especially admire their tenacity – specifically in the data warehousing domain – to carefully extract, ruthlessly junk, elegantly transform and delivered the polished data into the datamart. Its’ a tough job being a data warehouse developer.

So, I treat them with great respect. I engage with them and spend time debating why some ‘discrepancy’ should or should not be classified as a ‘bug’. But for this debate, I prepare rather rigorously; run the test at least twice to ensure I’m not the one making a fool of myself – check the logic, SQLs and most importantly, any rounding I may require to do. I then produce a worked example, following each data item from source to target (via all layers it traverses, if feasible), showing them why it was not transformed/calculated as expected.

If they agree with my finding, it is logged as a bug, else I get to know of an undocumented business rule that requires me to got back to the BA for confirmation and subsequently to my SQL scripts, make change and repeat the process all over again.

This process helps me understand their code behaviour, and in turn, helps them know the source of the problem and therefore allows them to deliver a fix quickly.

I see this as a win-win situation; a rather good symbiotic relationship!

Today was such a day; found myself running a script, and there wasnt much of a discrepancy as such but I did observe a rather strange pattern to the data. Because I had been looking at a date range with counts against each month, I noticed the data was split across two dates within a month – some being reported against start of the month, while others against the end of the month.

It would have taken me a good half a day to individually go through each possible combination of the columns (dimensions) to check for the one causing it; There had to be a better, quicker way to identify.

My default way of quickly identifying issues is to do this visually by getting data into Excel, analyse them using pivot tables, conditional formatting etc to quickly identify the problem areas. This time it was just not good enough adopting this technique.

I’d noticed that the data appeared twice a month, increasing gradually over a period of time so I emailed the developers about this and ask if they knew of any special rule that required us to have different reporting dates each month and left for lunch.

By the time I returned back from lunch, the developers had dug around and replied back they’d noticed it before but hadn’t known of any rule or why this was the case;  I’d noticed the pattern earlier so thought it would be interesting to see how the pattern looked when charted. This is my way of defocusing, I play with data in Excel a lot.

I separated the two sets of data – one with counts for the start of the month, other set that reported for end of the month.

I charted the data and saw this (ok, this isnt exactly what it was, but an extremely good representation of it using similar date ranges and google spreadsheets!):

This was almost a ‘Eureka’ moment for me; Almost because I wasnt 100% sure I’d found it. I emailed this back to the developer and asked, isnt this the month when the system was migrated onto the new platform? Immediately, the reply asked me to check the system the two data set represented. It wasnt surprising that they indeed were from two separate systems, with the old system one suddenly ending at March 2010.

I’d spent about 15 minutes and got the root cause without running different SQL scripts to get to the bottom of this discrepancy. Was glad that there was no bug, but we learnt something and it now formed a part of our ‘knowledgebase’!

Visualisations are all around us; we are being bombarded constantly with them in our daily lives – and they are fantastic tools too; a picture is worth and all that!

Testing doesn’t necessarily have to be technical, it can be visual too. I happen to work in an area where data is of great importance *before* it is visualised; but there’s no restriction on visualising the data any way possible to ensure it is visualisable! Afterall, every little helps!

Finally, some interesting reads on the visual aids for testers I’ve found are:

  1. blink tests that James Bach demonstrated during the Rapid Software Testing course and his article on it located here;
  2. another article by Ajay Balamurugudas on it – it’s here.
  3. an excellent presentation by David Evans at the TestBash in Cambridge last month; covered brilliantly by  Markus Gärtner here.

I have and continue to use visualisation a lot when testing; I’d be very interested to know what your experience has been.

Thanks for reading. All feedback, critique and comments welcome!

On recalling what you learnt at school

A few days ago, I got the opportunity to speak with my old teacher again, Professor Kalamkar after a good 17 years or so (that makes me feel really old!); A Professor of Statistics, he also happened to belong to the same school as I did, albeit years apart!

Although I had been keeping myself updated with the ongoings on the campus via friends, websites etc, it was especially satisfying to catch up with him and getting to know, first hand, how the University had progressed leaps and bounds. Felt happy and immensely proud.

Learning about his new blog (its here, for those interested in Statistics), I enthusiastically started reading his blog articles in a chronological order only to find how much I had distanced myself from the subject I had majored in!

I did find a post that I could comment on, and as of now it awaits moderation; but after submitting the comment, I started thinking about how much I could remember (and demonstrate) from the college days, and on the subject, if anyone asked me that minute.

I set about thinking of ‘Probability’ (which incidentally was the topic I had read and commented on). How would I demonstrate to anyone how I could calculate Probability – of say a roll of a dice – the most basic/standard experiment in Statistics.

Now we know that there are equal chances for me to roll any number between 1 to 6 on a single dice. i.e. 1/6 or 0.17 (0.16666…, but we’ll round it up to 2 decimals). “How” do we demonstrate that the chances are 1/6? or the probability of  rolling a number, say 2, is 0.17?

Whilst during my college days I remember being explained something along the lines of, “if you roll (a balanced) dice, say 6 times, one of the outcome would be the number 2”.

Fair enough. But this could not be proven. What if all 6 times the dice resulted in 2? What if I did it 12 times and all 12 resulted in a 2? Would I succeed if I did 18 rolls? 20?  There was only one way to find out. Experiment it.

The closest thing to experimenting with a dice was doing this virtually using my favourite tool Excel. I had decided to roll the dice enough times. How much is enough? I settled on 5000 first, then thought 7500 and then settled with a nice round number of 10000.

The best way to roll a dice in Excel is to use the function “RANDBETWEEN(1,6)” and then copying it into the 10,000 cells – took me all of 10 seconds!

Next, list the outcomes i.e. numbers 1 to 6 and then count the number of times each occurred. Also called the ‘frequency’ (done using a simple “COUNTIF” function). Finally divide each number by 10000 (i.e. total number of rolls) to give us the probability.

In my case, this was

No 1 2 3 4 5 6 Totals
Outcomes 1728 1687 1655 1667 1660 1603 10000
Probability 0.1728 0.1687 0.1655 0.1667 0.1660 0.1603 1

Bingo! Experiment successful!

Add to this the fact that the 10000 cells of actual outcomes and the summary table containing formulae, a simple F9 on the spreadsheet refreshes and recalculates these random numbers, thus conducting another 10000 rolls in a fraction of a second. The actual outcomes change but still stay within the 0.16 – 0.17 range, thus proving the experiment a success!

Now I wanted to scale this up and experiment with two dies. I also remembered that such a random experiment would have a pattern to it when I plot a simple bar chart for the amount of outcomes. This is in the shape of a bell-curve. This would allow me to demonstrate the simple, probability density function (or the normal distribution).

For the first part, I just substituted the actual outcome  formula to “RANDBETWEEN(2, 12)” – since the minimum number rolled would be 1 on the first dice + 1 on the other dice and the maximum being 6 and 6.  The ‘frequency distribution’ for this experiment now changed to:

No 2 3 4 5 6 7 8 9 10 11 12 Totals
Outcomes 933 894 906 920 942 903 905 901 919 883 894 10000
Probability 0.0933 0.0894 0.0906 0.0920 0.0942 0.0903 0.0905 0.0901 0.0919 0.0883 0.0894 1.0000

For the second part, I had to select the actual outcomes and chart a simple bar chart, but this came out all wrong.

The graph did not represent a bell-shaped curve at all. This can’t be right, I said to myself.  I thought for a bit, went for a coffee and while having coffee I remembered that the roll of dies were independent of each other! i.e. roll of a dice did not affect the outcome of other. So, all I had to do was change the formula to “RANDBETWEEN(1,6)+RANDBETWEEN(1,6)”. This resulted in the outcome to:

No 2 3 4 5 6 7 8 9 10 11 12 Totals
Outcomes 269 520 854 1109 1368 1663 1423 1132 823 574 265 10000

and the graph looked familiar:

It was very rewarding to recollect these simple, first lessons of Statistics and indeed recollected charting some of these down during the practical sessions on a graph paper. Although the sample sizes were significantly smaller!

Drawing parallels with testing, we are learning constantly, during the course of our employment, during training sessions, conferences, coaching sessions or even discussing it with a peer, development team members, business analysts, whilst reading articles, blogs, related and unrelated topics, interacting with our friends, families, children and many more sources;

It is becoming increasingly difficult, not completely un-manageable though, to store little nuggets of useful information, tips ‘n tricks of the trade and recollecting them when you need most.

A single, un-related conversation sparked the curiosity of remembering what you learn over the years, to fetch information that lies dormant in some remote corners of the brain, only to help us succeed in our craft.

I regularly try to reflect on what I have learnt new, now recalling what I had learnt ages ago goes on that list too!

Thanks for taking time to read this. All comments/feedbacks/critiques welcome!

The Toothbrush Theory

I have a habit of wandering over to Wikipedia and start reading on anything that I have been thinking about.

Recently, whilst reading, and jumping from one link to the other (there must be a name for such behaviour),  I came across the quote “… any colour, so long as its black” that Henry Ford is said to have quoted in his autobiography.

Debate about the authenticity aside, it also pointed me to a couple of chapters in Dan Ariely‘s The Upside of Irrationality where firstly, using a series of experiments, he demonstrates how we over-value our own creation and how we assume that this bias of over-valuation is shared by others. He then progresses and demonstrates that the same thing happens with our ideas too – the  ‘Not-Invented-Here’ bias, fondly called “The toothbrush theory”. The idea is that everyone wants a toothbrush,  everyone needs one, everyone has one, but no one wants to use anyone else’s.

Simply put, we tend to feel more certain, important and effective about the ideas we come up with than when someone else has done so.

How many times have we as testers felt we had the right idea to test certain things in a certain way? I’ve certainly done that. Innumerable number of times. I still do. I’ve also seen many colleagues fall into this trap.

In one of my earliest testing assignments, I got to see the toothbrush theory in action. I found myself on a young team 3 of whom were recent graduates. Each one a strong character, oozing self confidence! On one of the test cycles we were working on, there was a particularly complex piece of functionality we had to validate via an SQL script.

Impressed at how the new blood had performed during their probation period, the team lead decided to set a final task for the last week of their probation – to see how they worked together. They were tasked to collectively come up with the test script that would validate the business logic.

The three of them chose to spend time on understanding the functionality and working alone before bringing their ideas to the table. Because of the complexity of the functionality, they ended up spending nearly a week coming up with their own versions of the implementation and as expected there was a big row broke over whose idea to implement as a code.

Whilst one wanted to use views to get the qualifying data and then run the script on that, other wanted to use common table expression and the third one wanted to use functions. Merits and demerits of each approach aside, each of their solution would have achieved the same end result, but it was the notion of one idea that was being tested.

The significant amount of time spent by each tester led to the overvaluation of their own approach (endowment bias), which was difficult for them to let go (loss aversion), ultimately shutting themselves off to anyone else’s idea (not-invented-here bias).

Could this have been avoided? Would it have worked better with the way things were?

Perhaps it would have been difficult to rid two of testers of their bias in favour of the third one. If it were possible for someone to somehow convince that the chosen solution was their own brainchild, then yes, perhaps.

What if someone’s ideas constantly ended up being second best; or third best or last for that matter. In fact, what if only one (and the same) person’s ideas were the only ones that were always chosen? It impacts both parties – those whose ideas were chosen and those whose weren’t.

How do we strike a balance? I think it is difficult. Difficult, not impossible.

I think, for a start, it is essential for senior testers, test leads, test managers and co-workers to encourage and inspire each other to take pride and a sense of ownership in the work they are assigned.

But there is a negative side to this too; we could easily manipulate people by giving them false sense of ownership and get things done for ulterior motives!

This post has taken me some time to write and I still have a long way to go – in reading the book, even perhaps revisiting these posts. I may have a change of mind, giving myself a de-biasing opportunity by a sense of disinterest and non ownership!

It would certainly help and allow me to think if you leave your comments!

Thanks for reading.

I’ve started, so I’ll finish

In a true Mastermind fashion, I’ve got to say “I’ve started, so I’ll finish”; this post; and not do a wrap up of 2011…

My fascination with data must have started when I was about 9 or 10 when I helped my father unearth a discrepancy in the bank journal – it was one of those huge leather bound ledgers that had daily entries.

Me and a friend had caught a bus to my Dad’s bank to go Diwali shopping after the bank hours. I remember key staff, about 5-6 of them, including my Dad, his boss and others were still trying to resolve a discrepancy of about Rs 1500 for a particularly high profile customer.

With our constant requests of taking us Diwali shopping (mainly to get fireworks) falling on deaf ears, I said, “Let me have a go, I’ll find it for you”.

By the time I had said this, everyone was pretty tired and had wanted to give up. My Dad’s boss, who also happened to be my class teacher’s husband ordered tea and biscuits and asked the staff to relax. He and my Dad then explained to me what I should be looking for – about 7-8 entries that totalled Rs 1500 for a specific customer.

I requested paper and pen, noted down the amount, the customer name and decided to start with a mission of finding *all* entries for that customer.  I thought if I presented them with everything, they could surely work it out for themselves. The thought of this seemingly simple activity waned away at the sight of the ledger.

My Dad then came to my rescue and said, see if you can look at the last few months and gave me a specific date (the date they knew the customer had deposited a large amount). I decided to use that as a starting point; going through each entry enquiring what each item meant. I think I enquired on about 3-4 type of entries and got a hang of how they made the entries in the ledger.

After spending about 25-30 mins, I had been able to identify that some entries been clubbed together whilst others had been on what seemed like incorrect dates. I even saw an entry for 29th Feb which then I thought was odd as that year we only had 28 days and I was at a birthday party that day.

I excitedly called my Dad’s boss and asked if he could see what I had found; He was impressed with this and following on with the ‘trend’ I had noticed, they went on to uncover all the discrepancies. I just happened to overhear, “That is not the main cashier’s initials against these entries, they are someone else’s.”

This was an incident that made had a lasting impression on me. Being able to contribute to something that benefitted so many was not just satisfying, but rewarding too. I got an ice-cream and an additional Rs 5 to spend from my Dad’s boss to spend on fireworks! Probably my first pay, come to think of it – and worse still – all of it went up in smoke – literally in this case!

The data fascination continued from then on – from maintaining records, data, scoring sheets for Cricket teams, to studying Statistics and eventually ending up rather serendipitously into Data Warehouse testing!

Data warehouse testing requires a keen eye for detail especially when there is a need to investigate a data discrepancy in hundreds of millions of rows spread across a multitude of tables that span across different databases, schemas, applications or one of the data warehouse ‘layers’.

It is quite easy to get lost, frustrated and even confused when you are trying to find the discrepancy ‘needle’ in the in the data ‘haystack’; over the years, especially during my data warehouse testing days, I devised my own way of finding ways to succeed this finding mission. It was recently at the Rapid Software Testing course that I learnt, that it was the ‘focussing/defocussing’ strategy I had been applying!

Very briefly, Defocussing allows us to find a pattern that violates the test pattern and encourages to look at / investigate multiple factors at a time. Focussing on the other hand allows us to zoom in on one factor at a time that will help establish  the cause of the discrepancy. Well, at least, this is a interpretation that works for me!

The best analogy to this is a pivot table in MS Excel that allows you to look at summary values for different categories you are analysing the data for. You spot something that bothers you and you can just double click the number to get the details of what the source is for those numbers.

Same goes with data warehousing testing. It would just be easy to zoom in on a discrepancy (called ‘drill down’ in data warehousing terms) using an OLAP cube that is built on top of the data marts within the data warehouse, and then use any associated ‘drill through’ reports to see the data, but for some reason, there is always an unwillingness to provide the OLAP cube before the ETL process has been tested.  Something that I intend to write on in the new year.

In conclusion, this indeed was a rambling on something I just started to write a couple of days ago. I know of at least one (other) follower who reads this blog and critiques it enough to encourage me to do better!

As usual, comments, critiques, suggestions welcome!

Thanks for reading and good luck for the new year!

On Tester Independence, Collaboration and Productivity

I had an interesting Skype conversation with an ex-colleague recently. And had a sense of deja-vu yesterday that prompted this post.

Long story short, testing related discussion turned into a debate on how close a test team should get to the dev team. Whilst I promoted and encouraged the collaboration corner, my friends were fighting and rooting for the independence (no collaboration) corner.

I do have a stand on tester independence and (briefly) it’s this: the test team should have independence in designing and executing their tests, whilst collaborating with the dev team, yet not getting influenced by them.

When I say not getting influenced by dev, I mean not be driven by what and how they’ve coded. From my point of view, a tester can (and should) look at the code to understand it, but should not allow the code (or coder) to drive their tests – completely.

The arguments that was put to me were that of the need for testers and that this collaboration then puts the question of tester productivity into imbalance. It was interesting to hear this, considering both my colleagues happened to be working in an ‘offshore’  engagement model. Upon enquiring further, I learnt that their “fear” was that by collaborating, testers wont find as many bugs and therefore result in need for fewer (or no) testers!

Now, where have I heard this before…

There was klaxon I could hear screaming in my head when I heard both say, “how else would you measure tester’s productivity?”

Meaningless metrics and (a variant of inattentional) bias in one sentence (well, technically a question)!

Me of a year ago would have left the conversation there itself and would have found myself banging my head against a wall. Instead, I just smiled and replied “They’ve been proactive, preventing bugs, trying to build quality right at the start!”. Also adding, “If they’re really good, they’ll be creative, critical and will supply you with further information to improving the product quality!”

This also highlighted an unfortunate truth that still prevails in many workplaces – about testing being the ‘residual’ activity that gets squeezed into whatever is left over from design, development and the heavy documentation phase!

I’ve known of at least a handful of such places/projects that operate on the ‘contract based’, outsourcing/offshoring model that have and are suffering low (test) team morale resulting in high attrition rates.

Having said that, many peers, luminaries – the most notable (that I’ve known of in the Indian subcontinent where these ‘affected entities’ are based) being Pradeep Soundararajan’s venture and his Moolyavans (I’m sure thats what they are called – the valuables) who lead the way in educating the masses and helping elevate the value of a tester and the testing industry as a whole!

Thanks for reading. I’d appreciate your views, critique and comments…

How I learn from a 6 year old

This was long time due. Cold, flu, work, fever, work, more work, more flu, and much more work and a couple of drafts later here it is.

My ex-boss told me once, never discourage the enthusiasm and questions of children. I follow his advice. Religiously. Well, try my best to, and I think I do it well to a large extent.

I was fascinated by the idea of unschooling that was triggered by this video that I watched a year ago; and also by the buccaneer scholar, James Bach. It was also very fortunate to hear more from the man himself when I attended the Rapid Software Testing course that he was teaching a few weeks ago.

The six year old in question is my son who has a fascination for technology and is ever keen to see, work, explore and play with any gadgets he can lay his hands on. This was since he was about a year old or so.

The only thing he would talk when I used to visit his school during parents evening was about the over head projector, how it works, the computers children were allowed to use, what software it had, how it worked, why some things appeared different on the school pc than at home… the list is endless. One of the teachers even confessed that he knew every teacher’s password and they willingly allowed him to ensure every pc was logged in at the start of a session and correctly closed down at the end.

Whilst that made me proud, I attempted to know how it benefitted him, what the school did, if anything, that allowed him to progress and most importantly, me trying to understand how he learnt things.

The very first thing I noticed was, he wasn’t afraid of giving anything a go. He wasn’t worried about him not knowing anything. My parents have a completely opposite approach. They would be scared to use my laptop in fear of messing something up despite me insisting they work on it. It’s a different matter that my Dad has a sort of an “anti-midas” touch and I’ve seen things not working when he uses them but works perfectly fine if anyone else uses it!

I could see some sort of correlation between age and technology. Perhaps the older we get, we fail to catch up with the progressing technology and we form a shell around ourselves. We are more likely to enjoy our own comfort-zone. Most of us know that children are like sponge and they grasp things very quickly. We as parents have been able to throw more at our son and he has been able to do things faster than others at his age.

I have a tic-tac-toe game on my mobile phone. I gave my phone to him once to play this game and within 5 mins he showed me how the game had a delay before placing the next move and how he could just place three consecutive noughts or crosses in that time and win every time.

On the little big planet, how he could go to the main screen, select another user using a key combination and get that character/user in the game, how he can then use them to press a button that would allow him to progress ahead and move onto the next level! I was speechless when he showed me that.

The other day, despite me warning not to mess around with the TV menu – especially software updates, he showed me how he could create a list of favourite channels for everyone and how it would save us from going to menu, searching for our channel list and just use the up/down keys on remote. The fact that he made the excuse of “and it doesnt even mess the TV at all” was the best part of all!

Anyway; I got the chance of speaking with James during the course breaks on how I (and my son) would benefit from or go about encouraging his unschooling (defined by James as allowing yourselves/your child drive your/their own education according to the natural rythms of your/their own minds – which I read as their potential, their ability to learn – very rapidly in most cases). He encouraged me playing different games and observing how he does things, how he observes and learns from his experiences.

Over a weekend, after the course ended, father and son bonded over many things, including food – especially a breakfast at McDonalds – and a game.

The game was to see how an object behaved albeit with me having a slight advantage of having an identical looking one and having different properties.

The rate at which he formed his own testing heuristics/oracles were fascinating:

“Thats interesting, this bounces when I drop it, but does not when you try”
“I think there is a bouncy surface and a non-bouncy surface”
“The shiny side makes it bounce, the other side doesnt”
“I know its because one of the side is sticky and so it doesnt bounce”
“I think you cant get rid of the stickiness by wiping it off your trousers, lets try this paper towel”
“Let me see if it bounces on this table, this cushion, this floor, this food tray…”
“Let me see what happenes if I tried to throw it on the floor real hard”
“What if I dropped from a very high place, like if I stand on a chair, or even a building”
“My hands are warm so when I touch it warms and so it bounces, but yours are cold so it doesn’t when it gets cold. Let me see if it stops bouncing if I make it cold by placing this under the cold tropicana bottle”
“I tried to cool it down for 1 minute but it bounced back, let me try to make it colder by doing this for 2 minutes”
“Can I borrow your coffee mug so I can warm it to see if I can see any change”
“Why is it that I can press this part (of the object) but I was not able to do so last time”

This immediately reminded me of two things “rapid testing” and “context-driven testing”. He was applying his own heuristics to test an idea and rapidly swapping it for another when it failed.

“All heuristics/oracles are fallible” (James Bach / Michael Bolton, Context Driven Testing)

I think I’d prefer calling them “Fallicles” or even “Fallacles”(fallible+oracles; although happy to acknowledge anyone who has already coined this term, more importantly, in this context before! and will need evidence too!)

We carried on for a good 20 minutes and I could notice he never gave up; at least not until I had to stop. It was truly inspiring. He has such moments, but not always; but thats okay, I guess. His persistence with trying things out with whatever was available to him was commendable.

How many of us give up when we run into dead ends? I know I almost did this, this morning!

I decided to stop when I heard the last one, I said, “Do you think that Dad might be tricking you with yet another object like this one?” He said “No, that’s not possible.” I asked “Why?” and he innocently replied, “because I know you wont!”

Once we stopped, he asked me “Where did you get these from?” I answered “I got them from the best tester in the world. Do you know who that is?” Without a moment of hesitation, he replied “You!”.

Now there were two instances, that reminded me of James Lyndsay and his work on bias (which was also a topic of my previous post). There was faith, but there was also an element of biasness in my son’s honest reply.

I repeated what I said to James Bach when he asked me at the start of the RST course if I was a tester. I said, “I am, but not as good as I would like myself to be”.

James replied, “That’s good. That’s how you keep learning and strive for more.”

Agree. And the learning does not necessarily have to come from peers, sometimes children teach you a lot too!

Thanks for reading! Please do leave your comments, critiques, suggestions, tips ‘n tricks; and if you liked this post, please do share it with others…

Overcoming Irrationality

The past few months have been rewarding. On every aspects.

From a change in work, to work place to be able to spend time ‘thinking’ about testing. It is always nice to have time on hand, peers to bounce ideas off, advise from experts, knowledge from intellectuals to be able to better yourself; be it as a tester or as a human.

I remember, it was just over a year at the BCS SIGIST conference that I came across James Lyndsay’s paper “Irrational Tester“. It immediately strikes a chord. It immediately lights the bulb – I could almost feel it over my head at the time! Not only was it a wake up call, it also acts, since that day, as a check list for me.

I’ve been trying to experiment with my own version of exploratory testing at the workplace – old and new – and have been having varying degree of success. Its what suits best that counts, to be honest. Testing Data Warehouses has tremendous scope for exploratory testing (something for later day) as well as automation. I’d say a lot of exploratory testing *before* automation checks!

It is during one of these automation checks I have been undertaking recently that reminded me of the irrational tester. We have a pool of resources (I wont categorise them as ‘developers’ or ‘testers’ yet) who have been testing a part of data warehouse. Nothing wrong there; Even the Analysts have been having a poke around, which is really good to see. Minutest of details are being questioned, logged etc.

The situation now is that we have many many tests (read SQLs) that poke around at various places and do their own bit of testing.

Let me rephrase that. There are many ‘test’ SQLs that do their own bit of ‘checking’.

And because everyone has been doing their ownbit of testing, there’s no one who has bothered to assemble/write a regression pack.

Enter yours truly. After deciding on an approach to be taken, I set myself down saying – how hard can it be – looking at already written code, merging them after tidying/optimising them into a pack.

No sooner had I started merging multiple SQLs, I could see myself succumbing to almost every facet of the Confirmation Bias James mentions in his paper. Its not just limited to finding defects, but is also with other things ‘test’ related – design, approach and even execution.

I fell at the first hurdle. I assumed I knew the exact SQL script (of the hundreds) required, had a good understanding of what I had ‘expected’ it to do just by looking at a few lines of code and was overconfident because I had to get the script done in a day!

James describes this as Illusion of Control.

Even when I did get the right scripts and merged them to do a multitude of things – I couldnt get the script to return the same results as the individual ones did, I had fallen prey to Congruence bias. This was because of multiple joins to different tables with unions produced a cartesian product which wasn’t desired – which was also meant Inattentional bias!

It was not long before I realised I had to stop. Take a step (or two) back. The first thing I did was delete the script I had created (read stiched together).

Doing a retrospection, I could feel a light bulb over my head. It wasn’t exactly rocket science to realise everyone tests differently and so everyone will have their own version of a script / SQL, doing things in their own way. Some developers will write efficient SQL, analysts won’t. If I have a task to do my own thing, I should do it my own way.

Another ‘ping’ and another light bulb lit up. No better opportunity for me to use exploratory testing. Spoke to the business analyst and developer over lunch (and later at the coffee machine) to get an understanding of what was required, what tables, columns were required, what business rules were to be followed and I had my mind map (in my mind) of what I needed to do.

It is essential to point out that the strategies James mentioned were critically important in helping me de-biase. Those who might have jumped to the document might notice some of them including seeking independent explanations, keeping mind engaged by avoiding tiredness (in this case focus away from the clutter of SQLs), seeking feedback and even recognising a failing strategy and cutting one’s losses!

So, overall I’d say I ‘invested’ first half of that day learning.

A classic example of “At first, we cognize; then we recognize”.