Systems and methods for distributed data validation
Embodiments of the present disclosure include systems and methods for validating a target data table based on a source data table. A distributed memory comprises a plurality of computing systems, each storing at least a portion of the source data table and the target data table in local memory. Processing engines can be efficiently executed on each of the plurality of computing systems to perform comparison functions based on in-memory data. A checksum comparison engine is configured to compare source and target checksums. A data aggregation engine is configured to produce column-based aggregation summaries. A rule generation engine is configured to generate validation rules for checking by a validation engine.
1. A system for validating a target data table of a target data store based on a source data table of a source data store remote from the target data store, each of the target data table and the source data table including one or more rows and one or more columns for storing data elements, the system comprising:
a distributed memory comprising a plurality of computing systems, each computing system in data communication with at least one of the source data store or the target data store to receive at least a portion of the source data table and the target data table and configured to store the portion in a local random access memory such that the distributed memory includes the entirety of the source data table and the target data table;
a checksum comparison engine in data communication with the distributed memory and configured to respond to a checksum request by computing a checksum result indicating whether one or more source checksums computed based on the source data table match one or more target checksums computed based on the target data table;
a data aggregation engine in data communication with the distributed memory and configured to respond to a data aggregation request by computing a data aggregation summary for one or more columns of the source data store and one or more columns of the target data store;
a rule generation engine in data communication with the distributed memory and configured to respond to a rule generation request by computing one or more validation rules based on the source data table;
a validation engine in data communication with the distributed memory and configured to respond to a validation request by evaluating each row of the target data table based on the one or more validation rules without matching a row of the target data table to a corresponding row of the source data table to produce a validation report; and
a user interface configured to generate an output based on the checksum result, the aggregation summary, and the validation report.
2. The system of claim 1 , wherein at least one of the checksum comparison engine, the data aggregation engine, or the validation engine comprises a parallel instruction set, executable on each of the plurality of computing systems of the distributed memory, such that each of the plurality of computing systems executes the parallel instruction set on the portion of the source data table and the target data table stored in the local random access memory of the computing system, and wherein the parallel instruction set is executed in-memory.
3. The system of claim 1 , wherein the one or more source checksums and the one or more target checksums comprise Message Digest 5 (MD5) checksums.
4. The system of claim 1 , further comprising a scheduler in data communication with the checksum comparison engine, the data aggregation engine, the validation engine, and the user interface, and configured to:
deliver the checksum request to the checksum comparison engine and provide the checksum result to the user interface,
deliver the data aggregation request to the data aggregation engine and the rule generation request to the rule generation engine and provide the data aggregation summary to the user interface if the checksum result indicates that the one or more source checksums does not match the one or more target checksums,
deliver the validation request to the validation engine including the generated validation rules to the validation engine, and
provide the validation report to the user interface.
5. The system of claim 1 , wherein the data aggregation engine is configured to determine whether a column of the source data table is numeric based on the data elements of the source data table and whether a column of the target data table is numeric based on the data elements of the target data table.
6. The system of claim 5 , wherein the data aggregation engine is configured to compute a separate aggregation summary for each numeric column of the source data table and the target data table.
7. The system of claim 6 , wherein the aggregation summary for each numeric column of the source data table and the target data table comprises a mean value, a sum, a minimum value, a maximum value, and a standard deviation value for the data elements in the numeric column.
8. The system of claim 1 , wherein the rule generation engine is configured to compute a possible value validation rule for a column of the source data table if the column contains a limited set of values, such that the validation engine will determine that a data element of the target data table violates the possible value validation rule if the data element is in the column and has a value that is not represented in the limited set of values.
9. The system of claim 1 , wherein the rule generation engine is configured to compute a uniqueness rule for a column of the source data table if the column contains unique values for each row of the source table, whereby the validation engine will determine that a column of the target data table violates the uniqueness rule if the column does not contain unique values.
10. The system of claim 1 , further comprising a metadata evaluation engine configured to respond to a metadata evaluation request by computing a column type for each column of the source data table and the target data table.
11. A method for validating a target data table of a target data store based on a source data table of a source data store remote from the target data store, each of the target data table and the source data table including one or more rows and one or more columns for storing data elements, the method comprising:
loading the source data table and the target data table into a distributed memory comprising a plurality of computing systems, each computing system in data communication with at least one of the source data store or the target data store to receive at least a portion of the source data table and the target data table and configured to store the portion in a local random access memory such that the distributed memory includes the entirety of the source data table and the target data table;
computing a checksum result indicating whether one or more source checksums computed based on the source data table match one or more target checksums based on the target data table;
computing a data aggregation summary for one or more columns of the source data store and one or more columns of the target data store if the checksum result indicates that the source checksums do not match the target checksums;
computing a validation report if the checksum result indicates that the source checksums do not match the target checksums by:
computing one or more validation rules based on the source data table, and
evaluating each row of the target data table based on the one or more validation rules without matching a row of the target data table to a corresponding row of the source data table; and
generating an output based on the checksum result, the aggregation summary, and the validation report.
12. The method of claim 11 , further comprising computing at least one of the checksum result, the data aggregation summary, or the validation report by executing a parallel instruction set on each of the plurality of computing systems of the distributed memory, such that each of the plurality of computing systems executes the parallel instruction set on the portion of the source data table and the target data table stored in the local random access memory of the computing system; whereby the parallel instruction set is executed in-memory.
13. The method of claim 11 , wherein the one or more source checksums and the one or more target checksums comprise Message Digest 5 (MD5) checksums.
14. The method of claim 11 , further comprising determining whether a column of the source data table is numeric based on the data elements of the source data table and whether a column of the target data table is numeric based on the data elements of the target data table.
15. The method of claim 14 , wherein a separate data aggregation summary is computed for each numeric column of the source data table and the target data table.
16. The method of claim 15 , wherein the aggregation summary for each numeric column of the source data table and the target data table comprises a mean value, a sum, a minimum value, a maximum value, and a standard deviation value for the data elements in the numeric column.
17. The method of claim 11 , wherein computing one or more validation rules based on the source data table comprises:
computing a possible value validation rule for a column of the source data table if the column contains a limited set of values, such that a data element of the target data table is evaluated to violate the possible value validation rule if the data element is in the column and has a value that is not represented in the limited set of values.
18. The method of claim 11 , wherein computing one or more validation rules based on the source data table comprises:
computing a uniqueness rule for a column of the source data table if the column contains a unique value for each row of the source table, such that a column of the target data table is evaluated to violate the uniqueness rule if the column does not contain unique values.
19. The method of claim 11 , further comprising responding to a metadata evaluation request by computing a column type for each column of the source data table and the target data table.