Systems and methods for generating and synchronizing materialized views

1 . A multi-tenant system, comprising:

one or more hardware processors; and

memory storing computer instructions, the computer instructions when executed by the one or more hardware processors cause the one or more hardware processors to perform operations comprising:

receiving a request to perform an incremental update of a materialized view generated from

detecting, based on watermarks of the constituent records, one or more changes that were committed in the constituent records relative to a time that the materialized view was last updated;

synchronizing the materialized view with the one or more detected changes including executing a first query to obtain initial incremental updates based on the watermarks of the constituent records;

determining that an uncaptured long-running transaction was initiated before the latest incremental update of the initial incremental updates;

in response to determining that an uncaptured long-running transaction was initiated before the latest incremental update of the initial incremental updates, executing a subsequent query to capture additional incremental updates committed by the long-running transaction after the request to perform the incremental update was received;

updating the materialized view within a data platform of the multi-tenant system using the initial incremental updates and the additional incremental updates; and

transmitting the synchronized materialized view to a target database accessible by tenants.

2 . The multi-tenant system of claim 1 , wherein at least a portion of the constituent records of the subset of constituent records is stored in a tabular format, and the one or more operations comprise a join operation.

3 . The multi-tenant system of claim 1 , wherein the detecting of one or more changes comprises detecting an addition, a deletion, or a modification to existing data in the subset of the constituent records.

4 . The multi-tenant system of claim 3 , wherein the detecting of one or more changes comprises detecting a deletion; and the detecting of the deletion comprises detecting an indication that a row or a portion of a row is scheduled to be deleted.

5 . The multi-tenant system of claim 1 , wherein:

the detecting of one or more changes comprises detecting an update to a watermark corresponding to one or more rows of the subset of the constituent records; and

the update to the watermark comprises a monotonic increase in a value of the watermark beyond a highest value of a previous watermark in a most recent generation or synchronization of the materialized view.

6 . The multi-tenant system of claim 1 , wherein:

the detecting of one or more changes comprises detecting an update to a watermark corresponding to one or more rows of the subset of the constituent records;

the watermark comprises a timestamp; and

the update to the watermark comprises an update to the timestamp, the updated timestamp indicating a time later than a latest time of a previous watermark in a most recent generation or synchronization of the materialized view.

7 . The multi-tenant system of claim 1 , wherein the detecting one or more changes occurring in the subset of the constituent records comprises detecting a first change in a first record, the first record being changed by a first transaction having a first starting time and being confirmed at a first ending time; and wherein the computer instructions are further configured to perform:

detecting an unconfirmed second change in a second record, the second record being changed by a second transaction having a second starting time, the second starting time being before the first starting time;

in response to detecting the unconfirmed second change, refraining from updating a watermark corresponding to the first record; and

refraining from synchronizing the first change with the materialized view until the unconfirmed second change has been confirmed.

8 . The multi-tenant system of claim 1 , wherein updating the materialized view using the initial incremental updates and the additional incremental updates comprises removing duplicates from the additional incremental updates that also occur in the initial incremental updates.

9 . The multi-tenant system of claim 1 , wherein the parsing of the query comprises deciphering a primary key of a first table and a foreign key of a second table to be joined.

10 . The multi-tenant system of claim 1 , wherein the data platform:

compares a most updated iteration of the materialized view within the target database with a most updated iteration of the materialized view within the data platform;

determines whether the most updated iteration of the materialized view within the target database matches the most updated iteration of the materialized view within the data platform;

in response to determining that the most updated iteration of the materialized view fails to match the most updated iteration of the materialized view within the data platform, transmits the most updated iteration of the materialized view within the data platform to the target database.

11 . The multi-tenant system of claim 1 , wherein the data platform comprises a row-major relational database to store the synchronized materialized view.

12 . A method implemented by a multi-tenant system, the method comprising:

receiving a request to perform an incremental update of a materialized view generated from constituent records stored within a source database;

detecting, based on watermarks of the constituent records, one or more changes that were committed in the constituent records relative to a time that the materialized view was last updated;

synchronizing the materialized view with the one or more detected changes including executing a first query to obtain initial incremental updates based on the watermarks of the constituent records;

determining that an uncaptured long-running transaction was initiated before the latest incremental update of the initial incremental updates;

in response to determining that an uncaptured long-running transaction was initiated before the latest incremental update of the initial incremental updates, executing a subsequent query to capture additional incremental updates committed by the long-running transaction after the request to perform the incremental update was received;

updating the materialized view within a data platform of the multi-tenant system using the initial incremental updates and the additional incremental updates; and

transmitting the synchronized materialized view to a target database accessible by tenants.

13 . The method of claim 12 , wherein at least a portion of the constituent records of the subset of constituent records is stored in a tabular format, and the one or more operations comprise a join operation.

14 . The method of claim 12 , wherein the detecting of one or more changes comprises detecting an addition, a deletion, or a modification to existing data in the subset of the constituent records.

15 . The method of claim 12 , wherein the detecting of one or more changes comprises detecting a deletion; and the detecting of the deletion comprises detecting an indication that a row or a portion of a row is scheduled to be deleted.

16 . The method of claim 12 , wherein:

the detecting of one or more changes comprises detecting an update to a watermark corresponding to one or more rows of the subset of the constituent records; and

the update to the watermark comprises a monotonic increase in a value of the watermark beyond a highest value of a previous watermark in a most recent generation or synchronization of the materialized view.

17 . The method of claim 12 , wherein:

the detecting of one or more changes comprises detecting an update to a watermark corresponding to one or more rows of the subset of the constituent records;

the watermark comprises a timestamp; and

the update to the watermark comprises an update to the timestamp, the updated timestamp indicating a time later than a latest time of a previous watermark in a most recent generation or synchronization of the materialized view.

18 . The method of claim 12 , wherein the detecting one or more changes occurring in the subset of the constituent records comprises detecting a first change in a first record, the first record being changed by a first transaction having a first starting time and being confirmed at a first ending time; and wherein the computer instructions are further configured to perform:

detecting an unconfirmed second change in a second record, the second record being changed by a second transaction having a second starting time, the second starting time being before the first starting time;

in response to detecting the unconfirmed second change, refraining from updating a watermark corresponding to the first record; and

refraining from synchronizing the first change with the materialized view until the unconfirmed second change has been confirmed.

19 . The method of claim 12 , wherein updating the materialized view using the initial incremental updates and the additional incremental updates comprises removing duplicates from the additional incremental updates that also occur in the initial incremental updates.

20 . One or more non-transitory computer storage media encoded with computer program instructions that when executed by one or more computers cause the one or more computers to perform operations comprising:

receiving a request to perform an incremental update of a materialized view generated from constituent records stored in a source database;

detecting, based on watermarks of the constituent records, one or more changes that were committed in the constituent records relative to a time that the materialized view was last updated;

synchronizing the materialized view with the one or more detected changes including executing a first query to obtain initial incremental updates based on the watermarks of the constituent records;

determining that an uncaptured long-running transaction was initiated before the latest incremental update of the initial incremental updates;

in response to determining that an uncaptured long-running transaction was initiated before the latest incremental update of the initial incremental updates, executing a subsequent query to capture additional incremental updates committed by the long-running transaction after the request to perform the incremental update was received;

updating the materialized view within a data platform of the multi-tenant system using the initial incremental updates and the additional incremental updates; and

transmitting the synchronized materialized view to a target database accessible by tenants.