Task Scheduling in Database Systems

Abstract

Disclosed are systems, methods, and apparatus for performing tasks on database data in response to a triggering event. The method includes performing a transaction on a table including the database data, wherein performing the transaction includes generating a new table version. The method includes: in response to the transaction being fully executed, generating a change tracking entry, the change tracking entry including an indication of one or more modifications made to the table by the transaction; and storing the change tracking entry in a change tracking stream. The method includes performing the task on the new table version in response to the triggering event.

Description

[0001] CROSS-REFERENCE TO RELATED APPLICATIONS

[0002] This application claims priority to U.S. patent application serial number 16 / 203,322, filed on November 28, 2018, the entire contents of which are incorporated herein by reference. Technical Field

[0003] The present disclosure relates to systems, methods, and apparatus for databases, and more particularly to scheduling tasks to be performed on database data. Background Art

[0004] Databases are widely used for data storage and access in computing applications. A database may include one or more tables that include or reference data that can be read, modified, or deleted using queries. A database may store small to very large data sets anywhere within one or more tables. The data may be accessed by various users in an organization, or may even be used to serve public users, for example, via a website or application program interface (API). Both computing and storage resources and their underlying architecture may play an important role in achieving desired database performance.

[0005] Tasks or commands may be performed on database data to manipulate or change the data. Such tasks may be requested by a client account, and such tasks may manipulate the database data to make it more useful to the client account. In some implementations, it may be beneficial to schedule the automatic execution of certain tasks to simplify the database system. BRIEF DESCRIPTION OF THE DRAWINGS

[0006] Non-limiting and non-exhaustive embodiments of the present disclosure are described with reference to the following figures, wherein like reference numerals refer to like parts throughout the various figures unless otherwise specified.

[0007] Figure 1 is a schematic block diagram of a process flow for data ingestion according to one embodiment;

[0008] Figure 2 is a schematic block diagram of a table history and change tracking flow according to one embodiment;

[0009] Figure 3 is a block diagram illustrating modifications to a table version according to one embodiment;

[0010] Figure 4 is a block diagram of a query pipeline according to one embodiment;

[0011] Figure 5 is a block diagram of a change tracking flow according to one embodiment;

[0012] Figure 6 is a schematic diagram of a transaction timeline regarding database data according to one embodiment;

[0013] Figure 7 is a block diagram illustrating a delete command executed on a micro-partition of a database according to one embodiment;

[0014] Figure 8 is a block diagram illustrating an insert command executed on a micro-partition of a database according to one embodiment;

[0015] Fig. 9 is a block diagram illustrating update commands executed on micro-partitions of a database according to one embodiment;

[0016] Fig.10 is a block diagram illustrating a database system having a database service manager according to one embodiment;

[0017] Fig.11 is a block diagram illustrating components of a database service manager according to one embodiment;

[0018] Fig.12 is a block diagram illustrating components of a task execution manager according to one embodiment;

[0019] Fig.13 is a schematic flow chart illustrating a method for scheduling tasks on database data according to one embodiment;

[0020] Fig.14 is a schematic flow chart illustrating a method for scheduling tasks on database data according to one embodiment; and

[0021] Fig.15 is a block diagram depicting an example computing device consistent with at least one embodiment of the processes and systems disclosed herein. DETAILED DESCRIPTION

[0022] Systems, methods, and devices for scheduling tasks to be performed on database data are disclosed. Tasks such as structured query language (SQL) statements can be performed on database data to manipulate or change data. Tasks can include any functionality and can include user-defined logic received from a client account. In certain implementations, it may be beneficial to schedule automatic execution of tasks in response to one or more triggering events. In an embodiment, task execution can be triggered by the full execution of a transaction such as inserting, deleting, or updating database data.

[0023] Transactions such as INSERT, DELETE, MERGE, or UPDATE commands may be performed on a database table. It may be beneficial to track changes that have occurred on a database table and, in certain embodiments, trigger tasks to be performed on the database table in response to transactions that have been performed on the table. Change tracking has historically been expensive and requires a large amount of resources and storage capacity. As disclosed herein, a change tracking stream may be generated that indicates one or more versions of a database table and how the table has been modified over time. The change tracking stream provides a low-cost way to summarize all transactions that have been performed on a table over time and when those transactions occurred.

[0024] Tasks such as user defined logic or SQL statements may be executed on database data in response to a triggering event. In an embodiment, tasks are executed in response to a change tracking stream advancing after a transaction has been fully executed on the database data. By ensuring that a task is executed only once for a particular data set and also by ensuring that the task is executed for all newly ingested data, significant benefits may be provided over other task scheduling approaches known in the art. In an embodiment, this may be achieved by a change tracking system in the database that generates a change tracking stream. The change tracking stream participates in transactions similar to table data, such that if a transaction commits or otherwise remains unmodified, the change tracking stream will advance, and while the transaction commits or otherwise remains unmodified, the change tracking stream will advance.

[0025] A database table may store data in multiple micro-partitions, where a micro-partition is an immutable storage device. When a transaction is executed on such a table, all affected micro-partitions are recreated to generate new micro-partitions that reflect the modifications of the transaction. After the transaction is fully executed, any original micro-partitions that were recreated can then be removed from the database. After each transaction executed on the table, a new version of the table is generated. If the data in the table is subject to many changes such as insertions, deletions, updates, and / or merges, the table may be subject to many versions over a period of time. Each version of the table may include metadata indicating what transaction generated the table, when the transaction was ordered, when the transaction was fully executed, and how the transaction changed one or more rows in the table. The disclosed system, method, and device for low-cost table versioning can be used to provide an efficient way to trigger tasks to be performed on database data in response to modifications made to the data.

[0026] Change tracking information can be stored as metadata in the database. This metadata describes the data stored in the customer's database table, but is not actually the table data stored. The metadata can become very large, especially when there are large database tables for many customers. Current database systems have serious limitations in handling large amounts of metadata. Current database systems store metadata in mutable storage devices and services, including main memory, file systems, and key-value stores. These devices and services allow metadata to update data in place. If a data record changes, it can be updated with the new information and the old information is overwritten. This allows the database to easily maintain mutable metadata by updating the metadata in place.

[0027] However, these mutable storage devices and services have limitations. The limitations are at least two-fold. First, mutable storage devices such as main memory and file systems have strict limits on storage capacity. If the size of the metadata exceeds these limits, it is not possible to store more metadata there. Second, mutable storage services such as key-value stores do not perform well when reading large amounts of metadata. Reading data is performed using range scans, which take a long time to complete. In practice, in large-scale deployments, range scans may take several minutes or even close to an hour to complete.

[0028] These limitations make it impossible to store large amounts of metadata in existing mutable storage devices and services. The systems, methods, and devices disclosed herein provide improved metadata storage and management, including storing metadata in immutable (non-mutable) storage devices such as micro-partitions. As used herein, immutable or non-mutable storage devices include storage devices in which data cannot or is not allowed to be overwritten or updated in place. For example, changes to data located in a unit or region of a storage medium can be stored as a new file in a different, time-stamped unit or region of the storage medium. Mutable storage devices can include storage devices in which data is allowed to be overwritten or updated in place. For example, data in a given unit or region of a storage medium can be overwritten when there is a change to data related to that unit or region of the storage medium.

[0029] In one embodiment, the metadata is stored and maintained on a non-volatile storage service in the cloud. These storage services may include, for example, Amazon 、Microsoft Azure Blob and Google Cloud Many of these services do not allow data to be updated in place (i.e., are non-mutable or immutable). Data files can only be added or deleted, never updated. In one embodiment, storing and maintaining metadata on these services requires that for each change in metadata, a metadata file be added to the storage service. These metadata files can be periodically merged in the background into larger "compressed" or merged metadata files.

[0030] In an embodiment, all data in a table is automatically partitioned into immutable storage devices called micro-partitions. Micro-partitions can be viewed as batch processing units, where each micro-partition has a contiguous storage unit. By way of example, each micro-partition may contain between 50MB and 1000MB of uncompressed data (note that the actual size in the storage device may be smaller because the data may be stored compressed). Row groups in a table can be mapped to individual micro-partitions organized in a columnar manner. This size and structure allows for extremely fine-grained pruning of very large tables, which may include millions or even hundreds of millions of micro-partitions. Metadata about all rows stored in micro-partitions can be automatically collected, including: the value range of each column in the columns in the micro-partition; the number of different values; and / or other attributes for both optimization and efficient query processing. In one embodiment, micro-partitioning can be automatically performed on all tables. For example, a table can be transparently partitioned using the order in which data is inserted / loaded.

[0031] A detailed description of systems and methods consistent with embodiments of the present disclosure is provided below. Although several embodiments are described, it should be understood that the present disclosure is not limited to any one embodiment, but rather encompasses many alternatives, modifications, and equivalents. In addition, although many specific details are set forth in the following description to provide a thorough understanding of the embodiments disclosed herein, some embodiments may be practiced without some or all of these details. In addition, for the sake of clarity, certain technical materials known in the related art are not described in detail to avoid unnecessarily obscuring the present disclosure.

[0032] In an embodiment, a method for scheduling tasks to be performed on a database is disclosed. The method includes: executing a transaction on a table including database data, wherein executing the transaction includes generating a new table version. The method includes: in response to the transaction being fully executed, generating a change tracking entry including an indication of one or more modifications made to the table by the transaction, and storing the change tracking entry in a change tracking stream. The method includes executing the task on the new table version in response to a triggering event.

[0033] In an embodiment, the method further comprises advancing a stream offset in the change tracking stream in response to change tracking information consumed from the change tracking stream. In such an embodiment, a transaction (which may include a set of statements participating in the transaction) may include the advancement of the stream offset. In an embodiment, the triggering event comprises one or more of: a predefined time period, a threshold number of modifications made to a table, the availability of one or more change tracking entries in the change tracking stream, or the availability of a change tracking entry in the change tracking stream and a predefined time period.

