Tracking changes in database data

Patent metadata

Field	Value
Publication number	US-11874818-B2
Application number	US-202217660132-A
Country	US
Kind code	B2
Filing date	Apr 21, 2022
Priority date	Nov 6, 2018
Publication date	Jan 16, 2024
Grant date	Jan 16, 2024

Abstract

A method includes partitioning a database table into a first plurality of partitions. A plurality of changes are executed on the database table in response to a transaction. The changes occur at a corresponding plurality of timestamps and result in a second plurality of partitions. The database table is updated to include a log with the plurality of changes. For each change of the plurality of changes, the log includes and an identification of a portion of the database table the change is applied to. A selection of a first timestamp and a second timestamp of the plurality of timestamps is detected. A delta is generated in response to the selection. The delta indicates a total change occurring to the database 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.

First claim

What is claimed is:

1. A method comprising:
partitioning, by at least one hardware processor of a first computing node, a database table into a first plurality of partitions;
executing, by the at least one hardware processor, a plurality of changes on the database table in response to a transaction, the plurality of changes occurring at a corresponding plurality of timestamps and resulting in a second plurality of partitions of the database table;
updating, by the at least one hardware processor, the database table to include a log with the plurality of changes, for each change of the plurality of changes, the log including an identification of a portion of the database table modified based on execution of the change;
detecting, by the at least one hardware processor, a selection of a first timestamp and a second timestamp of the corresponding plurality of timestamps, the selection received at the first computing node;
generating, by the at least one hardware processor, a delta based on the log and in response to the selection, the delta being separate from the second plurality of partitions and indicating a total change occurring to the database 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; and
providing the delta to at least a second computing node.

2. The method of claim 1 , 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.

3. The method of claim 2 , wherein the sequence of dependencies further indicates one or more rows of a set of rows of the database table that are modified based on execution of the subset of the plurality of changes.

4. The method of claim 2 , 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.

5. The method of claim 1 , further comprising:
configuring the delta to further identify at least a first one of the second plurality of partitions generated due to the first change and at least a second one of the second plurality of partitions generated due to the second change.

6. The method of claim 1 , further comprising:
updating the database table to include the log with the plurality of changes when the transaction is completed.

7. The method of claim 6 , further comprising:
storing the log with the plurality of changes as metadata within a column of the database table.

8. The method of claim 1 , wherein the first plurality of partitions comprises at least one existing partition, the second plurality of partitions comprises at least one new partition, and the method further comprises:
generating the delta based on performing a comparison operation between the at least one new partition and the at least one existing partition.

9. The method of claim 8 , wherein performing the comparison operation comprises at least one of:
generating a first null value indicating that one or more rows were deleted from the database table;
generating a second null value indicating that the one or more rows were inserted into the database table; and
generating a third null value indicating that the one or more rows were updated within the database table.

10. A system comprising:
at least one hardware processor of a first computing node; and
at least one memory storing instructions that cause the at least one hardware processor to perform operations comprising:
partitioning a database table into a first plurality of partitions;
executing a plurality of changes on the database table in response to a transaction, the plurality of changes occurring at a corresponding plurality of timestamps and resulting in a second plurality of partitions of the database table;
updating the database table to include a log with the plurality of changes, for each change of the plurality of changes, the log including an identification of a portion of the database table modified based on execution of the change;
detecting a selection of a first timestamp and a second timestamp of the corresponding plurality of timestamps, the selection received at the first computing node;
generating a delta based on the log and in response to the selection, the delta being separate from the second plurality of partitions and indicating a total change occurring to the database 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; and

providing the delta to at least a second computing node.

11. The system of claim 10 , wherein the at least one hardware processor further performs operations 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.

12. The system of claim 11 , wherein the sequence of dependencies further indicates one or more rows of a set of rows of the database table that are modified based on execution of the subset of the plurality of changes.

13. The system of claim 11 , wherein the at least one hardware processor further performs operations 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.

14. The system of claim 10 , wherein the at least one hardware processor further performs operations comprising:
configuring the delta to further identify at least a first one of the second plurality of partitions generated due to the first change and at least a second one of the second plurality of partitions generated due to the second change.

15. The system of claim 10 , wherein the at least one hardware processor further performs operations comprising:
updating the database table to include the log with the plurality of changes when the transaction is completed.

16. The system of claim 15 , wherein the at least one hardware processor further performs operations comprising:
storing the log with the plurality of changes as metadata within a column of the database table.

17. The system of claim 10 , wherein the first plurality of partitions comprises at least one existing partition, the second plurality of partitions comprises at least one new partition, and the operations further comprising:
generating the delta based on performing a comparison operation between the at least one new partition and the at least one existing partition.

18. The system of claim 17 , wherein to perform the comparison operation, the at least one hardware processor further performs operations comprising:
generating a first null value indicating that one or more rows were deleted from the database table;
generating a second null value indicating that the one or more rows were inserted into the database table; and
generating a third null value indicating that the one or more rows were updated within the database table.

19. A computer-storage medium com

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