Tracking lineage of database data

Patent metadata

Field	Value
Publication number	US-11645262-B2
Application number	US-202117162790-A
Country	US
Kind code	B2
Filing date	Jan 29, 2021
Priority date	Nov 6, 2018
Publication date	May 9, 2023
Grant date	May 9, 2023

Abstract

The subject technology executes a change on an existing micro-partition of a table of a database, the executing of the change comprising generating a new micro-partition that embodies the change. The subject technology receives a request for a delta for the table between a first timestamp and a second timestamp. The subject technology queries at least one change tracking column to determine the delta between the first timestamp and the second timestamp, the delta including information indicating at least one database operation that was performed to at least one row of a set of rows of the table, without including information as to intermediate changes made to at least one row of the set of rows of the table between the first timestamp and the second timestamp, that facilitates a reduction in storage of historical versions of the set of rows of the table.

First claim

What is claimed is:

1. A method comprising:
executing, by at least one hardware processor of a computing node, a change on a first immutable micro-partition of a table of a database, the executing of the change comprising generating a new micro-partition that embodies the change and deleting the first immutable micro-partition;
storing, by the at least one hardware processor, a value of the first immutable micro-partition prior to executing the change in a change tracking column of the table;
receiving, by the at least one hardware processor, a request for a delta for the table between a first timestamp and a second timestamp, the request received from a request processing service executing by the computing node; and
querying, by the at least one hardware processor, at least one change tracking column of the table to determine the delta between the first timestamp and the second timestamp, the delta including the value of the first immutable micro-partition prior to executing the change and further indicating at least one operation performed on at least one row of the table, that facilitates a reduction in storage of historical versions of the at least one row; and
providing, by the at least one hardware processor, access to a storage location storing the delta for the table with the at least one operation performed on the at least one row to the request processing service executing by the computing node.

2. The method of claim 1 , further comprising:
updating a table history that comprises a log of changes made to the table, each change in the log of changes comprising a timestamp, the updating of the table history comprising inserting an indication of the change into the log of changes.

3. The method of claim 2 , further comprising:
providing the delta in response to the request for the delta, the delta indicating that a transaction based on the updated table history has completed.

4. The method of claim 1 , further comprising:
updating one or more change tracking columns in the new micro-partition.

5. The method of claim 4 , wherein the one or more change tracking columns indicate one or more of:
a prior micro-partition associated with a row in the new micro-partition, a prior row identification associated with a row in the new micro-partition, or a prior transaction associated with a row in the new micro-partition.

6. The method of claim 1 , further comprising:
comparing a first set of data associated with the first timestamp and a second set of data associated with the second timestamp, wherein the comparing comprises determining row granularity changes between added and removed micro-partitions between the first set of data and the second set of data.

7. The method of claim 1 , wherein the at least one change tracking column indicates a most recent change that occurred on a row, or a log of changes that have occurred on the table.

8. The method of claim 1 , further comprising:
determining a timestamp to a transaction that occurred on the table; and
determining a timestamp to each modification that occurs on one or more rows of the table.

9. A system comprising:
at least one processor; and
one or more non-transitory computer readable storage media containing instructions executable by the at least one processor for causing the at least one processor to perform operations comprising:
executing a change on a first immutable micro-partition of a table of a database, the executing of the change comprising generating a new micro-partition that embodies the change and deleting the first immutable micro-partition;
storing a value of the first immutable micro-partition prior to executing the change in a change tracking column of the table;
receiving a request for a delta for the table between a first timestamp and a second timestamp, the request received from a request processing service executing by a computing node; and
querying at least one change tracking column of the table to determine the delta between the first timestamp and the second timestamp, the delta including the value of the first immutable micro-partition prior to executing the change and further indicating at least one operation performed on at least one row of the table, that facilitates a reduction in storage of historical versions of the at least one row; and
providing access to a storage location storing the delta for the table with the at least one operation performed on the at least one row to the request processing service.

10. The system of claim 9 , wherein the operations further comprise:
updating a table history that comprises a log of changes made to the table, each change in the log of changes comprising a timestamp, the updating of the table history comprising inserting an indication of the change into the log of changes.

11. The system of claim 10 , wherein the operations further comprise:
providing the delta in response to the request for the delta, the delta indicating that a transaction based on the updated table history has completed.

12. The system of claim 10 , wherein the table history is stored within an immutable micro-partition.

13. The system of claim 11 , wherein the operations further comprise:
updating one or more change tracking columns in the table indicating the value of the first immutable micro-partition has been accessed.

14. The system of claim 13 , wherein the one or more change tracking columns indicate one or more of:
a prior micro-partition associated with a row in the new micro-partition, a prior row identification associated with a row in the new micro-partition, or a prior transaction associated with a row in the new micro-partition.

15. The system of claim 9 , wherein the operations further comprise:
comparing a first set of data associated with the first timestamp and a second set of data associated with the second timestamp, wherein the comparing comprises determining row granularity changes between added and removed micro-partitions between the first set of data and the second set of data.

16. The system of claim 9 , wherein the at least one change tracking column indicates a most recent change that occurred on a row, or a log of changes that have occurred on the table.

17. The system of claim 9 , wherein the operations further comprise:
determining a timestamp to a transaction that occurred on the table; and
determining a timestamp to each modification that occurs on one or more rows of the table.

18. A non-transitory computer readable storage media containing instructions executable by at least one processor for causing the at least one processor to perform operations comprising:
executing a change on a first immutable micro-partition of a table of a database, the executing of the change comprising generating a second immutable micro-partition that embodies the change and deleting the first immutable micro-partition;
storing a value of the first immutable micro-partition prior to executing the change in a change tracking column of the table;
receiving, from a request processing service executing by a computing node, a request for a delta for the table between a first timestamp and a second timestamp; and
querying at least one change tracking column of the table to determine the delta between the first timestamp and the second timestamp, the delta including the value of the first immutable micro-partition prior to executing the change and further indicating at least one operation performed on at least one row of the table, that facilitates a reduction in storage of historical versions of the at least o

Assignees

• Snowflake Inc

Inventors

• Cseri Istvan
• Grabs Torsten
• Dageville Benoit

Classifications

• G06F16/2358 Primary

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

• G06F16/2455

  Query execution · CPC title

• G06F16/278

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