[0034] In an embodiment, a transaction includes one or more of an insert, delete, merge, or update performed on a database table. In an embodiment, executing a transaction's data manipulation statements may include: generating at least one new micro-partition that reflects the transaction; and removing one or more existing micro-partitions that do not reflect the modifications made by the transaction. In an implementation where multiple new micro-partitions are generated based on one transaction, the change tracking information may include multiple change tracking entries, wherein each of the multiple change tracking entries is directed to a different micro-partition modified by the transaction. The change tracking information includes data related to the transaction, including, for example, an identifier of a user or account that initiated the transaction, a timestamp when the transaction was requested, a timestamp when the transaction execution started, a timestamp when the transaction execution completed, a list of all rows that the transaction has modified and how the rows were modified, a list of micro-partitions generated and / or removed based on the transaction, and any other suitable information related to the transaction. The change tracking information includes data in the micro-partitions to enable the lineage of table rows to be tracked across one or more micro-partitions. The table versioning information may include information about which micro-partitions are part of a particular table version, including which micro-partitions were added and removed. The table version control information may also include transaction information and / or query information, wherein the transaction information includes a start timestamp and an end timestamp of a transaction, and the query information includes which user executes which query.

[0035] In an embodiment, the change tracking information includes information about which rows were modified by a transaction, which micro-partitions were modified by a transaction, how a transaction modified rows and / or micro-partitions, information about prior modifications on a table, etc. The change tracking stream may include a lineage of modifications made to a table since the table was initially generated or since a particular time. The change tracking stream may include a list of table versions for a table, including all micro-partitions that are currently part of the table or have historically been part of the table. The change tracking stream may be stored in its own micro-partition, or may be stored as metadata in a micro-partition that includes database data.

[0036] In an embodiment, file metadata is stored in a metadata storage device. File metadata includes table versions and information about each table data file. The metadata storage device may include a mutable storage device (a storage device that can be overwritten or written in place), such as a local file system, system, memory, etc. In one embodiment, micro-partition metadata consists of two data sets: table version and file information. The table version data set includes a mapping of table versions to lists of added files and removed files. File information consists of information about each micro-partition, which includes, for example, micro-partition path, micro-partition size, micro-partition key ID, and a summary of all rows and columns stored in the micro-partition. Each modification to the table creates a new micro-partition and new micro-partition metadata. Inserting into a table creates a new micro-partition. If all rows in a micro-partition are not deleted, deleting from the table removes the micro-partition and may add a new micro-partition to the remaining rows in the table. Updating removes the micro-partition and replaces it with a new micro-partition with the row containing the updated record.

[0037] In one embodiment, metadata including change tracking columns may be stored in metadata micro-partitions in immutable storage. In one embodiment, the system may write metadata micro-partitions to cloud storage for each modification of a database table. In one embodiment, the system may download and read metadata micro-partitions to compute scan sets. Metadata micro-partitions may be downloaded in parallel and read upon receipt to improve scan set computations. In one embodiment, the system may periodically merge metadata micro-partitions in the background. In one embodiment, performance improvements may be included including pre-fetching, caching, column layout, etc. Additionally, security improvements including encryption and integrity checking may be made using metadata files with column layouts.

[0038] Figure 1 A schematic block diagram of a process flow 100 for ingesting and manipulating database data is shown. In an alternative embodiment, the process flow 100 includes a data lake 102, which may include a data warehouse. The data lake 102 includes a bucket file 104, which includes one or more data entries to be ingested in a database 106. At 108, the data is ingested into the database 106 at a staging table 110. At 112, a task is performed on the data to transform the data into one or more target tables, such as target table 1, target table 2, and / or target table 3. The task may include, for example, user-defined business logic, such as one or more transformations performed on the database data, such as merge, update, delete, insert, or other data manipulation language (DML) commands and any suitable multi-statement transaction or SQL statement. In an embodiment, the task is defined by the user and is applied in response to a trigger event such as a scheduled timer event.

[0039] Processing flow 100 enables data loaded from data lake 102 to undergo additional processing when executing a task at 112 before reaching its final destination table. A client account may provide SQL statements or DML commands to express the applicable processing logic for a task. Figure 1 In the illustrated embodiment, tasks are performed on the data after the data is loaded into the staging table 110 and before the data reaches the final destination table, such as Target Table 1, Target Table 2, and / or Target Table 3. The staging table 110 can be used to load data in a continuous and serverless manner. The data that has been ingested into the staging table 110 can also be modified by tasks performed at 112 before reaching one or more of its final destination target tables.

[0040] The tasks performed at 112 may be user defined. Such tasks may include stored procedures that provide a means for defining, for example, a sequence of SQL statements to be applied to data ingested into the staging table 110. A change tracking stream may be utilized to transactionally retrieve changes from the staging table 110 and consume the delta between the first timestamp and the second timestamp only once. Tasks may be referred to as objects in the database 106 catalog and may incorporate control flow logic. Tasks may include repeating tasks that are continuously processed as data is ingested and / or inputs are modified over a period of time. Tasks may be executed in response to triggering events.

[0041] Task scheduling can provide many benefits to database users and enables such users to schedule repetitive tasks on ingested database data and / or schedule tasks in response to one or more triggering events. Tasks such as those executed at 112 provide a scheduling mechanism that may not require resorting to external jobs for the common scheduling needs of SQL jobs. In addition, tasks enable the staging table 110 to be connected to downstream tables, such as target table 1, target table 2, and / or target table 3, using logic that is processed regularly, which obtains new data from the staging table 110 and transforms the data into the shape required by the target table.

[0042] In an embodiment, a task such as the task executed at 112 may be received from a client account in the form of a SQL statement that defines one or more tasks to be performed on the ingested data. Tasks may be scheduled to be executed in response to a triggering event. One or more triggering events may cause the execution of the task to be repeated at a predefined interval or based on a predefined threshold. In an embodiment, the triggering event is the execution of a transaction on a database, wherein the transaction includes one or more of an insertion, deletion, or update of database data. In an embodiment, the triggering event is the advancement of a change tracking stream in response to the full execution of a transaction on a database table. In an embodiment, the triggering event is the advancement of the change tracking stream plus a predefined time interval, such as, for example, 10 seconds after a change tracking entry is added to the change tracking stream, 5 minutes after a change tracking entry is added to the change tracking stream, etc. In an embodiment, such task scheduling may avoid scenarios where multiple incarnations of a task are executed simultaneously.

[0043] In an embodiment, a task is part of a database schema. A task may include information about one or more of the following: the date the task was created, the name of the task, the name of the database to which the task should apply, the schema to which the task belongs, comments attached to the task, the owner or creator of the task, the date the task was first initiated, the schedule of the task, the connection string, the SQL script, the state of the task - such as whether the task is running or stopped, the last execution time - such as the timestamp of the last complete execution of the task, or the last execution status - such as an indication of whether the task ran successfully or an error returned for the task if the task could not run successfully. In an embodiment, tasks may be managed and / or modified by a user account after they have been generated or executed.

[0044] In an embodiment, a task history is generated, which indicates whether the task was successfully executed or unsuccessfully executed. The task history may include metadata about the data, such as minimum / maximum value pairs of the relevant data, timestamps when the relevant data was ingested, timestamps when the relevant data was generated, etc. In addition, the task history may include information about the following items: how to modify the data, when to modify the data, how long it took to execute the task, how much processing power was required to execute the task, etc. In an embodiment, the start and end time parameters for the task history call are optional. In an embodiment, in order to be consistent with the behavior of similar history calls, the task history can report any suitable past time period, such as the past week, the past month, etc. In an embodiment, the task history includes one or more of the following: task name, task identifier, execution timestamp and execution correlation identifier, execution status such as SUCCESS or ERROR which may include an error code, a message attached to the task, or the result returned by the SQL statement executed by the task.

[0045] In an embodiment, a task such as the task executed at 112 is the execution of SQL code embedded or referenced in the database 106. The task can be initiated based on a schedule defined by the user in the task definition. In an embodiment, the current schedule definition does not allow overlapping executions of the same task definition. The execution of the task at 112 uses the context of the task to select an execution context (e.g., database, mode, etc.).

[0046] The task schedule may include a WAIT condition, or alternatively, a SCHEDULE attribute that defines an expression for triggering a time-based execution. Such a time-based execution may include a simple interval definition or a CRON expression. The WAIT condition is an expression that is evaluated before executing the task. If the expression is evaluated to false, the task execution will be skipped. Such an expression may allow reference to a stream such as a change tracking stream (see 202) that allows the activities of one or several streams to be derived, such as checking whether the stream is empty or contains changes from a base table. A task may include multiple stream checks with AND and / or OR into a larger Boolean expression, the requirement being that the Boolean expression is true for the SQL activity to be initiated.

[0047] In an embodiment, a task without WAIT and SCHEDULE attributes may indicate that the underlying system is to execute the task in a continuous manner using best effort based on an internal schedule, data change notifications, or simply using continuous execution of queries defined in the task.

[0048] In an embodiment, multiple applications of the same task cannot overlap. In such an embodiment, if the previous task execution 112 is still being processed, the next execution cannot be initiated. Instead, the next execution can be skipped, or the next execution can be initiated at a later time based on conventional task scheduling.

[0049] The processing flow 100 can be implemented as an automated ingestion framework to efficiently load files into target tables. The processing flow 100 can provide an improved ingestion pipeline compared to other implementations known in the art. In various implementations, the processing flow 100 can allow one or more of the following: loading data from an initial staging table 110 into multiple other target tables; loading data into an initial staging table 110 and then merging into other target tables; or expressing a complex workflow as one or more tasks executed at 112. The processing flow 100 can eliminate the need for multiple data ingestion pipelines and enable data to be directed to multiple target tables through a single automated ingestion pipeline. The processing flow 100 provides a cost-effective way to ingest data into multiple target tables that provides benefits over other methods that utilize COPY or MERGE commands. Similar methods using COPY or MERGE commands can be problematic because MERGE is expensive and requires clustering of data for efficiency. The process flow 100 disclosed herein provides an efficient and cost-effective way to ingest data into multiple target tables and apply one or more tasks to the ingested data.

