How to debug data analysis? 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:


X is indeed increasing over time
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.
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.