Target databases 110a-110n reside in cloud computing environment 110 and enable an organization to store, access, and share enterprise data for a multitude of end user software applications 112a-112n. Exemplary target databases 110a-110n include, but are not limited to, NoSQL data stores that use the DynamoDB® infrastructure, relational databases such as PostgreSQL™, and data analysis platforms such as Amazon® Redshift™ available from Amazon, Inc. ; Microsoft® Azure™ available from Microsoft Corp. ; Oracle® Cloud Infrastructure™ (OCI) available from Oracle Corp. ; Google® BigQuery™ available from Google, Inc. ; and Snowflake™ Data Cloud available from Snowflake, Inc.

Software applications 112a-112n generally comprise applications or application workflows that are accessed by end users and/or other computing systems (e.g., via application programming interfaces (APIs)) and that utilize enterprise data stored in one or more target databases 110a-110n. For example, an enterprise may configure software applications 112a-112n to perform a range of different data processing functions and/or transactions that are essential to operation of the enterprise. Software applications 112a-112n can include both customer-facing applications and internal applications. As just one example, a financial services organization may configure a software application 112a to execute a workflow for stopping payment on a check. When initiated by a customer and/or by an internal process, the stop payment workflow application can execute one or more functions on data stored in target databases 110a-110n (and/or on data stored in source databases 103a-103n) to perform the stop payment transaction processing.

Operations database 114a and data access module 114b are resources of cloud computing environment 110 that enable the monitoring of data replication processes performed by system 100 as well as managing access to replicated data stored in target databases 110a-110n. In some embodiments, operations database 114a is coupled to replication health module 108c for receipt and storage of replication status indicia and health metrics from module 108c. Data access module 114b is configured to analyze the replication status indicia and health metrics stored in operations database 114a to, e.g., determine whether software applications 112a-112n can access the replicated data stored in target databases 110a-110n. Additional detail about the functionality of operations database 114a and data access module 114b will be provided below.

As described previously, many organizations are migrating enterprise data to the cloud that is currently stored in disparate computing systems (e.g., legacy mainframes), architectures, software platforms, and geographic locations across the organization—in order to take advantage of the scalability, flexibility, security, collaboration features, and ease of use offered by cloud-based databases and infrastructures. However, this process requires significant time and resource investment from developers and system administrators to prepare the required data models and migration scripts for storage of the enterprise data in the cloud-based data analytics platform. In addition, certain on-premises or legacy data stores may not be readily compatible with the data model requirements imposed by cloud databases. In view of these challenges, the methods and systems described herein provide an improved process for seamlessly replicating source data from on-premises computing systems into target cloud-based databases and platforms using metadata and schema information provided from the respective source databases. The methods and systems described herein also beneficially reconcile data that has been replicated to the cloud to ensure data accuracy and consistency resulting from the replication process.

FIG. 2 is a flow diagram of a computerized method 200 of automated replication and reconciliation of source data from an on-premises database (e.g., source databases 103a-103n) into a cloud database (e.g., target databases 110a-110n), using system 100 of FIG. 1. In some embodiments, server computing device 106 is accessible by software installed at client computing device 102 to enable client computing device 102 to connect to server computing device 106 (e.g., via an HTTP session in a browser), provide commands for the replication of data from one or more database tables in source databases 103a-103n to corresponding data structures in one or more of target databases 110a-110n, and receive and view UI screens associated with the status and progress of data replication in cloud computing environment 110.

Upon logging into server computing device 106, a user at client computing device 102 can interact with data replication module 108b of server computing device 106 to identify (step 202) source database schema in one or more source databases 103a-103n from on-premises environment 103 for replication to corresponding data structure(s) in target databases 110a-110n of cloud computing environment 110. For example, the user at client computing device 102 can interact with one or more user interface elements to identify a software application and/or workflow (e.g., stop check payment) applicable to on-premises computing environment 103 that comprises data for replication to cloud computing environment 110. Upon receiving the identification of the software application from client computing device 102, data replication module 108b can determine a source database schema (e.g., one or more data structures such as tables/columns and related metadata) from source database(s) 103a-103n that corresponds to the identified software application. In some embodiments, data replication module 108b is configured with a mapping table or other data structure that associates software applications with applicable database schema in source databases 103a-103n. Data replication module 108b captures the source database schema and determines one or more data streams (e.g., based upon clusters and/or topics) in transaction message platform 105 that correspond to the source database schema. For example, data replication module 108b can determine that the software application workflow identified by the user of client computing device 102 is assigned to one or more data streams of transaction message platform 105 which produce messages containing the replicated data from source databases 103a-103b for that application workflow. Data replication module 108b can instruct message capture module 108a to subscribe to the specific data streams for the application workflow as part of the replication process, so that when data is pushed from the source databases 103a-103n, message capture module 108a receives the event messages with the source data for processing.