[0050] In an embodiment, the task performed at 112 is implemented as a continuous task. A continuous task can be a specific user-defined logic that is executed continuously. The so-called "continuous" execution of the task at 112 may not be truly continuous, but may include discrete actions that are evaluated and / or executed frequently enough to make it appear to be continuous. A continuous task may include user-defined logic that represents a real task that the client account wishes to perform. A continuous task may include an optional user-defined condition that determines whether the task needs to be called at a specific time. A continuous task may also include an indication of a data warehouse where the continuous task should be executed.

[0051] In an embodiment, the task is executed at 112 only after the WAIT condition is met. If the WAIT condition is not specified, the task will continue according to the task schedule. Triggering events may not be required in some implementations, but can provide cost savings for evaluating actions and determining whether the task needs to be executed. In an embodiment, the WAIT condition is evaluated in the task execution context before executing the query, so that the WAIT condition must be an expression that can be evaluated in the query compilation environment. Various example WAIT conditions include: a predefined time period has passed since the start time of the last execution of the task; a predefined time period has passed since the end time of the last execution of the task; the state of the last execution of the task; a predefined number of rows of changes captured in the change tracking stream (see 506); or the timestamp of the earliest entry of the change tracking stream 506. In an embodiment, the task includes multiple triggering events. In various embodiments, the task can continue after one of the triggering events is met, after one or more specific triggering events are met, after all triggering events are met, etc.

[0052] Figure 2 A schematic diagram of a table history 200 with multiple table versions and multiple entries for a change tracking stream 202 is shown. Table history 200 shows three table versions, namely, table version 0, table version 1, and table version 2. Change tracking stream 202 includes three entries, namely, change tracking entry 1, change tracking entry 2, and change tracking entry 3. Change tracking entry 1 is entered into change tracking stream 202 at time T1. Change tracking entry 2 is entered into change tracking stream 202 at time T2. Change tracking entry 3 is entered into change tracking stream 202 at time T3. Each of the change tracking entries is entered into change tracking stream 202 only after a transaction performed on a database table is completed. Transaction 1 is performed on table version 0, and change tracking entry 1 is entered into change tracking stream 202 after transaction 1 is completed. Transaction 2 is performed on table version 2, and change tracking entry 2 is entered into change tracking stream 202 after transaction 2 is completed. Transaction 3 is executed on table version 3, and after completion of transaction 3, change tracking entry 3 is entered into change tracking stream 202. In one embodiment, change tracking stream 202 may also include "change tracking entry 0", which includes metadata about the original version of the table.

[0053] Change tracking stream 202 advances only after a transaction has been fully executed on the table, i.e., a new change tracking entry is added. If a transaction is initiated on the table, but the transaction is canceled or suspended for any reason, no corresponding change tracking entry will be generated, and change tracking stream 202 will not advance.

[0054] The change tracking stream 202 may be read to determine a delta of a table between a first timestamp and a second timestamp. Each of the change tracking entries includes an indication of a transaction performed on the table and also includes a timestamp of the transaction. Queries may be run on the change tracking stream 202 to determine which rows of the table have been added, deleted, and / or updated between two timestamps. Additionally, queries may be run on the change tracking stream 202 to determine a comprehensive change tracking summary between the two timestamps, wherein the comprehensive change tracking summary indicates all intermediate modifications that have occurred on the table between the two timestamps.

[0055] In an embodiment, the change tracking stream 202 advances sequentially so that each change tracking entry is related to a transaction that occurred on the table. The change tracking entries are arranged in chronological order according to the transaction timestamp.

[0056] In an embodiment, the table version indicates a new table version after the data is ingested into the database. In an embodiment, the data is continuously ingested into the first table by means of an automatic ingestion service. While ingesting the data into the first table, it may be necessary to merge the data in the first table with the data in the second table. In an embodiment, the data may be periodically copied into the first table and further split into multiple other tables.

[0057] Figure 3 A schematic block diagram of a table history 300 with multiple table versions is shown. The example table history 300 shows three table versions, namely, table version 1, table version 2, and table version 3. Table version 1 includes data in the form of the following three micro-partitions (MPs), namely, micro-partition 1 (MP1), micro-partition 2 (MP2), and micro-partition 3 (MP3). A first transaction 302 is executed on table version 1 to generate table version 2. The first transaction 302 includes deleting rows in MP2 to generate a new micro-partition 4 (MP4) and deleting the original MP2. The first transaction 302 executed on table version 1 generates table version 2 including the original MP1 and MP3 and the newly generated MP4. As a result of the first transaction 302, MP2 has been removed from the table as reflected by table version 2. A second transaction 304 is executed on table version 2 to generate table version 3. The second transaction 304 includes inserting a new row, resulting in the generation of micro-partition 5 (MP5) and the removal of MP3 from the table. Table version 3 includes the original MP1, MP4 generated as a result of the first transaction 302, and MP5 generated as a result of the second transaction 304. MP2 is removed as a result of the first transaction 302, and MP3 is removed from the table as a result of the second transaction 304.

[0058] like Figure 3As shown, a database table may store database data in one or more micro-partitions, wherein the micro-partitions constitute immutable storage devices. When a change or modification is performed on a table, the affected micro-partitions are removed and new micro-partitions reflecting the change are created. In an embodiment, the original unmodified micro-partitions are not removed, but are also stored with the new micro-partitions. The change may include any command that affects one or more rows in a table, including, for example, a delete command, an insert command, an update command, and / or a merge command.

[0059] In an embodiment, a comprehensive change tracking summary indicating all changes that have been made between, for example, table version 1 and table version 3 may be determined. Figure 1 The comprehensive change tracking summary for the implementation shown in will indicate that the first transaction 302 caused rows to be deleted from MP2 and, in the absence of those rows, caused MP4 to be generated. The summary will also indicate that the second transaction 304 caused new rows to be inserted into the table, caused MP3 to be removed, and caused MP5 to be generated in the presence of those new rows. The comprehensive change tracking summary indicates all transactions that occurred on the table, when those transactions occurred, and how those transactions affected the table.

[0060] In an embodiment, the table history is updated only when a transaction is fully completed. Therefore, if a transaction is initiated but not fully completed, the table history will not be updated to include the transaction. For example, if a transaction is initiated to delete certain rows of the original micro-partition, a new micro-partition will be generated that includes all of the original rows in the original micro-partition except for the rows that should be deleted based on the delete command. If the transaction is not completed, that is, if the new micro-partition is not fully generated, the table history will not be updated to indicate that a transaction has occurred.

[0061] In an embodiment, the stream concept can be applied not only to table streams, but also to query execution in general. Similar to a stream that can provide a delta between two timestamps of table data by performing a logical comparison on the data during query execution, a stream can provide a delta between query results at two different transaction timestamps.

[0062] Figure 4 A schematic block diagram of an example query pipeline 400 is shown. In the query pipeline 400, table page views (tablepage_views) 402 can be manipulated with one or more transactions such as inserts, deletes, merges, or updates. Most commonly, the table page views 402 are populated with insert statements as data is ingested into the database. When the table page views 402 are populated with transactions, query page identifiers (query_page_ids) 404 represent continuous queries that generate results accordingly. The query page identifiers 404 are used to generate one or more streams. In Figure 4In the illustrated implementation, the query page identification 404 generates two streams, namely, stream 1 and stream 2. Stream 1 and stream 2 are located at different points in the query flow and will return different record sets when queried.

[0063] Figure 5 Schematic block diagram of an example change tracking flow 506 is shown. The change tracking flow 506 is generated based on a series of transactions 502: transactions at (timestamp) T1, transactions at (timestamp) T2, transactions at (timestamp) T3, and transactions at (timestamp) T4 arranged based on commit time timestamps. Transactions 502 generate a series of table versions 504, namely, table V1, table V2, table V3, and table V4. Figure 5 As shown, the change tracking stream 506 is updated at timestamp T1 , timestamp T2 , timestamp T3 , and timestamp T4 .

[0064] As shown, the transaction at T1 includes inserting R1 (i.e., row identification number 1) and inserting R2. Table V1 includes row R1 at timestamp T1 and row R2 at timestamp T1. The transaction at T2 includes inserting R3 and updating R1. Table V2 includes row R1 at timestamp T2, row R2 at timestamp T1, and row R3 at timestamp T2. The transaction at T3 includes deleting R2 and inserting R4. Table V3 includes row R1 at timestamp T2, row R3 at timestamp T2, and row R4 at timestamp T3. The transaction at T4 includes updating R1 and updating R4. Table V4 includes row R1 at timestamp T4, row R3 at timestamp T2, and row R4 at timestamp T4.

[0065] Change tracking stream 506 includes a change tracking entry at timestamp T1 indicating that R1 and R2 are inserted into the table. The change tracking entry at timestamp T2 indicates that R3 and R1 are inserted into the table and R1 is deleted from the table. The change tracking entry at timestamp T3 indicates that R2 is deleted from the table and R4 is inserted into the table. The timestamp at T4 indicates that R1 and R4 are deleted from the table while R1 and R4 are inserted into the table.

[0066] The change tracking stream 506 is a transaction container and maintains the current (i.e., latest) point in time at which transactions have been executed on the database tables, and also maintains information about which table versions can be safely discarded. The change tracking stream 506 is used to support Figure 1 The building blocks of the task and / or query pipeline shown. The change tracking stream 506 is implemented by introducing a method for maintaining stable row identification values ​​(e.g., micro-partition name and micro-partition row number) during DML statements. Change tracking entries can be retrieved from the table by utilizing a stream object, which will track the transaction time point at which modifications can be retrieved.

