How to debug data analysis?

Question

I've came across the following problem, that I recon is rather typical.

I have some large data, say, a few million rows. I run some non-trivial analysis on it, e.g. an SQL query consisting of several sub-queries. I get some result, stating, for example, that property X is increasing over time.

Now, there are two possible things that could lead to that:

1. X is indeed increasing over time
2. I have a bug in my analysis

How can I test that the first happened, rather than the second? A step-wise debugger, even if one exists, won't help, since intermediate results can still consist of millions of lines.

The only thing I could think of was to somehow generate a small, synthetic data set with the property that I want to test and run the analysis on it as a unit test. Are there tools to do this? Particularly, but not limited to, SQL.

Answer 1

Here is a suggestion:

• Code your analysis in such a way that it can be run on sub-samples.
• Code a complementary routine which can sample, either randomly, or by time, or by region, or ... This may be domain-specific. This is where your knowledge enters.
• Combine the two and see if the results are stable across subsamples.

Answer 2

This is what I normally do - take up the most important variables (basis your business understanding and hypothesis - you can always revise it later), group by on these attributes to reduce the number of rows, which can then be imported into a Pivot. You should include the sum and count of the relevant metrics on each row.

Make sure that you don't put any filters in the previous step. Once you have entire data at a summarized level, you can play around in Pivot tables and see what things are changing / increasing or decreasing.

If the data is too big to be summarized even on important parameters, you need to partition it in 3 - 4 subsets and then do this again.

Hope it helps.

Answer 3

First you need to verify that your implementation of the algorithm is accurate. For that use a small sample of data and check whether the result is correct. At this stage the sample doesn't need to be representative of the population.

Once the implementation is verified, you need to verify that there is a significant relationship among the variables that you try to predict. To do that define null hypothesis and try to reject the null hypothesis with a significant confidence level. (hypothesis testing for linear regression)

There might be unit test frameworks for your SQL distribution. But using a programming language like R will be more easier to implement.

Answer 4

I like a multiple step strategy:

1. Write clean easy to understand code, as opposed to short-tricky code. I know statisticians like tricky code, but spotting problems in tricky code is dangerous. ( I am mentioning this because a supervisor of mine was fond of undocumented 500 lines python scrips - have fun debugging that mess and I have seen that pattern a lot, especially from people who are not from an IT background)

2. Break down your code in smaller functions, which can be tested and evaluated in smaller stes.

3. Look for connected elements, e.g. the number of cases with condition X is Y - so this query MUST return Y. Most often this is more complex, but doable.

4. When you are running your script the first time, test it with a small subsample and carefully check if everything is in order. While I like unit tests in IT, bugs in statistics scripts are often so pronounced that they are easily visible doing a carefully check. Or they are methodical errors, which are probably never caught by unit tests.

That should suffice to ensure a clean "one - off " job. But for a time series as you seem to have, I would add that you should check for values out of range, impossible combinations etc. For me, most scripts that have reached step 4 are probably bug free - and they will stay that way unless something changes. And most often, the data are changing - and that is something which should be checked for every run. Writing code for that can be time consuming and annoying, but it beats subtle errors due to data entry errors.