# US 11,030,186 B2 · Incremental refresh of a materialized view > Agents: this data is available as JSON from the patentgrep API and MCP server — see https://patentgrep.com/llms.txt - Number: US 11,030,186 B2 - Document ID: US11030186B2 - Status: Granted - Kind: B2 - Application number: 16/662,645 - Filed: 2019-10-24 - Published: 2020-04-30 - Granted: 2021-06-08 - Earliest claimed: 2018-10-26 - Assignees: Snowflake lnc. - Applicants: Snowflake Inc. - Inventors: Thierry Cruanes; Benoit Dageville; Prasanna Rajaperumal; Jiaqi Yan - Web page: https://patentgrep.com/patent/US11030186B2 ## CPC - G06F 16/2365: {Ensuring data consistency and integrity} - G06F 16/2282: {Tablespace storage structures; Management thereof} - G06F 16/2393: {Updating materialised views} - G06F 16/24: Querying - G06F 16/278: {Data partitioning, e.g. horizontal or vertical partitioning} - G06F: ELECTRIC DIGITAL DATA PROCESSING (computer systems based on specific computational models G06N) ## Abstract Systems, methods, and devices for incrementally refreshing a materialized view are disclosed. A method includes generating a materialized view based on a source table. The method includes merging the source table and the materialized view to generate a merged table to identify whether an update has been executed on the source table that is not reflected in the materialized view. The method includes, in response to detecting an update made to the source table that is not reflected in the materialized view, applying the update to the materialized view. ## Claims ### Claim 1 A system comprising: a compute service manager for managing internal operations of a cloud-based database platform; a plurality of shared storage devices collectively storing database data, wherein the plurality of shared storage devices is independent of the compute service manager; and an execution platform comprising a plurality of execution nodes, wherein the execution platform independent is independent of the plurality of shared storage devices and the compute service manager; wherein the compute service manager is configured to: generate a materialized view based on a source table, the materialized view being a database object that includes results of a persistent query, the source table including a set of micro-partitions, and the materialized view combining information in the set of micro-partitions in the source table into a single micro-partition in the materialized view; merge the source table and the materialized view to generate a merged table to identify whether an update has been executed on the source table that is not reflected in the materialized view, the update including a modification to one or more of micro-partitions in the set of micro-partitions in the source table; in response to detecting an update made to the source table that is not reflected in the materialized view, assign tasks to one or more execution nodes of the execution platform to apply the update to the materialized view, the applying of the update including updating the single micro-partition in the materialized view corresponding to the set of micro-partitions in the source table to generate an updated materialized view; and generate a response to a query using data directly from the updated materialized view without accessing the source table. ### Claim 2 (depends on 1) The system of claim 1, wherein the update comprises one or more of: one or more rows inserted into the source table; one or more rows deleted from the source table; or one or more rows updated in the source table. ### Claim 3 (depends on 1) The system of claim 1, wherein the compute service manager is configured to assign tasks for applying the update to the materialized view by: in response to determining a new row has been inserted into the source table, assign a task to refresh the materialized that comprises inserting the new row into the materialized view; and in response to determining a row has been deleted from the source table, assign a task to compact the materialized view that comprises deleting the row from the materialized view. ### Claim 4 (depends on 3) The system of claim 3, wherein the compute service manager is configured to prioritize inserting the new row into the materialized view over deleting the row from the materialized view. ### Claim 5 (depends on 3) The system of claim 3, wherein the compute service manager is configured to assign a task to compact the materialized view only after a plurality of rows have been deleted from the source table such that a quantity of the plurality of rows exceeds a predetermined threshold number of deleted rows. ### Claim 6 (depends on 1) The system of claim 1, wherein the source table includes at least another set of micro-partitions, and wherein the update comprises one or more of a new micro-partition being added to the source table or one or more of micro-partitions being deleted from the source table. ### Claim 7 (depends on 1) The system of claim 1, wherein the compute service manager is further configured to scan the merged table to detect one or more modified micro-partitions in the source table by: identifying whether a new micro-partition has been inserted into the source table that is not present in the materialized view; or identifying an absence of a deleted micro-partition removed from the source table that is still present in the materialized view. ### Claim 8 (depends on 1) The system of claim 1, wherein the compute service manager is further configured to redirect queries away from the materialized view and to the source table while the update is being applied to the materialized view. ### Claim 9 (depends on 1) The system of claim 1, wherein the compute service manager is further configured to generate a source table log comprising: a log of one or more rows inserted into the source table since a last refresh of the materialized view; and a log of one or more rows deleted from the source table since a last compaction of the materialized view. ### Claim 10 (depends on 1) The system of claim 1, wherein the compute service manager is further configured to generate cost statistics for the materialized view comprising one or more of: a number of micro-partitions on the source table that have been materialized; a total number of partitions on the source table; a total number of partitions of the materialized view; a number of times a query is accessed on the materialized view; or a number of times a query is accessed on the source table. ### Claim 11 A method comprising: generating a materialized view based on a source table, the materialized view being a database object that includes results of a persistent query, the source table including a set of micro-partitions, and the materialized view combining information in the set of micro-partitions in the source table into a single micro-partition in the materialized view; merging the source table and the materialized view to generate a merged table to identify whether an update has been executed on the source table that is not reflected in the materialized view, the update including a modification to one or more of micro-partitions in the set of micro-partitions in the source table; and in response to detecting an update made to the source table that is not reflected in the materialized view, applying the update to the materialized view, including updating the single micro-partition in the materialized view corresponding to the set of micro-partitions in the source table to generate an updated materialized view; and generating a response to a query using data directly from the updated materialized view without accessing the source table. ### Claim 12 (depends on 11) The method of claim 11, wherein the update comprises one or more of: one or more rows inserted into the source table; one or more rows deleted from the source table; or one or more rows updated in the source table. ### Claim 13 (depends on 11) The method of claim 11, wherein applying the update to the materialized view comprises: in response to determining a new row has been inserted into the source table, refreshing the materialized view by inserting the new row into the materialized view; and in response to determining a row has been deleted from the source table, compacting the materialized view by deleting the row from the materialized view. ### Claim 14 (depends on 13) The method of claim 13, wherein refreshing the materialized view by inserting the new row into the materialized view is prioritized over compacting the materialized view by deleting the row from the materialized view. ### Claim 15 (depends on 13) The method of claim 13, wherein compacting the materialized view occurs only after detecting a plurality of rows deleted from the source table such that a quantity of the plurality of rows exceeds a predetermined threshold number of deleted rows. ### Claim 16 (depends on 11) The method of claim 11, wherein the source table is includes at least another set of micro-partitions, and wherein the update comprises one or more of a new micro-partition being added to the source table or one or more of micro-partitions being deleted from the source table. ### Claim 17 (depends on 11) The method of claim 11, further comprising scanning the merged table to detect one or more modified micro-partitions in the source table by: identifying whether a new micro-partition has been inserted into the source table that is not present in the materialized view; or identifying an absence of a deleted micro-partition removed from the source table that is still present in the materialized view. ### Claim 18 (depends on 11) The method of claim 11, further comprising redirecting queries away from the materialized view and to the source table while the update is being applied to the materialized view. ### Claim 19 (depends on 11) The method of claim 11, further comprising generating a source table log comprising: a log of one or more rows inserted into the source table since a last refresh of the materialized view; and a log of one or more rows deleted from the source table since a last compaction of the materialized view. ### Claim 20 (depends on 11) The method of claim 11, further comprising generating cost statistics for the materialized view comprising one or more of: a number of micro-partitions on the source table that have been materialized; a total number of partitions on the source table; a total number of partitions of the materialized view; a number of times a query is accessed on the materialized view; or a number of times a query is accessed on the source table. ### Claim 21 A non-transitory machine-storage medium embodying instructions that, when executed by a machine, cause the machine to perform operations comprising: generating a materialized view based on a source table, the materialized view being a database object that includes results of a persistent query, the source table including a set of micro-partitions, and the materialized view combining information in the set of micro-partitions in the source table into a single micro-partition in the materialized view; merging the source table and the materialized view to generate a merged table to identify whether an update has been executed on the source table that is not reflected in the materialized view, the update including a modification to one or more of micro-partitions in the set of micro-partitions in the source table; and in response to detecting an update made to the source table that is not reflected in the materialized view, applying the update to the materialized view, including updating the single micro-partition in the materialized view corresponding to the set of micro-partitions in the source table to generate an updated materialized view; and generating a response to a query using data directly from the updated materialized view without accessing the source table. ### Claim 22 (depends on 21) The machine-storage medium of claim 21, wherein the update comprises one or more of: one or more rows inserted into the source table; one or more rows deleted from the source table; or one or more rows updated in the source table. ### Claim 23 (depends on 21) The machine-storage medium of claim 21, wherein applying the update to the materialized view comprises: in response to determining a new row has been inserted into the source table, refreshing the materialized view by inserting the new row into the materialized view; and in response to determining a row has been deleted from the source table, compacting the materialized view by deleting the row from the materialized view. ### Claim 24 (depends on 23) The machine-storage medium of claim 23, further comprising scanning the merged table to detect one or more modified micro-partitions in the source table by: identifying whether a new micro-partition has been inserted into the source table that is not present in the materialized view; or identifying an absence of a deleted micro-partition removed from the source table that is still present in the materialized view. ### Claim 25 (depends on 23) The machine-storage medium of claim 23, further comprising inserting the new row into the materialized view over deleting the row from the materialized view. ### Claim 26 (depends on 23) The machine-storage medium of claim 23, further comprising assigning a task to compact the materialized view only after a plurality of rows have been deleted from the source table such that a quantity of the plurality of rows exceeds a predetermined threshold number of deleted rows. ### Claim 27 (depends on 21) The machine-storage medium of claim 21, further comprising generating a source table log comprising: a log of one or more rows inserted into the source table since a last refresh of the materialized view; and a log of one or more rows deleted from the source table since a last compaction of the materialized view. ### Claim 28 (depends on 21) The machine-storage medium of claim 21, wherein the source table includes at least another set of micro-partitions, and wherein the update comprises one or more of a new micro-partition being added to the source table or one or more of micro-partitions being deleted from the source table. ### Claim 29 (depends on 21) The machine-storage medium of claim 21, further comprising redirecting queries away from the materialized view and to the source table while the update is being applied to the materialized view. ### Claim 30 (depends on 21) The machine-storage medium of claim 21, further comprising generating cost statistics for the materialized view comprising one or more of: a number of micro-partitions on the source table that have been materialized; a total number of partitions on the source table; a total number of partitions of the materialized view; a number of times a query is accessed on the materialized view; or a number of times a query is accessed on the source table.