[0067] In an embodiment, the task is executed at 112 in a transaction, and the change tracking stream 102 is advanced only when the transaction commits. This allows for exactly once behavior with respect to consuming changes from the change tracking stream 102. The task schedule may indicate that the task should be initiated after a predefined period of time after the change tracking stream 506 has data for consumption. In an example embodiment, the task schedule indicates that the task should be executed 100 seconds after the last execution of the transaction that advanced the change tracking stream 506. In an example embodiment, the task schedule indicates that the task should be executed after at least 100 modified rows have been captured in the change tracking stream 506.

[0068] Even in the case of session loss or other service errors, the use of change tracking flow 506 can allow users to retrieve query results for tasks in a reliable manner. This provides significant benefits relative to other implementations known in the art for executing tasks, logic or queries on database tables.

[0069] Figure 6 A first transaction timeline 600 and a second transaction timeline 610 of a database table are shown. The database table undergoes a series of transactions including transactions T1, T2, T3, T4, T5, T6, and T7. The change tracking stream 506 maintains timestamp offsets on the transaction timelines 600, 610 of the table. In an embodiment, when the change tracking stream 506 is requested, the changeset interval starts at the last read timestamp, and the end interval is the current transaction commit time of the table. In an embodiment, given the retention time for any given table, the retention boundary 602 can be the "earliest" timestamp that the stream offset 604 can take. When the change tracking stream 506 is initialized on the table, the change tracking stream 506 can select the current transaction timestamp as the offset (i.e., as shown in the first transaction timeline 600, the start of the changeset interval is T4). The changeset of the row returned from the change tracking stream 506 in this case is the delta information generated by transactions T4 and T5. For example, when the stream offset of the second transaction timeline 610 advances to T6, the retention boundary 612 and the stream offset 614 are shifted so that reading the changeset will return the deltas generated by transactions T6 and T7.

[0070] In an embodiment, the change tracking stream 506 may be available as a system table valued function that includes a table name, a start timestamp, and optionally an end transaction time value for a changeset interval. The stream offsets 604, 614 of the change tracking stream 506 may be transactionally saved to enable independent stream processing. The change tracking stream 506 may be a first-class schema object that is linked to a base table object and supports transactional manipulation of the offsets it maintains. In an embodiment, the change tracking stream 506 includes the same columns as the table it is generated from, including additional METADATA$ACTION and METADATA$ISUPDATE columns that indicate the action (i.e., insert or delete) and whether this is part of an update of the rows returned when queried from the stream, respectively.

[0071] In an embodiment, when a change tracking stream 506 is generated on a table, the change tracking stream 506 will initialize the offset to the current table version. The change tracking stream 506 will support select (SELECT) operations similar to tables and views, but may not support explicit DML operations in some embodiments. Queries can be run on the change tracking stream 506 to return a set of modifications from the current stream offset to the current transaction time of the source table. When used in an auto-commit statement, the change tracking stream 506 can automatically advance the internal stream offset to the transaction time when the query was run (if the query or transaction did not fail). When running in an explicit multi-statement transaction, the change tracking stream 506 can select the transaction time of the start time of the transaction as the upper bound of the changed transaction interval and return it together with the offset as the lower bound. The change tracking stream 506 will not advance the stream offset until the transaction is fully committed, which means that multiple statements can retrieve the change set from the change tracking stream 506. Because if a DML statement modifies the table, the upper bound can be set by the transaction start time, and inside the transaction, the modification will not be reflected in the change set retrieved from the change tracking stream 506.

[0072] Figure 7 A block diagram of an example delete command 700 and a resulting delta 710 that may be returned after the delete command 700 is completed is shown. Figure 7 The illustrated micro-partition 2 (MP2) includes four columns. Column 1 includes a row number entry that is primarily used for identification purposes. Column 2 includes a row value entry that can include any value depending on the subject or purpose of the database table. The metadata name column includes table history information about which micro-partition the data originated from or was last located in. The metadata row column includes table history information about which row the data originated from or was located in.

[0073] like Figure 7 As shown, the delete command 700 is executed on MP2, and lines 2 and 3 are deleted from MP2 to generate a new MP4. Figure 2As shown, MP2 includes four rows, namely row 1, row 2, row 3 and row 4. It should be understood that micro-partitions can include any number of rows, and often thousands of rows can be included in a single micro-partition. By way of example, the values ​​used for each of the rows in MP2 are listed as value 1, value 2, value 3 and value 4 for the four rows, but it should be understood that the values ​​can include any suitable values ​​related to the database. In the original and unmodified MP2, the metadata name for each of the four rows is "NULL(MP2)", which indicates that the data for this micro-partition is original and there is no change history. Similarly, the metadata row column for MP2 is NULL and indicates the original row number because the data for MP2 is original and there is no change tracking history.

[0074] like Figure 7 As shown, MP4 is generated based on the delete command 700 executed on MP2 to delete rows 2 and 3. MP4 now includes only rows 1 and 4 having values ​​1 and 4, respectively. The metadata for each of rows 1 and 4 is "MP2" indicating that the row data originated from MP2 or was last located within MP2. The metadata lines for each of rows 1 and 4 indicate that the row was last located at positions 1 and 4, respectively.

[0075] After executing the delete command 700 on the table, a delta 710 may be determined. In an embodiment, a timestamp is appended to each transaction that occurs on the table. If the transaction is fully completed, the timestamp is further appended to the change tracking history of the transaction. Appending the timestamp to the change tracking history enables the system to know when a table is changed by a particular transaction and when a particular change occurs on any of the multiple rows in the table.

[0076] Figure 7 The delta 710 shown determines the difference or change that occurred between MP2 and MP4. In various embodiments, the delta 710 can determine the total change that occurred between any two timestamps, even if many transactions have occurred with respect to the data between the two timestamps and the data has been changed many times. The delta 710 provides an indication of the total change between the two timestamps without providing information about any intermediate changes that occurred between the first timestamp and the second timestamp.

[0077] Delta 710 includes four columns, namely, column 1 and column 2 (similar to those shown in MP2 and MP4) and a metadata action column and a metadata is updated column. Column 1 indicates that row 2 and row 3 have been changed between the first timestamp and the second timestamp. Column 2 indicates that the values ​​of row 2 and row 3 are value 2 and value 3, respectively. In various embodiments, column 2 may indicate the original value or the most recent value, where the values ​​of row 2 and row 3 may have changed one or more times between the first timestamp and the second timestamp. The metadata action column indicates that a delete (DELETE) command was performed on row 2 and row 3. The metadata is updated column indicates whether the metadata has been updated. In Figure 7 In the delta 710 shown in FIG. 7 , the metadata is an updated column that returns FALSE because the row was not updated (but was deleted).

[0078] In an embodiment, a table history is generated that includes all transactions that have been initiated for a table. Such transactions may include, for example, data manipulation language (DML) commands, such as delete, insert, merge, or update commands initiated on a micro-partition. The table history may be dynamically updated to reflect all deleted rows or inserted rows on the table for a transaction time interval. The table history may include a list of DML statements sorted by transaction time, where each transaction includes a timestamp. In an embodiment, it is assumed that all DML statements will delete, insert, and / or update rows at the same time, and this may be achieved by performing a merge (MERGE) statement.

[0079] In an embodiment, the table history may be determined by retrieving a list of added and removed micro-partitions between two transactions. For each given micro-partition, a lineage sequence may be generated that indicates dependencies of which rows were updated, how those rows were updated, and what transaction caused each update. The delta may be determined by requesting the set of changes between two timestamps in the series. The delta may return a list of all added micro-partitions and all removed micro-partitions between the two timestamps. The lists may be merged by removing those micro-partitions that appear on both the added micro-partition list and the removed micro-partition list. This may be accomplished by performing an external merge on the added and removed micro-partition lists.

[0080] Furthermore, in an embodiment, a granular list of rows that changed between any two transaction times for a given table can be determined. A side-by-side representation can be generated that can be easily used in a merge statement by checking which portion of the data exists. Table-valued functions can be utilized to query the table history (or materialized view) for a given table. Furthermore, SQL statements can be used by referencing the INSERTED or DELETED columns in a changeset to return an indication of which rows in the table have been inserted or deleted.

[0081] Figure 8A block diagram of an example insert command 800 and a result delta 810 that may be returned after the insert command 800 completes is shown. Figure 8 Beginning with exemplary micro-partition 3 (MP3), exemplary micro-partition 3 (MP3) is subjected to insert command 800 to generate micro-partition 5 (MP5). Insert command 800 inserts row 17 and row 18 into MP3. As an example implementation, MP3 includes three rows, namely row 21, row 22, and row 23, having values ​​of value 21, value 22, and value 23, respectively. The metadata name for each of the three rows is NULL(MP3) because there is no change tracking history for the row, which indicates where the row originated from or was last stored. The NULL(value) symbol indicates that when the row is first inserted into the table, the value of the metadata column is NULL, but the column has an implicit value that will be used when the row is copied to a new partition. NULL values ​​can reduce overhead. When the value of the row is copied to the new micro-partition, the row is represented by the NULL(row number) symbol. In such an implementation, the NULL(row number) symbol for the first row is 1, the second row is 2, the third row is 3, and so on.

[0082] MP5 is generated based on insert command 800, and now includes rows 17 and 18. The values ​​of rows 17 and 18 are value 17 and value 18, respectively, because rows 17 and 18 are inserted into MP5, and value 17 and value 18 are the assigned values ​​of the rows. The values ​​of rows 21, 22, and 23 are unchanged. The metadata name information of rows 21, 22, and 23 is "MP3" because the data originates from micro-partition 3 or was last stored in micro-partition 3. The metadata row information of rows 21, 22, and 23 is 1, 2, and 3, respectively, because rows 21, 22, and 23 were originally or last stored in rows 1, 2, and 3 of micro-partition 3. The metadata name information and metadata row information of rows 17 and 18 are "NULL" because the rows originate from MP5 and there is no change tracking history information yet.