Data replication module 108b defines (step 204) target database schema in one or more target cloud databases 110a-110n in which the replicated data from source databases 103a-103n will be stored. The target database schema comprises one or more data structures in the target cloud database(s) 110a-110n that correspond to the data structures in the source database schema. It should be appreciated that, in some embodiments, there does not need to be a one-to-one correspondence between data structures in the source database schema and data structures in the target database schema. As will be described in greater detail below, data replication module 108b transforms source data received from on-premises environment 103 into a form compatible with the target database schema, instead of simply copying the source data as-is into target data structures.

FIG. 3 is a diagram of an excerpt of an exemplary source database schema 300. As shown in FIG. 3, the source database schema 300 identifies an application workflow type (STOP_PAY) 302 and data structures/metadata 304 that make up the schema—e.g., column names, data types, transform fields, filters, other operations, etc. FIG. 4 is a diagram of an excerpt of an exemplary target database schema 400. As shown in FIG. 4, the target database schema 400 has the same application workflow type (STOP_PAY) 402 as the source schema and also identifies the source application workflow type 404. The schema 400 also includes identification of the source database structure (AUD_ENTTYP column) 406 from which the data is being replicated, as well as operations 408 to be performed as part of the replication based on the operation type (e.g., Insert, Update, Delete), and the target data structures 410 into which the source data is stored.

Once data replication module 108b has configured the source database schema and target database schema as described above, data can be replicated from source databases 103a-103n to target databases 110a-110n. Message capture module 108a of server computing device 106 captures (206) one or more data streams from transaction message platform 105 that comprise messages containing the source data from the identified source database schema. As mentioned above, module 108a has subscribed to one or more streams/topics in transaction message platform 105 that correspond to the application workflow. In some embodiments, when data changes occur in source databases 103a-103n that relate to the application data being replicated, a change data capture (CDC) process in on-premises environment 103 is configured to generate and produce event messages on one or more data streams in platform 105. These event messages can comprise the changed data along with identification of the application workflow (STOP_PAY) and/or source database schema. Message capture module 108a consumes the messages from transaction message platform 105 and provides the consumed messages to data replication module 108b for processing.

In some embodiments, the event messages produced by the CDC process and/or the transaction message platform 105 for consumption by module 108a also include a configuration data structure that defines, e.g., certain thresholds or metrics for the specific data replication process. FIG. 5 is a diagram of an excerpt of exemplary configuration data 500 for a data replication process. As shown in FIG. 5, the configuration data 500 includes one or more thresholds 502 (e.g., latency thresholds) for specific replication time windows. In some embodiments, the latency thresholds apply to the data replication from source databases 103a-103n to target databases 110a-110n and can be used by replication health module 108c to determine whether the replication process is operating correctly or whether there are errors or issues that may be causing undesirable delay in data replication. For example, if the amount of time it takes for the replication process to consume source data, transform the source data into a form compatible with the target databases, and store the transformed data into the target databases exceeds the defined latency threshold, replication health module 108c can generate a notification including one or more replication health metrics for storage in operations database 114a. Additional detail about the use of latency thresholds in determining the health of a replication process is provided later in the specification.

Turning back to FIG. 2, data replication messages are consumed by message capture module 108a and transmitted to data replication module 108b for processing. Data replication module 108b transforms (step 208) the source data contained in the messages into a form compatible with the target cloud databases 110a-110n. In some embodiments, data transformation includes converting a data type of one or more data elements in the source data to match a target data type acceptable by the target cloud databases 103a-103n. As can be appreciated, target databases 110a-110n may have different naming conventions, data type requirements, or other data configuration and storage parameters than the source databases 103a-103n. As an example, the source databases 103a-103n may store string values using a VARCHAR data type without a defined maximum length, while the target databases 110a-110n may require string data to have a maximum length. In this example, data replication module 108b can transform the source data that has a VARCHAR data type into target data that has VARCHAR(x) data types, where x denotes a specific character length. In some embodiments, data replication module 108b can be configured to utilize a mapping table when performing such conversions, where the mapping table associates source data types/values etc. found in source databases 103a-103n to data types/values acceptable to target databases 110a-110n.

In some embodiments, data replication module 108b is configured to validate the transformed source data prior to storage in the target cloud databases 110a-110n. For example, module 108b can analyze the target database schema associated with the data replication process to identify, e.g., specific data types, data flags, or other requirements imposed by the target schema. Then, module 108b can confirm that the transformations applied to the source data align with the target database requirements (e.g., by comparing the transformed data to the target schema requirements to identify errors or issues). In the event that any transformed data cannot be validated, data replication module 108b can transmit a notification to replication health module 108c for publication of corresponding health metrics to operations database 114a and/or another computing device (such as client computing device 102) for remediation.

