Tracking changes in database data

Patent metadata

Field	Value
Publication number	US-12561308-B2
Application number	US-202318520845-A
Country	US
Kind code	B2
Filing date	Nov 28, 2023
Priority date	Nov 6, 2018
Publication date	Feb 24, 2026
Grant date	Feb 24, 2026

Abstract

A method includes detecting, by at least one hardware processor, a change request for a table of a database, the table comprising a plurality of micro-partitions. A transaction associated with the change request is executed at a first timestamp. The transaction causes replacement of a first micro-partition of the plurality of micro-partitions with a second micro-partition. A change tracking column is generated in the second micro-partition. The change tracking column comprises metadata for the transaction. A delta for the table between the first timestamp and a second timestamp is generated using the metadata in the change tracking column. The delta indicates changes made to one or more rows of the table between the first time stamp and the second timestamp.

First claim

The invention claimed is:

1 . A method comprising:
detecting, by at least one hardware processor of a computing node, a change request for a table of a database, the table comprising a plurality of micro-partitions; executing at a first timestamp, a transaction on a first micro-partition of the plurality of micro-partitions, the transaction associated with the change request; replacing at the computing node, the first micro-partition with a second micro-partition of the plurality of micro-partitions based on the executing of the transaction on the first micro-partition; generating a change tracking column stored in the second micro-partition, the change tracking column comprising metadata for the transaction; generating a delta for the table between the first timestamp and a second timestamp using the metadata in the change tracking column of the second micro-partition, the delta indicating a completion status of the transaction and changes made to one or more rows of the table between the first timestamp and the second timestamp, the completion status being based on the transaction being fully completed; and generating, by the at least one hardware processor of the computing node, an output with the delta in response to the change request.

2 . The method of claim 1 , further comprising:
executing a plurality of changes on the table in response to the transaction, the plurality of changes occurring at a corresponding plurality of timestamps and resulting in a second plurality of micro-partitions of the table, the second plurality of micro-partitions including the second micro-partition.

3 . The method of claim 2 , further comprising:
configuring the delta to indicate a total change occurring to the table between a first change of the plurality of changes corresponding to the first timestamp and a second change of the plurality of changes corresponding to the second timestamp.

4 . The method of claim 2 , further comprising:
determining a sequence of dependencies between the first timestamp and the second timestamp, the sequence of dependencies associated with a subset of the plurality of changes occurring between the first timestamp and the second timestamp, wherein the sequence of dependencies further indicates one or more rows of a set of rows of the database table that the subset of the plurality of changes were applied to.

5 . The method of claim 4 , further comprising:
generating the delta based on the sequence of dependencies, the generating performed without including information as to one or more intermediate changes of the subset of the plurality of changes occurring between the first timestamp and the second timestamp.

6 . The method of claim 1 , further comprising:
configuring the metadata to include a row identification for each row of the one or more rows of the table changed between the first timestamp and the second timestamp, wherein the row identification is stored with a corresponding timestamp indicating when the row of the one or more rows was changed.

7 . The method of claim 6 , wherein generating the row identification for each row of the one or more rows of the table comprises:
for any unmodified row of the one or more rows of the table, deriving the row identification for the unmodified row based on a micro-partition of the plurality of micro-partitions and a row position in which row data in the unmodified row is stored; and for any modified row of the one or more rows of the table that is modified by executing the transaction, generating the row identification for the modified row in a new micro-partition that embodies the change.

8 . The method of claim 1 , further comprising:
configuring the metadata to include a first data set with table version information and a second data set with file information.

9 . The method of claim 8 , further comprising:
configuring the table version information to include a mapping of table versions to lists of added files and removed files during the execution of the transaction.

10 . The method of claim 9 , further comprising:
configuring the file information to include information about each micro-partition of the plurality of micro-partitions affected by the execution of the transaction, including one or more of: micro-partition path, micro-partition size, micro-partition key ID, and summaries of rows and columns that are stored in the micro-partition.

11 . A system comprising:
at least one hardware processor of a computing node; and at least one memory storing instructions that cause the at least one hardware processor to perform operations comprising:
detecting at the computing node, a change request for a table of a database, the table comprising a plurality of micro-partitions;
executing at a first timestamp, a transaction on a first micro-partition of the plurality of micro-partitions, the transaction associated with the change request;
replacing at the computing node, the first micro-partition with a second micro-partition of the plurality of micro-partitions based on the executing of the transaction on the first micro-partition;
generating a change tracking column stored in the second micro-partition, the change tracking column comprising metadata for the transaction;
generating a delta for the table between the first timestamp and a second timestamp using the metadata in the change tracking column of the second micro-partition, the delta indicating a completion status of the transaction and changes made to one or more rows of the table between the first timestamp and the second timestamp, the completion status being based on the transaction being fully completed; and
generating an output with the delta in response to the change request.

12 . The system of claim 11 , the operations further comprising:
executing a plurality of changes on the table in response to the transaction, the plurality of changes occurring at a corresponding plurality of timestamps and resulting in a second plurality of micro-partitions of the table, the second plurality of micro-partitions including the second micro-partition.

13 . The system of claim 12 , the operations further comprising:
configuring the delta to indicate a total change occurring to the table between a first change of the plurality of changes corresponding to the first timestamp and a second change of the plurality of changes corresponding to the second timestamp.

14 . The system of claim 12 , the operations further comprising:
determining a sequence of dependencies between the first timestamp and the second timestamp, the sequence of dependencies associated with a subset of the plurality of changes occurring between the first timestamp and the second timestamp, wherein the sequence of dependencies further indicates one or more rows of a set of rows of the database table that the subset of the plurality of changes were applied to.

15 . The system of claim 14 , the operations further comprising:
generating the delta based on the sequence of dependencies, the generating performed without including information as to one or more intermediate changes of the subset of the plurality of changes occurring between the first timestamp and the second timestamp.

16 . The system of claim 11 , the operations further comprising:
configuring the metadata to include a row identification for each row of the one or more rows of the table changed between the first timestamp and the second timestamp, wherein the row identification is stored with a corresponding timestamp indicating when the row of the one or more rows was changed.

Assignees

• Snowflake Inc

Inventors

• Cseri Istvan
• Grabs Torsten
• Dageville Benoit

Classifications

• G06F16/2455

  Query execution · CPC title

• G06F16/278

  Data partitioning, e.g. horizontal or vertical partitioning · CPC title

• G06F16/2358 Primary

  Change logging, detection, and notification (replication G06F16/27) · CPC title