Sharing materialized views in multiple tenant database systems

A system comprising:

• one or more processors of a machine;
• and
• a memory storing instructions that, when executed by the one or more processors, cause the machine to perform operations comprising:
• storing a source table associated with a provider account, the source table including at least one micro-partition and stored across one or more of a plurality of shared storage devices in a multiple tenant database system, and wherein the at least one micro-partition is an immutable storage object;
• generating, by one or more execution nodes allocated to the provider account, a materialized view from underlying data in the source table;
• storing the materialized view in cache storage allocated to the provider account separate from the plurality of shared storage devices;
• providing access to the materialized view to a receiver account;
• updating, by the provider account, the source table including deleting at least one micro-partition based on execution of a transaction and inserting at least one new micro-partition;
• receiving, from the receiver account, a query directed at the materialized view;
• identifying modifications to the source table not reflected in the materialized view by executing a merge command between the source table and the materialized view;
• scanning the source table to detect the insertion of the at least one new micro-partition;
• scanning the materialized view to detect the deletion of the at least one micro-partition;
• updating the materialized view based on detecting the at least one new micro-partition and detecting the deletion of the at least one micro-partition;
• executing the query based on the updated materialized view;
• and
• providing access to the updated materialized view to the receiver account for query processing and restricting the receiver account from accessing the update to the underlying data in the source table.

A non-transitory computer readable storage media storing instructions that, when executed by one or more processors, cause the one or more processors to:

• storing a source table associated with a provider account, the source table including at least one micro-partition and stored across one or more of a plurality of shared storage devices in a multiple tenant database system, and wherein the at least one micro-partition is an immutable storage object;
• generating, by one or more execution nodes allocated to the provider account, a materialized view from underlying data in the source table;
• storing the materialized view in cache storage allocated to the provider account separate from the plurality of shared storage devices;
• providing access to the materialized view to a receiver account;
• updating, by the provider account, the source table including deleting at least one micro-partition based on execution of a transaction and inserting at least one new micro-partition;
• receiving, from the receiver account, a query directed at the materialized view;
• identifying modifications to the source table not reflected in the materialized view by executing a merge command between the source table and the materialized view;
• scanning the source table to detect the insertion of the at least one new micro-partition;
• scanning the materialized view to detect the deletion of the at least one micro-partition;
• updating the materialized view based on detecting the at least one new micro-partition and detecting the deletion of the at least one micro-partition;
• executing the query based on the updated materialized view;
• and
• providing access to the updated materialized view to the receiver account for query processing and restricting the receiver account from accessing the update to the underlying data in the source table.