# US 12,216,654 B2 · Materialized table refresh using multiple processing pipelines > Agents: this data is available as JSON from the patentgrep API and MCP server — see https://patentgrep.com/llms.txt - Number: US 12,216,654 B2 - Document ID: US12216654B2 - Status: Granted - Kind: B2 - Application number: 18/362,898 - Filed: 2023-07-31 - Published: 2023-12-21 - Granted: 2025-02-04 - Earliest claimed: 2022-06-10 - Assignees: Snowflake Inc. - Applicants: Snowflake Inc. - Inventors: Tyler Arthur Akidau; Fabian Hueske; Tyler Jones; Daniel Mills; Leon Papke; Prasanna Rajaperumal; Daniel E. Sotolongo - Web page: https://patentgrep.com/patent/US12216654B2 ## CPC - G06F 7/14: Merging, i.e. combining at least two sets of record carriers each arranged in the same ordered sequence to produce a single set having the same ordered sequence - G06F 16/24539: {using cached or materialised query results} - G06F 16/2453: Query optimisation - G06F 16/24542: {Plan optimisation} - G06F: ELECTRIC DIGITAL DATA PROCESSING (computer systems based on specific computational models G06N) ## Abstract A system for a materialized table (MT) refresh using multiple processing pipelines includes at least one hardware processor coupled to memory storing instructions. The instructions cause the at least one hardware processor to perform operations including determining dependencies among a plurality of intermediate MTs generated from a source MT. The source MT uses a table definition with a query on one or more base tables and a lag duration value. A graph snapshot of dependencies among the plurality of intermediate MTs is generated. Processing pipelines are configured. Each of the processing pipelines corresponds to a subset of the plurality of intermediate MTs indicated by the graph snapshot. Responsive to detecting an instruction for a refresh operation on the source MT, refreshes on corresponding intermediate MTs of the plurality of intermediate MTs in each processing pipeline of the processing pipelines are performed to complete the refresh operation on the source MT. ## Claims ### Claim 1 A system comprising: at least one hardware processor; and at least one memory storing instructions that cause the at least one hardware processor to perform operations comprising: determining dependencies among a plurality of intermediate materialized tables (MTs) generated from a source materialized table (MT), the source MT using a table definition with a query on one or more base tables and a lag duration value; generating a graph snapshot of dependencies among the plurality of intermediate MTs based on an application of a graph rendering process to a log of the dependencies; configuring processing pipelines, each processing pipeline of the processing pipelines corresponding to a subset of the plurality of intermediate MTs indicated by the graph snapshot; and responsive to detecting an instruction for a refresh operation on the source MT, performing refreshes on corresponding intermediate MTs of the plurality of intermediate MTs in each processing pipeline of the processing pipelines to complete the refresh operation on the source MT. ### Claim 2 (depends on 1) The system of claim 1, wherein the operations comprise: parsing the table definition to determine the plurality of intermediate MTs. ### Claim 3 (depends on 1) The system of claim 1, wherein the operations further comprise: translating the query of the source MT into a plurality of data processing operations to obtain a query plan of the query. ### Claim 4 (depends on 3) The system of claim 3, wherein the operations further comprise: associating the plurality of data processing operations to a plurality of fragments, each of the plurality of fragments indicative of an intermediate state of at least one of the plurality of data processing operations. ### Claim 5 (depends on 4) The system of claim 4, wherein the operations further comprise: generating the plurality of intermediate MTs, wherein each of the plurality of intermediate MTs is based on a corresponding fragment of the plurality of fragments. ### Claim 6 (depends on 1) The system of claim 1, wherein the operations further comprise: generating the log as a data definition language (DDL) log of the dependencies. ### Claim 7 (depends on 6) The system of claim 6, wherein the operations further comprise: applying the graph rendering process to the DDL log of dependencies to generate the graph snapshot of the dependencies among the plurality of intermediate MTs, the graph snapshot comprising a plurality of nodes corresponding to the plurality of intermediate MTs. ### Claim 8 (depends on 7) The system of claim 7, wherein the operations further comprise: separating the plurality of nodes into subsets of nodes based on association of the nodes to a plurality of data processing accounts of database system users. ### Claim 9 (depends on 8) The system of claim 8, wherein the operations further comprise: selecting a first subset of nodes from the subsets of nodes based on association of the first subset of nodes to a first data processing account of the plurality of data processing accounts; and selecting a second subset of nodes from the subsets of nodes based on association of the second subset of nodes to a second data processing account of the plurality of data processing accounts. ### Claim 10 (depends on 9) The system of claim 9, wherein the operations further comprise: configuring a first processing pipeline of the processing pipelines at a first computing node, the first processing pipeline including the first subset of nodes associated with the first data processing account; configuring a second processing pipeline of the processing pipelines at a second computing node, the second processing pipeline including the second subset of nodes associated with the second data processing account; and sharding the refreshes on the corresponding intermediate materialized tables in the first processing pipeline and the second processing pipeline to complete the refresh operation on the materialized table. ### Claim 11 A method comprising: determining, by at least one hardware processor, dependencies among a plurality of intermediate materialized tables (MTs) generated from a source materialized table (MT), the source MT using a table definition with a query on one or more base tables and a lag duration value; generating a graph snapshot of dependencies among the plurality of intermediate MTs based on an application of a graph rendering process to a log of the dependencies; configuring processing pipelines, each processing pipeline of the processing pipelines corresponding to a subset of the plurality of intermediate MTs indicated by the graph snapshot; and responsive to detecting an instruction for a refresh operation on the source MT, performing refreshes on corresponding intermediate MTs of the plurality of intermediate MTs in each processing pipeline of the processing pipelines to complete the refresh operation on the source MT. ### Claim 12 (depends on 11) The method of claim 11, further comprising: parsing the table definition to determine the plurality of intermediate MTs. ### Claim 13 (depends on 11) The method of claim 11, further comprising: translating the query of the source MT into a plurality of data processing operations to obtain a query plan of the query. ### Claim 14 (depends on 13) The method of claim 13, further comprising: associating the plurality of data processing operations to a plurality of fragments, each of the plurality of fragments indicative of an intermediate state of at least one of the plurality of data processing operations. ### Claim 15 (depends on 14) The method of claim 14, further comprising: generating the plurality of intermediate MTs, wherein each of the plurality of intermediate MTs is based on a corresponding fragment of the plurality of fragments. ### Claim 16 (depends on 11) The method of claim 11, further comprising: generating the log as a data definition language (DDL) log of the dependencies. ### Claim 17 (depends on 16) The method of claim 16, further comprising: applying the graph rendering process to the DDL log of dependencies to generate the graph snapshot of the dependencies among the plurality of intermediate MTs, the graph snapshot comprising a plurality of nodes corresponding to the plurality of intermediate MTs. ### Claim 18 (depends on 17) The method of claim 17, further comprising: separating the plurality of nodes into subsets of nodes based on association of the nodes to a plurality of data processing accounts of database system users. ### Claim 19 (depends on 18) The method of claim 18, further comprising: selecting a first subset of nodes from the subsets of nodes based on association of the first subset of nodes to a first data processing account of the plurality of data processing accounts; and selecting a second subset of nodes from the subsets of nodes based on association of the second subset of nodes to a second data processing account of the plurality of data processing accounts. ### Claim 20 (depends on 19) The method of claim 19, further comprising: configuring a first processing pipeline of the processing pipelines at a first computing node, the first processing pipeline including the first subset of nodes associated with the first data processing account; configuring a second processing pipeline of the processing pipelines at a second computing node, the second processing pipeline including the second subset of nodes associated with the second data processing account; and sharding the refreshes on the corresponding intermediate materialized tables in the first processing pipeline and the second processing pipeline to complete the refresh operation on the materialized table. ### Claim 21 A computer-storage medium comprising instructions that, when executed by one or more processors of a machine, configure the machine to perform operations comprising: determining, by at least one hardware processor, dependencies among a plurality of intermediate materialized tables (MTs) generated from a source materialized table (MT), the source MT using a table definition with a query on one or more base tables and a lag duration value; generating a graph snapshot of dependencies among the plurality of intermediate MTs based on an application of a graph rendering process to a log of the dependencies; configuring processing pipelines, each processing pipeline of the processing pipelines corresponding to a subset of the plurality of intermediate MTs indicated by the graph snapshot; and responsive to detecting an instruction for a refresh operation on the source MT, performing refreshes on corresponding intermediate MTs of the plurality of intermediate MTs in each processing pipeline of the processing pipelines to complete the refresh operation on the source MT. ### Claim 22 (depends on 21) The computer-storage medium of claim 21, the operations further comprising: parsing the table definition to determine the plurality of intermediate MTs. ### Claim 23 (depends on 21) The computer-storage medium of claim 21, the operations further comprising: translating the query of the source MT into a plurality of data processing operations to obtain a query plan of the query. ### Claim 24 (depends on 23) The computer-storage medium of claim 23, the operations further comprising: associating the plurality of data processing operations to a plurality of fragments, each of the plurality of fragments indicative of an intermediate state of at least one of the plurality of data processing operations. ### Claim 25 (depends on 24) The computer-storage medium of claim 24, the operations further comprising: generating the plurality of intermediate MTs, wherein each of the plurality of intermediate MTs is based on a corresponding fragment of the plurality of fragments. ### Claim 26 (depends on 21) The computer-storage medium of claim 21, the operations further comprising: generating the log as a data definition language (DDL) log of the dependencies. ### Claim 27 (depends on 26) The computer-storage medium of claim 26, the operations further comprising: applying the graph rendering process to the DDL log of dependencies to generate the graph snapshot of the dependencies among the plurality of intermediate MTs, the graph snapshot comprising a plurality of nodes corresponding to the plurality of intermediate MTs. ### Claim 28 (depends on 27) The computer-storage medium of claim 27, the operations further comprising: separating the plurality of nodes into subsets of nodes based on association of the nodes to a plurality of data processing accounts of database system users. ### Claim 29 (depends on 28) The computer-storage medium of claim 28, the operations further comprising: selecting a first subset of nodes from the subsets of nodes based on association of the first subset of nodes to a first data processing account of the plurality of data processing accounts; and selecting a second subset of nodes from the subsets of nodes based on association of the second subset of nodes to a second data processing account of the plurality of data processing accounts. ### Claim 30 (depends on 29) The computer-storage medium of claim 29, the operations further comprising: configuring a first processing pipeline of the processing pipelines at a first computing node, the first processing pipeline including the first subset of nodes associated with the first data processing account; configuring a second processing pipeline of the processing pipelines at a second computing node, the second processing pipeline including the second subset of nodes associated with the second data processing account; and sharding the refreshes on the corresponding intermediate materialized tables in the first processing pipeline and the second processing pipeline to complete the refresh operation on the materialized table.