Snowflake Inc.

Leon Papke

Tyler Jones

Tyler Arthur Akidau

Daniel E Sotolongo

Daniel Mills

Prasanna Rajaperumal

Fabian Hueske

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.

Materialized table refresh using multiple processing pipelines

A method includes retrieving a plurality of materialized tables (MTs). Each of the plurality of MTs includes a lag duration and refers to a corresponding base table of a plurality of base tables. The lag duration indicates a maximum time period that a result of a prior refresh of a query on the corresponding base table can lag behind a current time instance. A plurality of time instances for the MT is determined based on the lag duration and a number of prior refreshes of the corresponding base table. A plurality of aligned time instances for the plurality of MTs is determined based on the plurality of time instances for each of the plurality of MTs. Refresh operations are scheduled for the plurality of MTs at one or more of the plurality of aligned time instances that are within the maximum time period.

Query execution using materialized tables

Provided herein are systems and methods for a database object (e.g., materialized table) configuration including using the database object for incremental maintenance of query results. For example, a method includes creating a materialized table using a table definition. The table definition includes a query on one or more base tables and a lag duration value. The lag duration value indicates a maximum time period that a result of a prior refresh of the query can lag behind a current time instance. A delta set for the one or more base tables is determined. The delta set includes at least one change applied to the one or more base tables since the prior refresh of the query. The delta set is merged with the materialized table to perform an incremental update of the materialized table.

Incremental maintenance of query results

Provided herein are systems and methods for a database object (e.g., materialized table) configuration including scheduling refreshes of the materialized table. For example, a method includes determining a dependency graph for a first MT. The dependency graph comprises a second MT from which the first MT depends. The first MT includes a query on one or more base tables and a lag duration value. The lag duration value indicates a maximum time period that a result of a prior refresh of the query can lag behind a current time instance. A tick period is selected for a set of ticks based on the lag duration value. The set of ticks corresponds to a set of aligned time instances. Refresh operations are scheduled for the first and second MTs at corresponding time instances from the set of aligned time instances. The corresponding time instances are separated by the tick period.

Growing community of inventors

Berlin, Germany

Leon Papke