[0083] The increment 810 for the insert command 800 shows the total changes made between the first timestamp and the second timestamp. Figure 8 As shown, delta 810 shows the changes that occurred between MP5 and MP3. It should be appreciated that in alternative embodiments or implementations, deltas may indicate the total changes or modifications that occurred on the table between any two timestamps, rather than indicating incremental changes that occurred on the table.

[0084] Delta 810 includes rows 17 and 18 having values ​​17 and 18, respectively, because rows 17 and 18 were added to the MP3 as a result of insert command 800. The metadata action for rows 17 and 18 is "INSERT" because insert command 800 is a transaction that causes row modification. The update information for the metadata for rows 17 and 18 is "FALSE" because the rows were not updated but inserted.

[0085] Fig. 9 A block diagram of an example update command 900 and a resulting delta 910 that may be returned after the update command 900 is completed is shown. Fig. 9 In the illustrated example implementation, micro-partition 78 (MP78) is updated to generate micro-partition 91 (MP91). Update command 900 updates row 1 and row 4 to new values. MP78 includes row 1, row 2, row 3, and row 4 having values ​​of value 1, value 2, value 3, and value 4, respectively. The metadata name information for each of the rows is "NULL (MP78)" because there is no change tracking history for the row, and the change tracking history indicates where the row was last stored. The metadata row information for each of the rows is NULL because there is no change tracking history for the row, and the change tracking history indicates in which row the value was last stored.

[0086] MP91 includes row 1, row 2, row 3, and row 4. However, due to update command 900, row 1 now has a value of 11 and row 4 now has a value of 44. The metadata name information for each of row 1, row 2, row 3, and row 4 is "MP78" because the value originated from MP78 or was last stored in MP78. The metadata row information for row 1 is "1" because the value was last stored in row 1 of MP78. Similarly, for row 2, row 3, and row 4, the metadata row information is "2", "3", and "4", respectively.

[0087] Increment 910 indicates the change between the first timestamp and the second timestamp. Fig. 9 As shown, increment 910 indicates the total changes between MP78 and MP91 due to update command 900. Increment 910 indicates the deletion of row 1 and row 4 having values ​​of "value 1" and "value 2", respectively. Increment 910 indicates the insertion of row 1 and row 4 having values ​​of "value 11" and "value 44", respectively. The update information for all row metadata is "true" because update command 900 was executed on the row. As indicated by increment 910, when an update command is executed, the original row is deleted and a new row is inserted to execute the command.

[0088] In an embodiment, each increment 710, 810, 910 may be used to determine a change tracking entry for the table that may be inserted into the change tracking stream. The increment information may be stored as metadata in the change tracking stream in each new micro-partition generated in the table. The advancement of the change tracking stream may be used as a trigger event to initiate execution of a task on the table.

[0089] Go to Fig.10 , a block diagram of a processing platform 1000 for providing database services according to one embodiment is shown. The processing platform 1000 includes a database service manager 1002 that can be accessed by multiple users 1004, 1006, and 1008. The database service manager 1002 may also be referred to as a resource manager or a global service in this article. In some implementations, the database service manager 1002 can support any number of users who desire to access the data or services of the processing platform 1000. Users 1004 to 1008 may include the following: for example, end users who provide data storage and retrieval queries and requests, system administrators who manage the systems and methods described herein, software applications that interact with databases, and other components / devices that interact with the database service manager 1002. In a specific embodiment as shown herein, users 1004 to 1008 can initiate changes to database data and can request increments for database tables.

[0090] The database service manager 1002 may provide various services and functions to support the operation of systems and components within the processing platform 1000. The database service manager 1002 accesses the stored metadata associated with the data stored throughout the data processing platform 1000. The database service manager 1002 may use the metadata to optimize user queries. In some embodiments, the metadata includes a summary of data stored in a remote data storage system and data available from a local cache (e.g., a cache within one or more clusters in the cluster of the execution platform 1012). Additionally, the metadata may include information about how the data is organized in the remote data storage system and the local cache. The metadata enables systems and services to determine whether a piece of data needs to be processed without loading or accessing the actual data from a storage device.

[0091] As part of the data processing platform 1000, metadata can be collected when data is changed using a data manipulation language (DML), which can be performed by means of any DML statement. Examples of manipulating data can include, but are not limited to, selecting, updating, changing, merging data, and inserting data into a table. As part of the processing platform 1000, micro-partitions can be created, and metadata can be collected on a per-file and per-column basis. This collection of metadata can be performed during data ingestion, or the collection of metadata can be performed as a separate process after ingesting or loading data. In an implementation, metadata can include multiple different values, multiple null values, and minimum and maximum values ​​for each file. In an implementation, metadata can also include string length information and the range of characters in the string.

[0092] In one embodiment, at least a portion of the metadata is stored in an immutable storage device such as a micro-partition. For example, the metadata may be stored on the storage platform 1014 together with the table data. In one embodiment, the same or separate cloud storage resources used for the table data may be allocated to and used for the metadata. In one embodiment, the metadata may be stored in a local immutable storage device. In one embodiment, information about the metadata in the immutable storage device or information about the metadata file stored in the immutable storage device is stored in the mutable storage device 1010. The information about the metadata may be referenced to locate and access the metadata stored in the immutable storage device. In one embodiment, a system having a metadata storage device may be reconfigured so that the metadata storage device is used instead to store information about the metadata file located in the immutable storage device.

[0093] The database service manager 1002 also communicates with an execution platform 1012, which provides computing resources for performing various data storage and data retrieval operations. The execution platform 1012 may include one or more computing clusters. The execution platform 1012 communicates with one or more data storage devices 1016, 1018, and 1020 that are part of the storage platform 1014. Although Fig.10Three data storage devices 1016, 1018 and 1020 are shown, but the execution platform 1012 can communicate with any number of data storage devices. In some embodiments, data storage devices 1016, 1018 and 1020 are cloud-based storage devices located in one or more geographical locations. For example, data storage devices 1016, 1018 and 1020 can be part of a distributed storage system of a public cloud infrastructure or a private cloud infrastructure or any other manner. Data storage devices 1016, 1018 and 1020 may include a hard disk drive (HDD), a solid state drive (SSD), a storage cluster or any other data storage technology. Additionally, the storage platform 1014 may include a distributed file system (such as a Hadoop distributed file system (HDFS)), an object storage system, etc.

[0094] In some embodiments, the following communication links are implemented via one or more data communication networks: a communication link between the database service manager 1002 and the user 1004 to the user 1008, a communication link between the database service manager 1002 and the variable storage device 1010 for information about the metadata file (i.e., metadata file metadata), and a communication link between the database service manager 1002 and the execution platform 1012, and the communication link can allocate various tasks so that user requests can be optimized. Similarly, the communication link between the execution platform 1012 and the data storage device 1016 to the data storage device 1020 in the storage platform 1014 is implemented via one or more data communication networks. These data communication networks can utilize any communication protocol and any type of communication medium. In some embodiments, the data communication network is a combination of two or more data communication networks (or sub-networks) coupled to each other. In an alternative embodiment, these communication links are implemented using any type of communication medium and any communication protocol.

[0095] Fig.10The database service manager 1002, variable storage 1010, execution platform 1012, and storage platform 1014 are shown as individual components. However, each of the database service manager 1002, variable storage 1010, execution platform 1012, and storage platform 1014 may be implemented as a distributed system (e.g., distributed across multiple systems / platforms at multiple geographic locations) or may be combined into one or more systems. Additionally, each of the database service manager 1002, variable storage 1010, execution platform 1012, and storage platform 1014 may be expanded or contracted (independently of one another) based on changes in requests received from users 1004 to 1008 and changing needs of the data processing platform 1000. Therefore, in the described embodiment, the data processing platform 1000 is dynamic and supports regular changes to meet current data processing needs.

[0096] Fig.11 A block diagram depicting components of a database service manager 1002 is shown according to one embodiment. The database service manager 1002 includes a key manager 1104 and an access manager 1102 coupled to a data storage device 1106. The access manager 1102 handles authentication and authorization tasks for the system described herein. The key manager 1104 manages the storage and authentication of keys used during authentication and authorization tasks. The request processing service 1108 manages received data storage requests and data retrieval requests. The management console service 1110 supports access to various systems and processes by administrators and other system managers.

[0097] The database service manager 1002 also includes a SQL compiler 1112, a SQL optimizer 1114, and a SQL executor 1116. The SQL compiler 1112 parses the SQL query and generates execution code for the query. The SQL optimizer 1114 determines the best method for executing the query based on the data to be processed. The SQL executor 1116 executes the query code for the query received by the database service manager 1002. The query scheduler and coordinator 1118 sends the received query to the appropriate service or system for compilation, optimization, and dispatching to the execution platform 1112. The virtual warehouse manager 1120 manages the operation of multiple virtual warehouses.

[0098] Additionally, the database service manager 1002 includes a task execution manager 1128 that manages information related to data stored in remote data storage devices and local caches. The monitor and workload analyzer 1124 oversees the processes executed by the database service manager 1002 and manages the distribution of tasks (e.g., workloads) across virtual warehouses and execution nodes of the execution platform 1012. The task execution manager 1128 and the monitor and workload analyzer 1124 are coupled to the data storage device 1126. In one embodiment, the configuration and metadata manager 1122 collects, stores, and manages metadata in immutable storage resources. In one embodiment, updates to metadata generate new files and updates to metadata are not updated in place.

[0099] As discussed herein, a metadata file may include a file containing metadata for modifications (e.g., each modification) to any database table in a data warehouse. A modification to a database table may generate one or more metadata files, typically just a single metadata file. In one embodiment, the metadata file contains the following information: information about the metadata file, including a version number; a list of all added table data files; a list of deleted table data files; and information about each added table data file, including the file path, file size, file key identifier, and a summary of all rows and columns stored in the table data file.