Also, in some embodiments, message capture module 108a and data replication module 108b can perform message consumption and data transformation processes for a plurality of different data replication workflows in parallel. For example, changes to source data can occur for multiple different software application workflows concurrently in on-premises environment 103. As a result, these data changes are pushed as they occur to different message topics/streams in transaction message platform 105. Message capture module 108a can subscribe to each of these message streams and thus consume messages from multiple streams at the same time for processing by data replication module 108b. Data replication module 108b can be configured to process the messages in parallel to effect data replication from source to target for multiple different application workflows at the same time. Once data replication module 108b has transformed and validated the source data according to the target database schema, module 108b stores (step 210) the transformed source data in one or more target databases 110a-110n in cloud computing environment 110.

An important aspect of the present methods and systems is the ability for server computing device 106 to monitor the health of data replication processes and to publish performance metrics associated with the data replication processes to, e.g., operations database 114a. As can be appreciated, accuracy and completeness of enterprise data that is being replicated from source databases 103a-103n to target databases 110a-110n may be essential for uninterrupted operation of the enterprise's business software applications. In the event that data is not replicated from source to target in a timely fashion and/or the replicated data is inaccurate, incomplete, or missing, it can have substantial impacts on the integrity of the organization's computing systems and the organization's ability to carry out its mission-critical functions—from data processing to transaction execution. Replication health module 108c advantageously captures real-time replication health status and metrics and publishes (step 212) the data to operations database 114a for use in dynamically adjusting the ability for downstream applications (e.g., applications 112a-112n) to access replicated data in cloud environment 110, as well as generating reports and notifications to end users such as technical personnel regarding anomalies or issues that occur during a replication process.

In some embodiments, the health metrics captured by replication health module 108c include one or more of a replication latency, a replication data error count, and a replication connection error count. For example, module 108c can be configured to measure an amount of time (also called latency) between (i) a data change occurring in a source database 103a-103n and (ii) the corresponding transformed data being stored in a target database 110a-110n. In some embodiments, module 108c can capture a first timestamp associated with the source data to a second timestamp associated with storage of the data in the target database to determine the latency. For example, when the CDC process in on-premises environment 103 detects a data change in source databases 103a-103n, the CDC process can generate a message for production to transaction message platform 105 that includes a timestamp of when the change was detected. Similarly, when data replication module 108b writes the transformed source data to one or more target databases 110a-110n, module 108b can include a timestamp with the data records that indicates when the data was written. Replication health module 108c can compare these timestamps to determine the latency. As can be appreciated, certain types of data may become unreliable or ‘stale’ as time passes—for example, real-time transaction data is updated frequently. If a replication process involving this real-time data is outside of an acceptable latency threshold, applications 112a-112n may not have access to the most current data from target databases 110a-110n which can cause errors or data integrity issues for the corresponding application functionality. To prevent this from happening, replication health module 108c can identify when a replication latency is outside of a maximum defined threshold, record the latency issue in operations database 114a, and transmit a notification message to one or more remote computing devices (e.g., client device 102) to inform personnel of the latency issue.

In some embodiments, data replication module 108b may encounter errors or issues during the data transformation, validation, and storage processes described above. For example, module 108b can determine that certain data elements are unable to be transformed or validated for storage in target databases 110a-110n. Replication health module 108c can capture these errors and record corresponding health metrics (e.g., a replication error count) and logs in operations database 114a.

In some embodiments, server computing device 106 may encounter problems establishing a connection to on-premises computing environment 103, transaction message platform 105, and/or cloud computing environment 110. For example, network instability, computing hardware failure, or other technical issues can occur that prevent server computing device 106 from connecting to databases or other resources to perform its functions. Replication health module 108c can detect these connection failures and record corresponding health metrics (e.g., a replication connection error count) and logs in operations database 114a.

Users at remote computing devices can access the health metrics/logs to generate reports that detail the status of certain data replication processes. In some embodiments, operations database 114a can be integrated with one or more external data analysis tools, such as observability services or analytics platforms (e.g., Datadog™ available from Datadog, Inc.; Splunk™ available from Cisco Systems, Inc.). In some embodiments, operations database 114a is integrated with an incident ticket management platform to automatically generate incident tickets for remediation based upon the replication health metrics.

