On occasion as database administrators we have to migrate data for various reasons. No matter the reason or the origin, we have to test the migration to see if the result is what we expect. The question is: How?
In Part 1 of this series, I’m going to explore a simple “smoke test” of a data migration. What is a smoke test you ask? A smoke test is what I like to refer to as a sanity check that something is behaving I expect.
Example #1 – Count rows between new and old tables.
This can provide a low level of satisfaction, however, it’s probably the first of my tiers of testing. If row counts are off, your methodology is wrong and you can now start over and/or rollback the change.
-- Get row counts: (should match) smoke tests SELECT COUNT(*) FROM new_table; SELECT COUNT(*) FROM old_table;
Example #2 – Check your primary keys.
If you’re copying rows, then you expect your primary keys to be the same. If not, you’ve got a problem. Here’s a quick smoke test for key comparison. If you see a difference, your methodology is wrong and you should rollback.
-- Check PKs smoke tests SELECT MIN(id), MAX(id) FROM new_table; SELECT MIN(id), MAX(id) FROM old_table;
Example #3 – Find unwanted NULL values.
Say, you’re denormalizing or aggregating data and you’re maintaining a foreign key. You would expect to have NO NULL values. So you you need to test for their existence. If you find NULL columns, extend your testing to the source tables.
-- Find NULLs SELECT COUNT(*) FROM new_table WHERE some_fk_id IS NULL; SELECT COUNT(*) FROM old_table WHERE some_fk_id IS NULL;
…look for more to come in Part 2!