[0100] In one embodiment, the content of the metadata file may change over time. If the format or content of the metadata file changes, the version number of the metadata file may increase. In one embodiment, the metadata storage device (or other mutable data storage resource) only stores information about the metadata files (the metadata files are stored in the immutable storage device), and does not store information about the table data files. In practice, the information about the metadata files stored in the metadata storage device (or other mutable storage device) is very limited and may contain data for thousands of metadata files. In one embodiment, information about up to 30,000 metadata files may be stored in the metadata storage device or other mutable storage device. This greatly reduces the amount of storage required in the metadata storage device or other mutable storage device.

[0101] In one embodiment, for each modification to a database table (e.g., modification of a table data file), the system writes a metadata file to the cloud storage device. In addition to adding and deleting files, each modification to a database table in the data warehouse also generates one or more metadata files. Typically, a modification creates a single metadata file. However, if the modification to the table is large (e.g., an insert into a table that generates many files), it may result in the creation of multiple metadata files. In addition, the operation of the task execution manager 1128 will be combined with Figures 6 to 12 Further discussion.

[0102] The database service manager 1002 also includes a task execution manager 1128 that manages the scheduling and execution of one or more tasks on the database data, wherein a task may include user-defined logic such as SQL statements. Because multiple users / systems can access the same data at the same time, changes to the data can be synchronized to ensure that each user / system works with the current version of the data and accesses the change tracking history for the data.

[0103] Security improvements are also achieved in some embodiments. In one embodiment, the metadata file and change tracking information are encrypted using a single file key. Within a micro-partition, columns can be encrypted individually using AES-CTR mode with different starting counters. This enables the database system to read a single column from a micro-partition because the micro-partition can be decrypted immediately without having to immediately decrypt the entire micro-partition. Encryption improves security because no one can read the micro-partition without the appropriate file key.

[0104] To verify that a micro-partition has not been changed, the system can store a hash of the column for each column within a micro-partition. Before decrypting the data, the system compares the hash of the encrypted column for that micro-partition with the stored hash of the column. If the hashes do not match, the micro-partition must be changed. This improves security because all changes to the micro-partition are detected by the database system.

[0105] Fig.12 1 is a schematic block diagram showing the components of a task execution manager 1128 according to one embodiment. The task execution manager 1128 can manage the scheduling and execution of one or more tasks on database table data. The task execution manager 1128 includes a transaction component 1202, a change tracking component 1204, a trigger component 1206, a task scheduling component 1208, and a task execution component 1210. Components 1202 to 1210 are given only by way of illustration and may not be included in all embodiments. In fact, some embodiments may include any combination of only one or two or more of the components 1202 to 1210. For example, some of the components may be located outside the task execution manager 1128 or separated from the task execution manager 1128, such as being located in the database service manager 1002 or the processing platform 1000. In addition, components 1202 to 1210 may include hardware, computer-readable instructions, or a combination of both to perform functions and provide structures discussed herein.

[0106] The transaction component 1202 performs one or more transactions on a table of a database, wherein the table includes database data. The transaction may include any DML command, such as insert, delete, update and / or merge commands. In an embodiment, the transaction component 1202 performs transactions on micro-partitions of a database table by generating new micro-partitions reflecting the transaction and removing the (original) micro-partitions. A single transaction may affect multiple rows of database data across multiple micro-partitions, and may therefore cause the generation of multiple new micro-partitions. In an embodiment, the transaction component 1202 never performs transactions by modifying database data in place, but rather generates new immutable storage devices such as micro-partitions.

[0107] The transaction component 1202 also generates and stores transaction data for each transaction executed on the table. The transaction component 1202 can associate the transaction data with the new micro-partition so that the transaction data is stored as metadata for the new micro-partition. Such transaction data may include, for example, an identifier of the user or account that initiated the DML or SQL statement, when the transaction was requested, when the transaction was initiated, when the transaction was completed, how the transaction affected the table, what rows and / or micro-partitions were modified by the transaction, etc.

[0108] The change tracking component 1204 generates and stores change tracking information about transactions that have been executed on the database table. The change tracking information can be stored in one or more change tracking entries that form the lineage of modified information in the change tracking stream. For example, the change tracking component 1204 can store the change tracking information as metadata on an immutable storage device such as a micro-partition, the change tracking information can be stored in a local mutable storage device and / or can be stored as a change tracking stream in the table data. In one embodiment, the change tracking information can be stored and updated in place. In one embodiment, the change tracking information is stored in an immutable micro-partition in a change tracking stream that indicates the most recent changes that occurred on the row and / or a log of changes that occurred on the table. In an embodiment, the change tracking component 1204 protects the timestamps of transactions that occurred on the table, and also protects the timestamps of the associated change tracking entries in the change tracking stream.

[0109] The trigger component 1206 determines one or more event triggers that may be associated with the task to be performed on the database. The trigger component 1206 may also determine whether the trigger event is satisfied and therefore the task should be performed on the database. The trigger component 1206 may receive an indication of the trigger event from the client account and / or may automatically generate the trigger event based on predefined rules.

[0110] The task scheduling component 1208 schedules tasks to be performed on the database based on one or more triggering events. In an embodiment, the task scheduling component 1208 schedules tasks that must be performed on all newly ingested data while storing all newly ingested data in the staging table 110 before being stored in the final target table.

[0111] The task execution component 1210 performs one or more tasks on the database data. The task execution component 1210 can initiate a task in response to the trigger component 1206 determining that a trigger event has been satisfied. The task can include user-defined logic, such as a SQL statement received from a client account. The task can manipulate the database data when it is ingested into the database to ensure that the data is in the correct shape to be stored in the target table. The task can include, for example, continuous or automatic querying of database data.

[0112] Fig.13 13 is a schematic flow chart illustrating an example method 1300 for scheduling tasks to be executed on a database. The method 1300 may be performed by the task execution manager 1128, the database service manager 1002, the processing platform 1000, and / or other services or platforms.

[0113] The method 1300 begins, and at 1302 a computing device executes a transaction on a table including database data, wherein executing the transaction includes generating a new table version. In response to fully executing the transaction, at 1304 the computing device generates a change tracking entry including an indication of one or more modifications made to the table by the transaction. At 1306 the computing device enters the change tracking entry into a change tracking stream. In response to a triggering event, at 1308 the computing device executes a task on the new table version.

[0114] Fig.14 14 is a schematic flow chart illustrating an example method 1400 for scheduling tasks to be executed on a database. The method 1400 may be performed by the task execution manager 1128, the database service manager 1002, the processing platform 1000, and / or other services or platforms.

[0115] The method 1400 begins, and at 1402 a computing device ingests data into a staging table of a database, wherein ingesting the data includes generating a new micro-partition including the data. In response to completely generating the new micro-partition, at 1404 the computing device executes a task on the new micro-partition stored in the staging table. In response to completely executing the task on the new micro-partition, at 1406 the computing device stores the new micro-partition in one or more target tables. The method 1400 causes the task to include a SQL statement received from a client account (see 1408).

[0116] Fig.151 is a block diagram depicting an example computing device 1500. In some embodiments, computing device 1500 is used to implement one or more of the systems and components discussed herein. For example, computing device 1500 may include task execution manager 1128, database service manager 1002, processing platform 1000 and / or any other components or systems discussed herein, or may be a part of them. As another example, components, systems or platforms discussed herein may include one or more computing devices 1500. In addition, computing device 1500 may interact with any of the systems and components described herein. Therefore, computing device 1500 may be used to perform various processes and tasks, such as those discussed herein. Computing device 1500 may be used as a server, client or any other computing entity. Computing device 1500 may be any of a variety of computing devices, such as a desktop computer, a notebook computer, a server computer, a handheld computer, a tablet computer, etc.

[0117] The computing device 1500 includes one or more processors 1502, one or more memory devices 1504, one or more interfaces 1506, one or more mass storage devices 1508, and one or more input / output (I / O) devices 1510, all coupled to a bus 1512. The processor 1502 includes one or more processors or controllers that execute instructions stored in the memory devices 1504 and / or the mass storage devices 1508. The processor 1502 may also include various types of computer-readable media, such as cache memory.

[0118] The storage device 1504 includes various computer-readable media, such as volatile memory (eg, random access memory (RAM)) and / or nonvolatile memory (eg, read-only memory (ROM)). The storage device 1504 may also include a rewritable ROM, such as a flash memory.

[0119] The mass storage device 1508 includes various computer-readable media, such as magnetic tapes, magnetic disks, optical disks, solid-state memory (e.g., flash memory), etc. Various drives may also be included in the mass storage device 1508 to enable reading and / or writing of various computer-readable media from various computer-readable media. The mass storage device 1508 includes removable media and / or non-removable media.

[0120] I / O devices 1510 include various devices that enable data and / or other information to be input to or retrieved from computing device 1500. Example I / O devices 1510 include a cursor control device, a keyboard, a keypad, a microphone, a monitor or other display device, a speaker, a printer, a network interface card, a modem, a lens, a CCD or other image capture device, and the like.

[0121] Interfaces 1506 include various interfaces that enable computing device 1500 to interact with other systems, devices, or computing environments. Example interfaces 1506 include any number of different network interfaces, such as interfaces to a local area network (LAN), a wide area network (WAN), a wireless network, and the Internet.

[0122] Bus 1512 enables processor 1502, memory device 1504, interface 1506, mass storage device 1508, and I / O device 1510 to communicate with each other and other devices or components coupled to bus 1512. Bus 1512 represents one or more of several types of bus structures, such as a system bus, a PCI bus, a USB bus, etc.

[0123] Example

[0124] The following examples relate to other implementations.

[0125] Example 1 is a method for scheduling tasks to be performed on a database. The method includes executing a transaction on a table including database data, wherein executing the transaction includes generating a new table version. The method includes: in response to the transaction being fully executed, generating a change tracking entry, the change tracking entry including an indication of one or more modifications made to the table by the transaction; and storing the change tracking entry in a change tracking stream. The method includes executing the task on the new table version in response to a triggering event.

[0126] Example 2 is a method according to Example 1, wherein the transaction includes ingesting new data into a table, and wherein the method further includes: storing the new table version in a staging table before executing the task on the new table version; and in response to the task being executed, transforming the new table version to one or more target tables.

