Tracking intermediate changes in database data

Patent metadata

Field	Value
Publication number	US-11620281-B2
Application number	US-202217656960-A
Country	US
Kind code	B2
Filing date	Mar 29, 2022
Priority date	Nov 6, 2018
Publication date	Apr 4, 2023
Grant date	Apr 4, 2023

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:
receiving a data manipulation language (DML) command corresponding to a transaction to modify data in a table, the table including a first micro-partition associated with a first timestamp and including a first plurality rows;
executing the DML command to generate a second micro-partition associated with a second timestamp and including a second plurality of rows and to remove the first micro-partition from the table;
associating transaction data with the second micro-partition;
associating modification data with the second 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 first micro-partition being carried over to the second micro-partition with a new value;
joining the transaction data with the modification data to generate joined data stored as metadata with the second micro-partition; and
querying the joined data to determine a listing of intermediate modifications made to the table between the first timestamp and the second timestamp.

2. The method of claim 1 , further comprising:
determining tuple changes between each of a series of sequential micro-partition pairs between the first and second timestamps.

3. The method of claim 1 , wherein multiple modifications to a row of the first plurality of rows in the first micro-partition are performed and the joined data indicates at least one intermediate change to the row between initial and final values.

4. The method of claim 1 , wherein the transaction data includes one or more of: an identity of an account that initiated the transaction, the second time stamp, a third timestamp when the transaction was requested, a fourth timestamp when execution of the transaction began, a listing of all rows that were modified by the transaction, and details of the modifications.

5. The method of claim 1 , wherein the modification data is stored in the second micro-partition as metadata, wherein the modification data includes a lineage of modifications made to the table.

6. The method of claim 1 , wherein the transaction data and the modification data are stored in the second micro-partition as metadata in immutable storage.

7. The method of claim 1 , further comprising:
storing a delta table with final changes in the second micro-partition at the second timestamp from the first micro-partition at the first timestamp,
wherein the delta table stores an initial value of a row at the first timestamp and a final value of the row after performance of multiple modifications to the row at the second timestamp,
wherein the delta table includes information indicating which rows have been modified, final values of rows that have been modified between the first and second timestamps, and an action type of the modification for each row modified.

8. A system comprising:
one or more processors of a machine; and
a memory storing instructions that, when executed by the one or more processors, cause the machine to perform operations comprising:
receiving a data manipulation language (DML) command corresponding to a transaction to modify data in a table, the table including a first micro-partition associated with a first timestamp and including a first plurality rows;
executing the DML command to generate a second micro-partition associated with a second timestamp and including a second plurality of rows and to remove the first micro-partition from the table;
associating transaction data with the second micro-partition;
associating modification data with the second 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 first micro-partition being carried over to the second micro-partition with a new value;
joining the transaction data with the modification data to generate joined data stored as metadata with the second micro-partition; and
querying the joined data to determine a listing of intermediate modifications made to the table between the first timestamp and the second timestamp.

9. The system of claim 8 , the operations further comprising:
determining tuple changes between each of a series of sequential micro-partition pairs between the first and second timestamps.

10. The system of claim 8 , wherein multiple modifications to a row of the first plurality of rows in the first micro-partition are performed and the joined data indicates at least one intermediate change to the row between initial and final values.

11. The system of claim 8 , wherein the transaction data includes one or more of: an identity of an account that initiated the transaction, the second time stamp, a third timestamp when the transaction was requested, a fourth timestamp when execution of the transaction began, a listing of all rows that were modified by the transaction, and details of the modifications.

12. The system of claim 8 , wherein the modification data is stored in the second micro-partition as metadata, wherein the modification data includes a lineage of modifications made to the table.

13. The system of claim 8 , wherein the transaction data and the modification data are stored in the second micro-partition as metadata in immutable storage.

14. The system of claim 8 , the operations further comprising:
storing a delta table with final changes in the second micro-partition at the second timestamp from the first micro-partition at the first timestamp,

wherein the delta table stores an initial value of a row at the first timestamp and a final value of the row after performance of multiple modifications to the row at the second timestamp,
wherein the delta table includes information indicating which rows have been modified, final values of rows that have been modified between the first and second timestamps, and an action type of the modification for each row modified.

15. A non-transitory computer readable storage media storing instructions that, when executed by one or more processors, cause the one or more processors to:
receiving a data manipulation language (DML) command corresponding to a transaction to modify data in a table, the table including a first micro-partition associated with a first timestamp and including a first plurality rows;
executing the DML command to generate a second micro-partition associated with a second timestamp and including a second plurality of rows and to remove the first micro-partition from the table;
associating transaction data with the second micro-partition;
associating modification data with the second 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 first micro-partition being carried over to the second micro-partition with a new value;
joining the transaction data with the modification data to generate joined data stored as metadata with the second micro-partition; and
querying the joined data to determine a listing of intermediate modifications made to the table between the first timestamp and the second timestamp.

16. The non-transitory computer readable storage media of claim 15 , further comprising:
determining tuple changes between each of a series of sequential micro-partition pairs between the first and second timestamps.

17. The non-transitory computer readable storage media of claim 15 , wherein multiple modifications to a row of the first plural

Assignees

• Snowflake Inc

Inventors

• Grabs Torsten
• Cseri Istvan
• Dageville Benoit

Classifications

• G06F16/2456

  Join operations · CPC title

• G06F16/24554

  Unary operations; Data partitioning 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

• G06F16/219 Primary

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