Tracking changes in database data

Patent metadata

Field	Value
Publication number	US-10824617-B2
Application number	US-201916705424-A
Country	US
Kind code	B2
Filing date	Dec 6, 2019
Priority date	Nov 6, 2018
Publication date	Nov 3, 2020
Grant date	Nov 3, 2020

Abstract

Systems, methods, and devices for tracking changes to database data. A method includes determining a change to be executed on a micro-partition of a table of a database and executing the change on the table by generating a new micro-partition that embodies the change. The method includes updating a table history that includes a log of changes made to the table, wherein each change in the log of changes includes a timestamp, and wherein updating the table history includes inserting the change into the log of changes.

First claim

What is claimed is:

1. A method comprising:
executing 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 and deleting the existing micro-partition;
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;
receiving a request for a delta for the table between a first timestamp and a second timestamp;
responsively determining the delta based on the table history, the delta including information indicating at least one database operation that was performed to at least one row of a plurality of rows of the table, without including information as to intermediate changes made to at least one row of the plurality of rows of the table between the first timestamp and the second timestamp, that facilitates a reduction in storage of historical versions of the plurality of rows of the table; and
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.

2. The method of claim 1 , wherein generating the new micro-partition that embodies the change comprises:
copying one or more unmodified rows from the existing micro-partition along with change history information stored in association with one or more unmodified rows;
generating one or more new modified rows based on the change; and
generating a row identification for each of the one or more new modified rows.

3. The method of claim 1 , further comprising generating a row identification for each row of a plurality of rows of the table, wherein each row identification is stored in the log of changes in association with an applicable timestamp.

4. The method of claim 3 , wherein generating a row identification for each row of the plurality of rows of the table comprises:
for any row of the plurality of rows that is not modified by executing the change, deriving the row identification for an unmodified row based on a micro-partition and a row position of the unmodified row; and
for any row of the plurality of rows that is modified by executing the change, generating the row identification for an modified row in the new micro-partition that embodies the change.

5. The method of claim 1 , wherein the delta indicates changes between the first timestamp and the second timestamp to at least one micro-partition of the table.

6. The method of claim 1 , wherein the table history comprises information indicating a particular set of rows that were changed, and a particular micro-partition where a particular row from the particular set of rows was stored.

7. The method of claim 1 , wherein determining the delta based on the table history comprises determining the delta by querying the table history.

8. The method of claim 7 , wherein querying the table history comprises querying the log of changes.

9. The method of claim 1 , wherein:
the log of changes in the table history comprises a lineage for each of a plurality of rows in the table; and
the lineage for a given row comprises a log of modifications to the given row with a timestamp associated with each modification, the lineage for the given row further comprising a lineage of micro-partitions in which the given row has been stored and a lineage of ordinal numbers for the given row in the micro-partitions in which the given row has been stored.

10. The method of claim 1 , wherein:
the table history comprises one or more change tracking columns stored in a micro-partition; and
the one or more change tracking columns comprise data indicating one or more of:
an original micro-partition identification for a value;
an original row identification for a value;
a change action that occurred on a row, and
an indication of whether a value was updated.

11. 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 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 and deleting the existing micro-partition;
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;
receiving a request for a delta for the table between a first timestamp and a second timestamp;
responsively determining the delta based on the table history, the delta including information indicating at least one database operation that was performed to at least one row of a plurality of rows of the table, without including information as to intermediate changes made to at least one row of the plurality of rows of the table between the first timestamp and the second timestamp, that facilitates a reduction in storage of historical versions of the plurality of rows of the table; and
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 11 , wherein generating the new micro-partition that embodies the change comprises:
copying one or more unmodified rows from the existing micro-partition along with change history information stored in association with one or more unmodified rows;
generating one or more new modified rows based on the change; and
generating a row identification for each of the one or more new modified rows.

13. The system of claim 11 , the operations further comprising generating a row identification for each row of a plurality of rows of the table, wherein each row identification is stored in the log of changes in association with an applicable timestamp.

14. The system of claim 13 , wherein generating a row identification for each row of the plurality of rows of the table comprises:
for any row of the plurality of rows that is not modified by executing the change, deriving the row identification for an unmodified row based on a micro-partition and a row position of the unmodified row; and
for any row of the plurality of rows that is modified by executing the change, generating the row identification for a modified row in the new micro-partition that embodies the change.

15. The system of claim 11 , wherein the delta indicates changes between the first timestamp and the second timestamp to at least one micro-partition of the table.

16. The system of claim 11 , wherein the table history comprises information indicating a particular set of rows that were changed, and a particular micro-partition where a particular row from the particular set of rows was stored.

17. The system of claim 11 , wherein determining the delta based on the table history comprises determining the delta by querying the table history.

18. The system of claim 17 , wherein querying the table history comprises querying the log of changes.

19. The system of claim 11 , wherein:
the log of changes in the table history comprises a lineage for each of a plurality of rows in the table; and
the lineage for a given row comprises a log of modifications to the given row with a timestamp associated with each modi

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/278

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

• G06F16/2455

  Query execution · CPC title