[0127] Example 3 is a method according to any one of Examples 1 to 2, further comprising: advancing a stream offset in the change tracking stream in response to storing a change tracking entry in the change tracking stream, and wherein the triggering event is the advancement of the stream offset.

[0128] Example 4 is a method according to any one of Examples 1 to 3, wherein the triggering event includes one or more of the following items: a predefined time period, a threshold number of modifications to a table, storage of change tracking entries in a change tracking stream, or storage of change tracking entries in a change tracking stream and a predefined time period.

[0129] Example 5 is a method according to any one of Examples 1 to 4, wherein the table includes one or more micro-partitions storing database data, and wherein executing a transaction on the table includes generating at least one new micro-partition reflecting the transaction and removing at least one corresponding micro-partition of the one or more micro-partitions.

[0130] Example 6 is a method according to any one of Examples 1 to 5, wherein the transaction includes one or more of the following: inserting data into a table, deleting data in a table, or updating data in a table.

[0131] Example 7 is a method according to any one of Examples 1 to 6, wherein the change tracking entry further includes one or more of the following items: a timestamp of when the transaction was requested, a timestamp of when the transaction was fully executed, an identifier of the user or account requesting the transaction, or a minimum and maximum data value pair for the data inserted by the transaction.

[0132] Example 8 is a method according to any one of Examples 1 to 7, wherein the task includes user-defined logic, and the user-defined logic includes one or more structured query language (SQL) statements.

[0133] Example 9 is a method according to any one of Examples 1 to 8, wherein the change tracking stream is transactional, so that the change tracking stream is advanced only after the transaction is fully and successfully executed, and wherein the task is executed on the new table version in a transactional manner, so that the task is executed on the new table version only once.

[0134] Example 10 is a method according to any one of Examples 1 to 9, further comprising: in response to executing a task on a new table version, generating a task history entry, the task history entry comprising one or more of the following items: a task name, a task identifier, an execution timestamp indicating when the task was executed, an execution status indicating whether the task was successfully executed or whether an error was returned, a message including an error code in response to the task not being successfully executed, or one or more results returned by executing the task.

[0135] Example 11 is a method according to any one of Examples 1 to 10, further comprising retrieving a task from a database schema, wherein the task comprises one or more of the following items: a timestamp indicating when the task was received or generated, a task name, a database name indicating on which database the task should be executed, an identifier of the owner or creator of the task, a task schedule including a triggering event, a Structured Query Language (SQL) script, a last execution timestamp indicating the last time the task was executed, or a last execution status indicating whether the task was successfully run at the last time the task was executed.

[0136] Example 12 is a system for scheduling tasks to be performed on a database. The system includes means for performing a transaction on a table including database data, wherein performing the transaction includes generating a new table version. The system includes means for generating a change tracking entry in response to the transaction being fully executed, the change tracking entry including an indication of one or more modifications made to the table by the transaction. The system includes means for storing the change tracking entry in a change tracking stream. The system includes means for performing the task on the new table version in response to a triggering event.

[0137] Example 13 is a system according to Example 12, wherein the transaction includes ingesting new data into a table, and wherein the system further includes: a device for storing the new table version in a staging table before executing a task on the new table version; and a device for transforming the new table version to one or more target tables in response to the task being executed.

[0138] Example 14 is a system according to any one of Examples 12 to 13, further comprising means for advancing a stream offset in the change tracking stream in response to storing a change tracking entry in the change tracking stream, and wherein the triggering event is the advancement of the stream offset.

[0139] Example 15 is a system as described in any of Examples 12 to 14, wherein the triggering event includes one or more of the following: a predefined time period, a threshold number of modifications to a table, change tracking entries stored in a change tracking stream, or change tracking entries stored in a change tracking stream and a predefined time period.

[0140] Example 16 is a system according to any one of Examples 12 to 15, wherein the table includes one or more micro-partitions storing database data, and wherein the device for performing transactions on the table is configured to generate at least one new micro-partition reflecting the transaction and remove at least one corresponding micro-partition of the one or more micro-partitions.

[0141] Example 17 is a system as described in any one of Examples 12 to 16, wherein the change tracking stream is transactional such that the change tracking stream advances only after the transaction is fully and successfully executed, and wherein the task is executed on the new table version in a transactional manner such that the data accessed by the retrieval task is only once.

[0142] Example 18 is a non-transitory computer-readable storage medium storing instructions that, when executed by one or more processors, cause the one or more processors to perform the following operations: execute a transaction on a table comprising database data, wherein executing the transaction includes generating a new table version; in response to the transaction being fully executed, generate a change tracking entry, the change tracking entry including an indication of one or more modifications made to the table by the transaction; store the change tracking entry in a change tracking stream; and execute a task on the new table version in response to a triggering event.

[0143] Example 19 is a non-transitory computer-readable storage medium according to Example 18, wherein the transaction includes ingesting new data into a table, and wherein the instructions further cause one or more processors to perform the following operations: storing the new table version in a staging table before executing the task on the new table version; and in response to the task being executed, transforming the new table version to one or more target tables.

[0144] Example 20 is a non-transitory computer-readable storage medium according to any one of Examples 18 to 19, wherein the instructions further cause one or more processors to perform the following operations: advancing a stream offset in a change tracking stream in response to storing a change tracking entry in the change tracking stream, and wherein the triggering event is the advancement of the stream offset.

[0145] Example 21 is a non-transitory computer-readable storage medium according to any one of Examples 18 to 20, wherein the triggering event includes one or more of the following items: a predefined time period, a threshold number of modifications to a table, change tracking entries stored in a change tracking stream, or change tracking entries stored in the change tracking stream and a predefined time period.

[0146] Example 22 is a non-transitory computer-readable storage medium according to any of Examples 18 to 21, wherein the table includes one or more micro-partitions storing database data, and wherein the instructions cause the one or more processors to perform the following operations: perform a transaction on the table by generating at least one new micro-partition reflecting the transaction and removing at least one corresponding micro-partition of the one or more micro-partitions.

[0147] Example 23 is a non-transitory computer-readable storage medium according to any one of Examples 18 to 22, wherein the change tracking stream is transactional such that the change tracking stream advances only after the transaction is fully and successfully executed, and wherein the task is executed on the new table version in a transactional manner such that the task is executed only once on the new table version.

[0148] Example 24 is an apparatus comprising means for performing the method of any one of Examples 1 to 23.

[0149] Example 25 is a machine-readable storage device comprising machine-readable instructions, which, when executed, implement the method described in any one of Examples 1 to 23 or implement the apparatus described in any one of Examples 1 to 23.

[0150] The flowcharts and block diagrams herein illustrate the architecture, functions and operations of possible implementations of the systems, methods and computer program products according to various embodiments of the present disclosure. In this regard, each box in the flowchart or block diagram may represent a module, section or portion of a code, and the code includes one or more executable instructions for implementing a specified logical function. It will also be noted that each box of the block diagram and / or flowchart and the combination of boxes in the block diagram and / or flowchart can be implemented by a dedicated hardware-based system that performs a specified function or action, or by a combination of dedicated hardware and computer instructions. These computer program instructions can also be stored in a computer-readable medium, and can instruct a computer or other programmable data processing device to operate in a specific manner, so that the instructions stored in the computer-readable medium produce a product including an instruction method, and the instruction method implements the function / action specified in a flowchart and / or block diagram box or multiple flowcharts and / or block diagram boxes.

[0151] The systems and methods described herein use new data processing platforms, methods, systems, and algorithms to provide a flexible and scalable data warehouse. In some embodiments, the described systems and methods utilize a cloud infrastructure that supports cloud-based storage resources, computing resources, and the like. Example cloud-based storage resources provide large amounts of storage capacity available on demand at a low cost. In addition, these cloud-based storage resources may be fault-tolerant and highly scalable, which would be expensive to implement in private data storage systems. Example cloud-based computing resources are available on demand and can be priced based on the actual level of usage of the resources. Typically, the cloud infrastructure is dynamically deployed, reconfigured, and deactivated in a rapid manner.

[0152] In the described systems and methods, the data storage system utilizes a relational database based on SQL (Structured Query Language). However, these systems and methods are applicable to any type of database that uses any data storage architecture and uses any language to store and retrieve data within the database. The systems and methods described herein may also provide a multi-tenant system that supports isolation of computing resources and data between different customers / clients and between different users within the same customer / client.

[0153] Various technologies or some aspects or parts thereof can take the form of program code (i.e., instruction) embedded in tangible media (e.g., floppy disk, CD-ROM, hard disk drive, non-transient computer-readable storage medium or any other machine-readable storage medium), wherein, when program code is loaded into a machine such as a computer and executed by it, the machine becomes a device for practicing various technologies. In the case of executing program code on a programmable computer, a computing device may include a processor, a storage medium (including volatile and non-volatile memory and / or storage element) readable by a processor, at least one input device, and at least one output device. Volatile and non-volatile memory and / or storage element may be RAM, EPROM, flash drive, CD-ROM drive, magnetic hard disk drive or other media for storing electronic data. One or more programs that can implement or utilize various technologies described herein may use application programming interface (API), reusable controls, etc. Such programs may be implemented in high-level process or object-oriented programming languages ​​to communicate with a computer system. However, if necessary, programs may be implemented in assembly or machine language. In any case, language may be a compiled or interpreted language and may be combined with a hardware implementation.

[0154] It should be understood that many of the functional units described in this specification can be implemented as one or more components, and component is a term used to more specifically emphasize the independence of its implementation. For example, a component can be implemented as a hardware circuit including a custom very large scale integration (VLSI) circuit or gate array, a ready-made semiconductor such as a logic chip, a transistor, or other discrete components. Components can also be implemented in programmable hardware devices such as field programmable gate arrays, programmable array logic, programmable logic devices, etc.