In addition to the above, system 100 is configured to manage access to data stored in target databases 110a-110n based upon analysis of the replication health metrics. In some embodiments, data access module 114b of cloud computing environment 110 is configured to analyze replication health metrics stored in operations database 114a and allow (step 214) one or more applications 112a-112n in cloud computing environment to consume the transformed source data from the target cloud databases 110a-110n when the replication health metrics from database 114a satisfy one or more performance criteria. As mentioned previously, the latency associated with a particular replication process may be important in determining whether the replicated data in target databases 110a-110n is reliable or not. Data access module 114b can use the health metrics from operations database 114a to identify whether a latency associated with a replication process exceeds a maximum defined latency threshold. If the maximum latency is exceeded, data access module 114b can restrict access to corresponding target databases 110a-110n, and/or specific data structures within the databases 110a-110n, so that applications 112a-112n are not able to perform certain actions (e.g., read, copy) on the target data. For example, data access module 114b can adjust a setting in target databases 110a-110n to block applications 112a-112n from accessing the data. In another example, data access module 114b can re-route data access requests received from applications 112a-112n to one or more source databases 103a-103n in the on-premises computing environment 103 to ensure continued operation of the applications 112a-112n using the most current source data. In this example, when the replication latency falls below the maximum latency threshold, data access module 114a can enable applications 112a-112n to resume access to the requested data from target databases 110a-110n.

Along with the improvements to data replication described above, the systems and methods of the present application also provide a data reconciliation procedure that periodically verifies that data replicated between source databases 103a-103n and target databases 110a-110n is accurate. Data reconciliation module 108d is configured to reconcile (step 216) transformed source data in target databases 110a-110n with source data in source databases 103a-103n.

FIG. 6 is a diagram of a computerized method 600 of automated reconciliation of source data between an on-premises database (e.g., databases 103a-103n) and a cloud database (e.g., databases 110a-110n), using system 100 of FIG. 1. Data reconciliation module 108d of server computing device 106 initiates the data reconciliation procedure by extracting (step 602) source data from one or more source databases 103a-103n. In some embodiments, data reconciliation module 108d selects a configured data replication pipeline and extracts source data from one or more source databases 103a-103n for use in the reconciliation. In some embodiments, module 108d copies at least a portion of the source data into a data storage container (e.g., AWS™ S3 bucket) in cloud computing environment 110. The copied source data can comprise snapshot data (or point-in-time data) for a specific database state or time. Module 108d preprocesses the extracted source data to, e.g., identify the source database schema, retrieve configuration data, and identify the target data structures (e.g., table names) in databases 110a-110n where the transformed source data is stored. In some embodiments, module 108d can determine the above information based upon the selection of the data replication pipeline.

Then, data reconciliation module 108d extracts (step 604) transformed source data from target databases 110a-110n that corresponds to the extracted source data. In some embodiments, module 108d requests a point-in-time export from target databases 110a-110n of a portion of transformed source data that aligns with the same point in time as the extracted source data. Module 108d can store the extracted transformed source data from target databases 110a-110n in the data storage container where the extracted source data from source databases 103a-103n is located.

Data reconciliation module 108d compares (step 606) the extracted source data to the extracted transformed source data in the data storage container to identify one or more discrepancies. In some embodiments, module 108d is configured to determine that one or more data elements in the source data are missing in the transformed source data. For example, during a data replication process, data replication module 108b may have been unable to transform and store a particular source data record in the target database 110a-110n. Data reconciliation module 108d can detect this discrepancy by determining that a source data record does not have a corresponding transformed source data record in target database 110a-110n. In some embodiments, module 108d is configured to determine that one or more data elements in the transformed source data are missing in the source data. For example, an error may have occurred during data replication that resulted in invalid or incorrect data being stored in target databases 110a-110n that does not have a corresponding record in source databases 103a-103n. In some embodiments, data reconciliation module 108d is configured to determine that one or more data elements in the source data do not match the corresponding data elements in the transformed source data. For example, data replication module 108b may have incorrectly transformed a data element (e.g., used the wrong data type, truncated the data element, etc.) when storing the data element in target databases 110a-110n. Data reconciliation module 108d can detect this discrepancy by comparing the data elements and identify a difference between them.

Upon detecting one or more discrepancies, data reconciliation module 108d can perform several different tasks—including notifying relevant technical personnel and executing data correction processes to self-heal the target databases 110a-110n. In some embodiments, module 108d can transmit a notification message to a remote computing system (e.g., client computing device 102) upon identifying one or more discrepancies. The notification message can include information about the discrepancies, such as table names, discrepancy counts, discrepancy types, erroneous data elements, and so forth. In some embodiments, the discrepancy information can be stored in operations database 114a.

In some embodiments, module 108d can update one or more data elements in the source data to correct the one or more discrepancies. For example, module 108d can generate one or more scripts or load files that contain operations for correcting the discrepancies in the source databases 103a-103n. The operations can comprise insert, update, and/or delete commands that are executed against source databases 103a-103n to synchronize the source data to the transformed source data analyzed by module 108d.

Similarly, in some embodiments, module 108d can update one or more data elements in the transformed source data to correct the one or more discrepancies. For example, module