System, apparatus, and method for maintaining data quality using automatic timeliness verification mechanisms

1 . A system, comprising:

an information system;

a first computing apparatus; and

a second computing apparatus adapted to maintain a source data table and a batch data table, wherein

the first computing apparatus is adapted to:

periodically update the source data table with updated system data at a first regular time interval, comprising:

obtaining, from the second computing apparatus, the updated system data and the source data table;

identifying one or more of a new record, a changed record, and an expired record in the updated system data obtained from the database server;

when a new record is identified, inserting the new record to the source data table;

when a changed record is identified, updating the changed record in the source data table; and

when an expired record is identified, deleting the expired record from the source data table;

periodically update, in an iterative or recursive manner in conjunction with the periodically updating of the source data table at the first regular time interval, the batch data table with the updated system data at a second regular time interval, comprising:

obtaining, from the second computing apparatus, one or more delta records in the updated system data according to a latest timestamp and the batch data table;

comparing the one or more delta records obtained from the database server to one or more corresponding target table records in the batch data table;

when the comparing identifies a delta between the one or more delta records and the batch data table, inserting the one or more delta records to the batch data table;

when the comparing fails to identify a correspondence between the one or more delta records and the batch data table, excluding the one or more delta records from the batch data table; and

updating the latest timestamp for a next iteration of the updating of the batch data table;

the second computing apparatus is adapted to:

obtain, from the information system, one or more data tables and metadata corresponding to the one or more data tables;

obtain one or more transaction tables corresponding to the one or more data tables;

determine whether a refresh timestamp or a modification timestamp associated with the one or more data tables is within a predetermined time period from a timestamp associated with the one or more transaction tables;

when it is determined that the refresh timestamp or the modification timestamp associated with the one or more data tables is within the predetermined time period from the timestamp associated with the one or more transaction tables, record a timestamp positive result in a profile result table; and

when it is determined that the refresh timestamp or the modification timestamp associated with the one or more data tables is not within the predetermined time period from the timestamp associated with the one or more transaction tables, record a timestamp negative result in the profile result table and update a data visualization application for reviewing the timestamp negative result, wherein

the data visualization application comprises a graphical user interface that is accessible using a third computing apparatus for reviewing and confirming the timestamp negative result, and

the first regular time interval is greater than the second regular time interval.

2 . The system of claim 1 , wherein one or more data points associated with a confirmed negative result is excluded from a data trend calculation at the second computing apparatus.

3 . The system of claim 1 , wherein the first regular time interval is one week, the second regular time interval is twenty four (24) hours, and the predetermined time period is between forty eight (48) hours and ninety six (96) hours.

4 . The system of claim 1 , wherein the second computing apparatus is further adapted to:

construct the profile result table based on one or more completed record updates in a latest iteration of the updating of the batch data table;

determine whether one or more parameters are defined for updated data included in the one or more completed record updates;

when it is determined that one or more parameters are defined for the updated data, determine whether the updated data conforms to the one or more defined parameters;

when it is determined that the updated data conforms to the one or more defined parameters, record a defined parameter positive result in the profile result table;

when it is determined that the updated data fails to conform to the one or more defined parameters, record a defined parameter negative result in the profile result table and update the data visualization application for reviewing the defined parameter negative result;

when it is determined that no parameters are defined for the updated data, determine whether the updated data conforms to one or more rule-based logic thresholds;

when it is determined that the updated data conforms to the one or more rule-based logic thresholds, record a logic threshold positive result in the profile result table; and

when it is determined that the updated data fails to conform to the one or more rule-based logic thresholds, record a logic threshold negative result in the profile result table and update the data visualization application for reviewing the logic threshold negative result.

5 . The system of claim 1 , wherein the second computing apparatus is further adapted to:

construct the profile result table based on one or more completed record updates in a latest iteration of the updating of the batch data table;

determine whether the updated data conforms to one or more rule-based logic thresholds;

when it is determined that the updated data conforms to the one or more rule-based logic thresholds, record a logic threshold positive result in the profile result table; and

when it is determined that the updated data fails to conform to the one or more rule-based logic thresholds, record a logic threshold negative result in the profile result table and update the data visualization application for reviewing the logic threshold negative result.

6 . The system of claim 5 , wherein the one or more rule-based logic thresholds comprise a z-score logic threshold.

7 . The system of claim 1 , wherein the second computing apparatus is further adapted to:

upon recording the timestamp negative result in the profile result table, issue a notification to one or more user accounts with a subscription to a data table associated the timestamp negative result.