[0155] Components may also be implemented in software for execution by various types of processors. The identified components of executable code may, for example, include one or more physical or logical blocks of computer instructions, which may, for example, be organized as objects, procedures, or functions. However, the executable files of the identified components need not be physically located together, but may include different instructions stored in different locations, which, when logically combined together, comprise the component and achieve the stated purpose of the component.

[0156] In fact, the components of executable code can be a single instruction or many instructions, and can even be distributed over several different code segments, distributed between different programs and distributed over several memory devices. Similarly, operational data can be identified and shown in this article within the component, and can be implemented in any suitable form and can be organized within any suitable type of data structure. Operational data can be collected as a single data set, or can be distributed in different locations, including distribution on different storage devices, and can exist at least in part as electronic signals on a system or network. Components can be passive or active, including agents that can be operated to perform desired functions.

[0157] References throughout the specification to "example" mean that a particular feature, structure, or characteristic described in conjunction with the example is included in at least one embodiment of the present disclosure. Therefore, the phrase "in an example" appearing in various places throughout the specification does not necessarily refer to the same embodiment.

[0158] As used herein, for convenience, multiple items, structural elements, constituent elements and / or materials can be presented in a public list. However, these lists should be interpreted as each member of the list being individually identified as a separate and unique member. Therefore, only based on its presentation in a public group without contrary indication, any single member of such a list should not be interpreted as being in fact equivalent to any other member of the same list. In addition, various embodiments and examples of the present disclosure and alternatives about its various components can be referenced herein. It should be understood that such embodiments, examples and alternatives should not be interpreted as being in fact equivalent to each other, but should be regarded as separate and autonomous representations of the present disclosure.

[0159] Although the foregoing has been described in detail for the sake of clarity, it will be apparent that certain changes and modifications may be made without departing from the principles thereof. It should be noted that there are many alternative ways to implement both the processes and the apparatus described herein. Therefore, the present embodiments should be considered to be illustrative rather than restrictive.

[0160] Those skilled in the art will appreciate that many changes may be made to the details of the above embodiments without departing from the basic principles of the present disclosure. Therefore, the scope of the present disclosure should be determined solely by the appended claims.

Claims

1. A method for scheduling tasks to be performed on database data, comprising: executing a transaction on a first table, the first table comprising database data, the execution of the transaction comprising generating a second table corresponding to a new version of the first table, the second table being a separate table relative to the first table; generating a change tracking entry after fully executing the transaction based at least in part on the second table, the second table being generated as the separate table, the change tracking entry including an indication of at least one modification made to the first table by execution of the transaction; advancing the change tracking stream at least in part by inputting the generated change tracking entry into the change tracking stream, the advancing of the change tracking stream comprising advancing a stream offset and a retention boundary in the change tracking stream, the stream offset corresponding to a first portion in the change tracking stream and the retention boundary corresponding to a second portion in the change tracking stream, the change tracking stream comprising a series of committed transactions; as well as In response to a triggering event including advancement of the change tracking flow, a predefined task is executed on the second table and a task history entry is generated indicating that the predefined task has been successfully executed.

2. The method according to claim 1, wherein: Executing the transaction on the first table includes ingesting new data into the first table; and The method further comprises: storing the new data in a staging table; as well as The new data in the staging table is transformed for storage in one or more target tables.

3. The method according to claim 1, wherein: Executing the transaction on the first table includes one or more of inserting data into the first table, deleting data from the first table, updating data in the first table, and merging data in the first table.

4. The method according to claim 1 or 2, wherein: The change tracking entry also includes a transaction timestamp associated with the execution of the transaction, and the change tracking stream is arranged in time order according to the transaction timestamp.

5. The method according to claim 4, wherein: The indication that at least one modification is made to the first table by the execution of the transaction indicates: any one or more micro-partitions added to the first table by execution of the transaction; any one or more micro-partitions removed from the first table by execution of the transaction; any one or more rows added to the first table by execution of the transaction; as well as Any one or more rows removed from the first table by execution of the transaction. 6 . The method of claim 5 , further comprising reading the change tracking stream to determine a delta for the first table between a first transaction timestamp and a second transaction timestamp.

7. The method of claim 5, further comprising executing a query on the change tracking stream to determine a comprehensive change tracking summary between a first transaction timestamp and a second transaction timestamp, the comprehensive change tracking summary indicating all intermediate modifications that occurred on the first table between the first transaction timestamp and the second transaction timestamp.

8. The method according to claim 1, wherein: The triggering event also includes the passage of a predefined time period.

9. The method according to claim 1, wherein: The triggering event also includes a threshold number of modifications having been made to the first table.

10. A system for scheduling tasks to be performed on database data, comprising: at least one processor; as well as One or more non-transitory computer-readable storage media containing instructions executable by the at least one processor to cause the at least one processor to perform operations comprising: executing a transaction on a first table, the first table comprising database data, the execution of the transaction comprising generating a second table corresponding to a new version of the first table, the second table being a separate table relative to the first table; generating a change tracking entry after fully executing the transaction based at least in part on the second table, the second table being generated as the separate table, the change tracking entry including an indication of at least one modification made to the first table by execution of the transaction; advancing the change tracking stream at least in part by inputting the generated change tracking entry into the change tracking stream, the advancing of the change tracking stream comprising advancing a stream offset and a retention boundary in the change tracking stream, the stream offset corresponding to a first portion in the change tracking stream and the retention boundary corresponding to a second portion in the change tracking stream, the change tracking stream comprising a series of committed transactions; and In response to a triggering event including advancement of the change tracking flow, a predefined task is executed on the second table and a task history entry is generated indicating that the predefined task has been successfully executed.

11. The system according to claim 10, wherein: Executing the transaction on the first table includes ingesting new data into the first table; and The operations also include: storing the new data in a staging table; as well as The new data in the staging table is transformed for storage in one or more target tables.

12. The system according to claim 10, wherein: Executing the transaction on the first table includes one or more of inserting data into the first table, deleting data from the first table, updating data in the first table, and merging data in the first table.

13. The system according to claim 10, wherein: The change tracking entry also includes a transaction timestamp associated with the execution of the transaction, and the change tracking stream is arranged in time order according to the transaction timestamp.

14. The system according to claim 13, wherein: The indication that at least one modification is made to the first table by the execution of the transaction indicates: any one or more micro-partitions added to the first table by execution of the transaction; any one or more micro-partitions removed from the first table by execution of the transaction; any one or more rows added to the first table by execution of the transaction; as well as Any one or more rows removed from the first table by execution of the transaction.

15. The system of claim 14, the operations further comprising reading the change tracking stream to determine a delta for the first table between a first transaction timestamp and a second transaction timestamp.

16. The system of claim 14, the operations further comprising performing a query on the change tracking stream to determine a comprehensive change tracking summary between a first transaction timestamp and a second transaction timestamp, the comprehensive change tracking summary indicating all intermediate modifications that occurred on the first table between the first transaction timestamp and the second transaction timestamp.

17. The system according to claim 10, wherein: Advancing the change tracking stream includes advancing a stream offset on the change tracking stream.

18. The system of claim 10, wherein: The triggering event also includes the passage of a predefined time period.

19. The system of claim 10, wherein: The triggering event also includes a threshold number of modifications having been made to the first table.

20. One or more non-transitory computer-readable storage media containing instructions that, when executed by at least one processor, cause the at least one processor to perform operations comprising: executing a transaction on a first table, the first table comprising database data, the execution of the transaction comprising generating a second table corresponding to a new version of the first table, the second table being a separate table relative to the first table; generating a change tracking entry after fully executing the transaction based at least in part on the second table, the second table being generated as the separate table, the change tracking entry including an indication of at least one modification to the first table by execution of the transaction; advancing the change tracking stream at least in part by inputting the generated change tracking entry into the change tracking stream, the advancing of the change tracking stream comprising advancing a stream offset and a retention boundary in the change tracking stream, the stream offset corresponding to a first portion in the change tracking stream and the retention boundary corresponding to a second portion in the change tracking stream, the change tracking stream comprising a series of committed transactions; as well as In response to a triggering event including advancement of the change tracking flow, a predefined task is executed on the second table and a task history entry is generated indicating that the predefined task has been successfully executed.

21. The non-transitory computer readable storage medium of claim 20, wherein: Executing the transaction on the first table includes ingesting new data into the first table; and The operations also include: storing the new data in a staging table; as well as The new data in the staging table is transformed for storage in one or more target tables.

22. The non-transitory computer readable storage medium of claim 20, wherein: Executing the transaction on the first table includes one or more operations of inserting data into the first table, deleting data from the first table, updating data in the first table, and merging data in the first table.

23. The non-transitory computer readable storage medium of claim 20, wherein: The change tracking entry also includes a transaction timestamp associated with the execution of the transaction, and the change tracking stream is arranged in time order according to the transaction timestamp.

24. The non-transitory computer readable storage medium of claim 23, wherein: The indication that at least one modification is made to the first table by the execution of the transaction indicates: any one or more micro-partitions added to the first table by execution of the transaction; any one or more micro-partitions removed from the first table by execution of the transaction; any one or more rows added to the first table by execution of the transaction; as well as Any one or more rows removed from the first table by execution of the transaction.

25. The non-transitory computer-readable storage medium of claim 24, the operations further comprising reading the change tracking stream to determine a delta between a first transaction timestamp and a second transaction timestamp for the first table.

26. The non-transitory computer-readable storage medium of claim 24, the operations further comprising performing a query on the change tracking stream to determine a comprehensive change tracking summary between a first transaction timestamp and a second transaction timestamp, the comprehensive change tracking summary indicating all intermediate modifications that occurred on the first table between the first transaction timestamp and the second transaction timestamp.

27. The non-transitory computer readable storage medium of claim 20, wherein: Advancing the change tracking stream includes advancing a stream offset on the change tracking stream.

28. The non-transitory computer readable storage medium of claim 20, wherein: The triggering event also includes the passage of a predefined time period.

29. The non-transitory computer readable storage medium of claim 20, wherein: The triggering event also includes a threshold number of modifications having been made to the first table.

Images