Tracking intermediate changes in database data

Patent metadata

Field	Value
Publication number	US-10977244-B2
Application number	US-201816182216-A
Country	US
Kind code	B2
Filing date	Nov 6, 2018
Priority date	Nov 6, 2018
Publication date	Apr 13, 2021
Grant date	Apr 13, 2021

Abstract

Systems, methods, and devices for tracking a series of changes to database data are disclosed. A method includes executing a transaction to modify data in a micro-partition of a table of a database by generating a new micro-partition that embodies the transaction. The method includes associating transaction data with the new micro-partition, wherein the transaction data comprises a timestamp when the transaction was fully executed, and further includes associating modification data with the new micro-partition that comprises an indication of one or more rows of the table that were modified by the transaction. The method includes joining the transaction data with the modification data to generate joined data and querying the joined data to determine a listing of intermediate modifications made to the table between a first timestamp and a second timestamp.

First claim

What is claimed is:

1. A method for tracking intermediate changes to database data, the method comprising:
executing a transaction to modify data in a micro-partition of a table of a database by generating a new micro-partition that embodies the transaction, the micro-partition including a plurality of rows and the new micro-partition includes a plurality of rows;
associating transaction data with the new micro-partition, wherein the transaction data comprises a timestamp that indicates when the transaction was fully executed;
associating modification data with the new micro-partition, the modification data comprising an indication of one or more rows of the table that were modified by the transaction, at least one row of the micro-partition being carried over to the new micro-partition with a new value;
joining the transaction data with the modification data to generate joined data stored as metadata with the new micro-partition, the joined data including information identifying the transaction as causing modification of the micro-partition and information regarding the modification to the micro-partition; and
querying the joined data to determine a listing of intermediate modifications made to the table between a first timestamp and a second timestamp.

2. The method of claim 1 , wherein querying the joined data comprises querying a plurality of entries of the joined data for a plurality of transactions executed on the table between the first timestamp and the second timestamp.

3. The method of claim 1 , wherein the transaction comprises one or more of: a delete command, an insert command, an update command, and a merge command.

4. The method of claim 1 , further comprising removing the micro-partition after the new micro-partition is fully generated.

5. The method of claim 1 , wherein associating the transaction data with the new micro-partition comprises inserting the transaction data in a transaction tracking column in the new micro-partition, and wherein associating the modification data with the new micro-partition comprises inserting the modification data in a change tracking column in the new micro-partition.

6. The method of claim 5 , wherein the modification data comprises one or more of:
a prior micro-partition identification for a value in the table, a prior row identification for a value in the table, a transaction that occurred on a row of the table, and an indication of whether a value of the table was updated.

7. The method of claim 1 , wherein querying the joined data comprises deriving tuple granularity changes between each of a series of sequential micro-partition pairs between the first timestamp and the second timestamp to determine a series of intermediate modifications made to the table between the first timestamp and the second timestamp, and wherein each of the series of sequential micro-partition pairs is ordered according to timestamps.

8. The method of claim 1 , wherein associating the transaction data with the new micro-partition occurs after the new micro-partition is fully generated and the transaction is fully executed.

9. The method of claim 1 , wherein the transaction modifies rows of the table that are stored across a plurality of micro-partitions of the table, and wherein executing the transaction comprises generating a plurality of new micro-partitions and removing the plurality of micro-partitions after the transaction is fully executed.

10. The method of claim 1 , further comprising generating a report comprising one or more of:
a listing of intermediate modifications made to the table between the first timestamp and the second timestamp;
an indication of when each of the intermediate modifications occurred;
an indication of what transaction caused each of the intermediate modifications;
an indication of what rows of the table were modified by a transaction that initiated a modification to the table; and
an indication of what user account initiated a transaction that initiated a modification to the table.

11. A system for tracking intermediate changes to database data, the system comprising:
means for executing a transaction to modify data in a micro-partition of a table of a database by generating a new micro-partition that embodies the transaction, the micro-partition including a plurality of rows and the new micro-partition includes a plurality of rows;
means for associating transaction data with the new micro-partition, wherein the transaction data comprises a timestamp that indicates when the transaction was fully executed;
means for associating modification data with the new micro-partition, the modification data comprising an indication of one or more rows of the table that were modified by the transaction, at least one row of the micro-partition being carried over to the new micro-partition with a new value;
means for joining the transaction data with the modification data to generate joined data stored as metadata with the new micro-partition, the joined data including information identifying the transaction as causing modification of the micro-partition and information regarding the modification to the micro-partition; and
means for querying the joined data to determine a listing of incremental modifications made to the table between a first timestamp and a second timestamp.

12. The system of claim 11 , wherein the means for querying the joined data is configured to query a plurality of entries of the joined data for a plurality of transactions executed on the table between the first timestamp and the second timestamp.

13. The system of claim 11 , further comprising means for removing the micro-partition from the database after the new micro-partition is fully generated.

14. The system of claim 11 , wherein the means for associating the transaction data with the new micro-partition is configured to insert the transaction data in a transaction tracking column in the new micro-partition, and wherein the means for associating the modification data with the new micro-partition is configured to insert the modification data in a change tracking column in the new micro-partition.

15. The system of claim 11 , wherein the means for querying the joined data is configured to derive tuple granularity changes between each of a series of sequential micro-partition pairs between the first timestamp and the second timestamp to determine a series of intermediate modifications made to the table between the first timestamp and the second timestamp, and wherein each of the series of sequential micro-partition pairs is ordered according to timestamps.

16. The system of claim 11 , further comprising means for generating a report comprising one or more of:
a listing of intermediate modifications made to the table between the first timestamp and the second timestamp;
an indication of when each of the intermediate modifications occurred;
an indication of what transaction caused each of the intermediate modifications;
an indication of what rows of the table were modified by a transaction that initiated a modification to the table; and
an indication of what user account initiated a transaction that initiated a modification to the table.

17. Non-transitory computer readable storage media containing instructions executable by a processor for performing a set of operations, the operations comprising:
executing a transaction to modify data in a micro-partition of a table of a database by generating a new micro-partition that embodies the transaction, the micro-partition including a plurality of rows and the new micro-partition includes a plur

Assignees

• Snowflake Inc

Inventors

• Grabs Torsten
• Cseri Istvan
• Dageville Benoit

Classifications

• G06F16/2379 Primary

  Updates performed during online database operations; commit processing · CPC title

• G06F16/219 Primary

  Managing data history or versioning (querying versioned data G06F16/2474; querying temporal data G06F16/2477) · CPC title

• G06F16/2456

  Join operations · CPC title

• G06F16/2282

  Tablespace storage structures; Management thereof · CPC title

• G06F16/24554

  Unary operations; Data partitioning operations · CPC title