8 . A method, comprising:

periodically updating, at a first computing apparatus, a source data table maintained at a second computing apparatus with updated system data at a first regular time interval, comprising:

obtaining, from the second computing apparatus, the updated system data and the source data table;

identifying one or more of a new record, a changed record, and an expired record in the updated system data obtained from the database server;

when a new record is identified, inserting the new record to the source data table;

when a changed record is identified, updating the changed record in the source data table; and

when an expired record is identified, deleting the expired record from the source data table;

periodically updating, in an iterative or recursive manner at the first computing apparatus in conjunction with the periodically updating of the source data table at the first regular time interval, a batch data table maintained at the second computing apparatus with the updated system data at a second regular time interval, comprising:

obtaining, from the second computing apparatus, one or more delta records in the updated system data according to a latest timestamp and the batch data table;

comparing the one or more delta records obtained from the database server to one or more corresponding target table records in the batch data table;

when the comparing identifies a delta between the one or more delta records and the batch data table, inserting the one or more delta records to the batch data table;

when the comparing fails to identify a correspondence between the one or more delta records and the batch data table, excluding the one or more delta records from the batch data table; and

updating the latest timestamp for a next iteration of the updating of the batch data table;

obtaining, at the second computing apparatus from the information system, one or more data tables and metadata corresponding to the one or more data tables;

obtaining, at the second computing apparatus, one or more transaction tables corresponding to the one or more data tables;

determining, at the second computing apparatus, whether a refresh timestamp or a modification timestamp associated with the one or more data tables is within a predetermined time period from a timestamp associated with the one or more transaction tables;

when it is determined that the refresh timestamp or the modification timestamp associated with the one or more data tables is within the predetermined time period from the timestamp associated with the one or more transaction tables, recording a timestamp positive result in a profile result table; and

when it is determined that the refresh timestamp or the modification timestamp associated with the one or more data tables is not within the predetermined time period from the timestamp associated with the one or more transaction tables, recording a timestamp negative result in the profile result table and updating a data visualization application for reviewing the timestamp negative result, wherein

the data visualization application comprises a graphical user interface that is accessible using a third computing apparatus for reviewing and confirming the timestamp negative result,

wherein the first regular time interval is greater than the second regular time interval.

9 . The method of claim 8 , wherein one or more data points associated with a confirmed negative result is excluded from a data trend calculation at the second computing apparatus.

10 . The method of claim 8 , wherein the first regular time interval is one week, the second regular time interval is twenty four (24) hours, and the predetermined time period is between forty eight (48) hours and ninety six (96) hours.

11 . The method of claim 8 , further comprising:

constructing, at the second computing apparatus, the profile result table based on one or more completed record updates in a latest iteration of the updating of the batch data table;

determining, at the second computing apparatus, whether one or more parameters are defined for updated data included in the one or more completed record updates;

when it is determined that one or more parameters are defined for the updated data, determining whether the updated data conforms to the one or more defined parameters;

when it is determined that the updated data conforms to the one or more defined parameters, recording a defined parameter positive result in the profile result table;

when it is determined that the updated data fails to conform to the one or more defined parameters, recording a defined parameter negative result in the profile result table and updating the data visualization application for reviewing the defined parameter negative result;

when it is determined that no parameters are defined for the updated data, determining whether the updated data conforms to one or more rule-based logic thresholds;

when it is determined that the updated data conforms to the one or more rule-based logic thresholds, recording a logic threshold positive result in the profile result table; and

when it is determined that the updated data fails to conform to the one or more rule-based logic thresholds, recording a logic threshold negative result in the profile result table and updating the data visualization application for reviewing the logic threshold negative result.

12 . The method of claim 8 , further comprising:

constructing, at the second computing apparatus, the profile result table based on one or more completed record updates in a latest iteration of the updating of the batch data table;

determining, at the second computing apparatus, whether the updated data conforms to one or more rule-based logic thresholds;

when it is determined that the updated data conforms to the one or more rule-based logic thresholds, recording a logic threshold positive result in the profile result table; and

when it is determined that the updated data fails to conform to the one or more rule-based logic thresholds, recording a logic threshold negative result in the profile result table and updating the data visualization application for reviewing the logic threshold negative result.

13 . The method of claim 12 , wherein the one or more rule-based logic thresholds comprise a z-score logic threshold.

14 . The method of claim 8 , further comprising:

upon recording the timestamp negative result in the profile result table, issuing, at the second computing apparatus, a notification to one or more user accounts with a subscription to a data table associated the timestamp negative result.