System and method to improve data synchronization and integration of heterogeneous databases distributed across enterprise and cloud using bi-directional transactional bus of asynchronous change data system

A method of snapshot materialization and application consistency, comprising:

• running a change capture system to capture all changes by first collecting a change capture data before an initial load is started,
• wherein the change capture data concerns modifications including an insert, a delete, and an update in a source system in an order of its occurrence,
• wherein a logical clock value determines the order in which the changes have occurred,
• wherein the changes are at least one of a transactional and a non-transactional and
• wherein the transaction boundaries are preserved as part of the change capture data;
• running a initial bulk load of all data in the source system while change capture is progressing;
• applying all change transactions to a particular transaction id when the initial load is completed;
• removing a reappearance of a record using keys that handle de-duplication of entries;
• and
• deeming a snapshot of a target system as consistent if the state of the whole system is identical to a source database at a certain transaction id,
• wherein a historic consistent snapshot with past data is preserved for at east one of a historic data analysis, an auditing, and a backup,
• wherein the key is at least one of a primary key and a composite key,
• wherein the source system is at least one of a OLTP RDBMS, a NoSQL database, an API system, and a message bus,
• wherein the source system to run on at least one of a bare metal hardware, a VM, a private, and a public cloud,
• wherein the target system is at least one of a OLTP RDBMS, a OLAP Data Warehouse, a Data Lake, and a NoSQL database
• wherein the target system to run on at least one of a bare metal hardware, a VM, a private or public cloud,
• wherein the target system to utilize at least one of a row storage, a columnar storage, an in-place update storage, and an append-only storage,
• wherein the target system to support a transactional update,
• wherein the target system is at least one of an identical and a different type than the source system,
• wherein a hierarchical declarative replication policy is applied to the change capture data and initial load data to filter, mask and modify the source data to be used among others for synchronizing a subset of the source data, remove and mask sensitive and personal data, transform the source data format and types to match the target system,
• wherein a target database is at least one of a RDBMS in-place update and HDFS append-only database, and
• wherein a replicated table in the target database is presented as at least one of an object materialized view, database view, and a database materialized view.