In my experience, something like a csv compare can be done programmatically without too much of a speed hit - so long as there aren't many errors.
The way I'd handle it is to have your baseline CSV files being the data you expect each table to contain, one for each table (excluding any timestamps or other time-sensitive data - you'd need to check that separately).
Then a simple routine to export the contents of each target table to CSV after you've run your data through the API will give you your CSV comparison files. I strongly recommend using a query that retrieves specific fields for this since "SELECT * FROM tablename" will throw comparison errors any time the schema changes.
Next, do a file-level diff. At this level, all you're doing is checking whether or not the files are identical. If they are, you don't need to go any further. If they're not, add the table name to an in-memory list of which tables need to have a more detailed diff run against them (it helps if your files are named for the table you're comparing - so you might have \baseline\customers.csv and \results\customers.csv).
The fun part starts at this point - and this is the part that's... not so much error-prone as painstaking and can cause more than a little angst. I've written my share of these, so I know the "joy" involved. The biggest problem when you have differences in table data is that it's very easy for humans to recognize a missed row, but rather more complicated for software.
It's also time consuming, particularly with a lot of data.
First, I'll open both files for reading. For smaller files, it's probably easier to read the whole thing into memory rather than stream it, but for larger ones, reading line by line is probably better (less in-memory data - which matters when you have a CSV comparison of files with multiple thousands of lines). Reading the file as a record set is better still, despite the large memory overhead for large files (and can save you the need to export your data to CSV files - although I prefer to do the export anyway because I can use the exported CSV files as references and quickly update baseline CSVs when I need to).
Then I'll go through them, line by line. If the lines are identical, I move to the next line. If they aren't, I start a field-by-field comparison.
At this point, you've got a number of options:
- You can look for another line in the baseline that matches the one you've got in the results (this is useful if every field differs from the expected value - chances are something got missed and you've got an off-by-x error). To be able to do this, you have to make sure to read the file with the ability to move forward and backwards through the rows.
- You can list the differences between the fields (you probably should do that anyway - it makes tracking down any differences a little easier)
- You can set a level at which you're not going to check for more differences (again, useful when you have an off-by-x error because once you hit the missing row you're not going to match anything unless you can build a good algorithm to find the match and go forward from there)
- You can report each row with differences as a single difference, or each table with differences as a single difference. I don't recommend reporting at the cell level because of the off-by-X issue.
Regardless, you use the same routine to do all the comparisons - give it the files to compare and call it from your master routine. That way you can easily add new tables to the routine and you only have to build the logic once.
Your biggest issue is going to be that if there are differences, it will be slower. How much slower will depend on how much data you're comparing. I've seen this take an 8 hour run (which included a lot of functional tests - the database comparison generally needed a few minutes because of the by then immense memory load (not helped by the tool retaining its log in memory rather than writing to the hard drive and flushing the information)) to over 24 hours. Yes, I split that particular script into multiple smaller runs and re-baselined.
