Multi-table data validation tool
A multi-table data validation tool is run following migration of data from a source database to a target database. The multi-table data validation tool extracts data from source and target locations into memory, transforms data and masks confidential data as needed, then performs two types of data comparison, including row count and data content comparison. Result files of each comparison are available to the migration team, enabling updates and improvements to the migration tools. The multi-table data validation tool may further be used to extract requested data from either the source database or the target database. The multi-table data validation tool may be dockerized as a container for ease of deployment in different environments.
1. An apparatus comprising:
a processor; and
memory coupled to the processor, the memory comprising instructions that, when executed by the processor, cause the processor to:
access a source database and a target database, the target database generated via migration of the source database from a first database engine to a second database engine by a database migration software program, the target database comprising tokenized portions being masked in the target database, the tokenized portions tokenized during the migration, the tokenized portions non-tokenized in the source database,
load a first table from the source database into first memory locations, the source database being a first type of database,
load a second table from the target database into second memory locations, the first memory locations different from the second memory locations,
determine the tokenized portions being masked in the target database,
mask the tokenized portions in both the first memory locations and the second memory locations,
perform a table data comparison between the first table and the second table, wherein the tokenized portions being masked are excluded from the table data comparison between the first table and the second table,
store results of the table data comparison in a data mismatch file,
perform a first comparison between the first memory locations and the second memory locations to determine a mismatch in a number of rows between the first table and the second table, wherein the first comparison is stored in a row count file,
perform a second comparison between data stored in the first memory locations and the second memory locations to determine a mismatch in the data between the first table and the second table, wherein the second comparison is stored in a column delta file, and
analyze the row count and column delta files to update a software program used to migrate the source database to the second database engine,
wherein the tokenized portions being masked are excluded from the first comparison and the second comparison.
2. The apparatus of claim 1 , the tokenized portions comprising confidential data.
3. The apparatus of claim 1 , the memory comprising instructions that, when executed by the processor, cause the processor to access a configuration file indicating masked portions of the target database.
4. The apparatus of claim 3 , the memory comprising instructions that, when executed by the processor, cause the processor to exclude the tokenized portions being masked based on the configuration file.
5. The apparatus of claim 1 , wherein the first table comprises elements of a first data type, the second table comprises elements of a second data type, and the first data type is different than the second data type.
6. The apparatus of claim 5 , the memory further comprising instructions that, when executed by the processor, cause the processor to transform the elements of the second data type into the first data type.
7. The apparatus of claim 1 , the memory further comprising instructions that, when executed by the processor, cause the processor to:
perform a row count comparison between the first table and the second table; and
store results of the row count comparison in a row count mismatch file.
8. The apparatus of claim 1 , the memory further comprising instructions that, when executed by the processor, cause the processor to:
perform a data comparison between the first table and the second table; and
store results of the data comparison in a data mismatch file.
9. The apparatus of claim 1 , the memory further comprising instructions that, when executed by the processor, cause the processor to perform a column level data analysis by:
selecting a column of the first table;
selecting an analogous column of the second table;
comparing first data in the column with second data in the analogous column; and
generating a first file to store differences between the first data and the second data.
10. At least one non-transitory machine-readable storage medium comprising instructions that, when executed by a processor, cause the processor to:
access a source database and a target database, the target database generated via migration of the source database from a first database engine to a second database engine by a database migration software program, the target database comprising tokenized portions being masked in the target database, the tokenized portions tokenized during the migration, the tokenized portions non-tokenized in the source database;
load a first table from the source database into first memory locations, the source database being a first type of database;
load a second table from the target database into second memory locations, the first memory locations different from the second memory locations;
determine the tokenized portions being masked in the target database;
mask the tokenized portions in both the first memory locations and the second memory locations;
perform a table data comparison between the first table and the second table, wherein the tokenized portions being masked are excluded from the table data comparison between the first table and the second table;
store results of the table data comparison in a data mismatch file;
perform a first comparison between the first memory locations and the second memory locations to determine a mismatch in a number of rows between the first table and the second table, wherein the first comparison is stored in a row count file;
perform a second comparison between data stored in the first memory locations and the second memory locations to determine a mismatch in the data between the first table and the second table, wherein the second comparison is stored in a column delta file; and
analyze the row count and column delta files to update a software program used to migrate the source database to the second database engine,
wherein the tokenized portions being masked are excluded from the first comparison and the second comparison.
11. The at least one non-transitory machine-readable storage medium of claim 10 , the tokenized portions comprising confidential data.
12. The at least one non-transitory machine-readable storage medium of claim 10 , further comprising instructions that, when executed by the processor, cause the processor to access a configuration file indicating masked portions of the target database.
13. The at least one non-transitory machine-readable storage medium of claim 12 , further comprising instructions that, when executed by the processor, cause the processor to exclude the tokenized portions being masked based on the configuration file.
14. The at least one non-transitory machine-readable storage medium of claim 10 , wherein the first table comprises elements of a first data type, the second table comprises elements of a second data type, and the first data type is different than the second data type.
15. An apparatus comprising a processor and memory coupled to the processor, the memory comprising instructions that, when executed by the processor, cause the processor to:
access a source database and a target database, the target database generated via migration of the source database from a first database engine to a second database engine by a database migration software program, the target database comprising tokenized portions being masked in the target database, the tokenized portions tokenized during the migration, the tokenized portions non-tokenized in the source database,
extract a first table from the source database into first memory locations, wherein the source database was generated using a first database engine;
extract a second table from the target database into second memory locations, the first memory locations different from the second memory locations;
determine the tokenized portions being masked in the target database;
mask the tokenized portions in both the first memory locations and the second memory locations;
perform a first comparison between the first memory locations and the second memory locations to determine a mismatch in a number of rows between the first table and the second table, wherein the first comparison is stored in a row count file;
perform a second comparison between data stored in the first memory locations and the second memory locations to determine a mismatch in the data between the first table and the second table, wherein the second comparison is stored in a column delta file; and
analyze the row count and column delta files to update a software program used to migrate the source database to the second database engine,
wherein the tokenized portions being masked are excluded from the first comparison and the second comparison.
16. The apparatus of claim 15 , the memory comprising instructions that, when executed by the processor, cause the processor to access a configuration file indicating masked portions of the target database.
17. The apparatus of claim 16 , the memory comprising instructions that, when executed by the processor, cause the processor to exclude the tokenized portions being masked based on the configuration file.
18. The apparatus of claim 1 , wherein columns comprising the tokenized portions being masked are marked to be excluded from the data comparison.
19. The at least one non-transitory machine-readable storage medium of claim 10 , wherein columns comprising the tokenized portions being masked are marked to be excluded from the data comparison.
20. The apparatus of claim 15 , wherein columns comprising the tokenized portions being masked are marked to be excluded from the data comparison.