How do you test a data load?

Question

I'm working on a project that involves loading batches of 3000+ files to several dozen tables. There is no user interface, and the tables are simply available for querying. What are the best practices for testing this type of process?

• Loading a small set of data and validating each and every member?
• Loading all the data and validating a subset?
• Loading all the data and validating counts, averages, and other metrics?

Are there other types of testing that can be done, or is some combination best?

Answer 1

I would create two (sets of) test cases. One that runs often, is fast and validates only statistically and another one that runs less often (depending on your project schedule - every week or month) but checks all the data.

The fast test would import all the data then check:

1. the number of records
2. take a number of records and check their integrity (pick every K records where K could be something like number of records / 100)
3. if you have columns that support aggregate operations which execute fast you might want to check those too in the fast tests.

For the test that is executed less often just do a full integrity check either by doing 1-1 comparisons or by computing hashes.

Answer 2

The way to test the data integrity is very simple:

1. Load the file using whatever method you choose.
2. Using queries reconstitute the file.
3. Compare the 2 files. (md5sum as a simplest measure)

Since you are loading 3000+ files into about a dozen tables you will need a way to identify the set you have just loaded. And simple formatting of the files would make the job easier but that is the only way I can see you verifying that the load was properly done.

Answer 3

I think you want two kinds of tests:

• the "positive" set which is sample enough data to validate the tool
• the "negative" set which consists of isolated sample cases of the edge cases which broke things and were fixed. These should be built over time.

Strategy presumes real world conditions such as poor specifications and bad data hygiene.

Answer 4

I guess it depends on what other validation checks you have in the process that actually uses the data in these files

If it contains its own validation, then I'd just do a quick check on the first few lines of each file to verify it's in the right format.

If it has no validation, then I would do a full validation check on the files so that the process that uses the data can run without a problem.

Personally I prefer the first method of adding the validation in the process that will be using the data, and doing a quick validation on import.