Tracking Intermediate Changes In Database Data

Patent metadata

Field	Value
Publication number	US-2020142987-A1
Application number	US-201816182216-A
Country	US
Kind code	A1
Filing date	Nov 6, 2018
Priority date	Nov 6, 2018
Publication date	May 7, 2020
Grant date	—

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

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; 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; means for joining the transaction data with the modification data to generate joined data; 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; 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; 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.

18 . The non-transitory computer readable storage media of claim 17 , the operations further comprising removing the micro-partition from the database after the new micro-partition is fully generated.

19 . The non-transitory computer readable storage media of claim 17 , 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.

20 . The non-transitory computer readable storage media of claim 17 , 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.

21 . The non-transitory computer readable storage media of claim 17 , the operations 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 ta

Assignees

• Snowflake Inc

Inventors

• Grabs Torsten
• Cseri Istvan
• Dageville Benoit

Classifications

• G06F16/24554

  Unary operations; Data partitioning operations · CPC title

• G06F16/2456

  Join operations · CPC title

• G06F16/2379 Primary

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

• G06F16/2282

  Tablespace storage structures; Management thereof · CPC title

• G06F17/30498

  Physics · mapped topic