In regards to sampling, I would perform a random sample on the Hadoop side and upload that sample to Teradata - 10K's of records as opposed to millions or billions. Then perform the full outer join on the Teradata side - small data set joined against large dataset. If the primary key of the data set == the Teradata primary index, you should get reasonable join performance. Depending on capacity, you could also go the other way with sampling from Teradata and comparing to the full Hadoop dataset. There are a number of online tools you can use to generate sample sizes for different confidence levels. The processing capacity required by either system to generate a sample set will depend on how truly random you need the sample to be or if a sampling 'short-cut' technique will work. For example, on one of our systems we have a multi-billion row table that's very well distributed. Selecting the top-n rows from each block or amp gives a useful sample, though not technically a truly statistically random sample. Assuming you're not concerned with validating field values, but only record counts, e.g. you trust the field transforms, once you have the joined sample, you then have the following pieces of information (based on key matching): count of records found in the old data sets, but not the new data sets (null) count of records not found in the old data sets (null), but in the new data sets count of records found in both old and new data sets Depending on how many different datasets or schemas with which you're dealing, the key matching technique is something that can be generated more easily pragmatically. You just need to specify key fields, as opposed to having to enumerate all fields. Assuming you included the fields in the subset, not just the keys, you could then use this matched subset to also validate field transformations. Any of the measures you've listed above could be used to get confidence between the two data sets?
... View more
