Table data processing using a change tracking column

Patent metadata

Field	Value
Publication number	US-11928098-B2
Application number	US-202217809203-A
Country	US
Kind code	B2
Filing date	Jun 27, 2022
Priority date	Dec 7, 2018
Publication date	Mar 12, 2024
Grant date	Mar 12, 2024

Abstract

A system includes one or more processors and data storage containing instructions executable by the one or more processors to perform operations. The operations include detecting a first executed transaction causing a first modification to table data stored in a table. The table data is associated with a corresponding metadata file with metadata information of the table. A new metadata file is generated responsive to the first executed transaction. The new metadata file includes the metadata information and additional metadata associated with the first modification. A second executed transaction causing a second modification to the table data is detected. The table data is updated with a change tracking column. The change tracking column includes lineage of executed transactions on the table data. The lineage indicates at least the first transaction and the second transaction.

First claim

What is claimed is:

1. A method comprising:
detecting, by at least one hardware processor, a first executed transaction causing a first modification to table data stored in a table, the table data associated with a corresponding metadata file with metadata information of the table;
generating a new metadata file responsive to the first executed transaction, the new metadata file being distinct from the metadata file, and the new metadata file including the metadata information and additional metadata associated with the first modification;
detecting a second executed transaction causing a second modification to the table data; and
updating the table data with a change tracking column, the change tracking column including lineage of executed transactions on the table data, the lineage indicating at least the first transaction and the second transaction, timestamps when the first and second transactions were executed, identification information of at least one user account initiating the first and second transactions.

2. The method of claim 1 , further comprising:
configuring the table data in at least a first partition of a plurality of partitions of a storage device; and
configuring the metadata file in at least a second partition of the plurality of partitions.

3. The method of claim 2 , further comprising:
updating the metadata file to include the metadata information of the table and transactional metadata associated with at least one additional transaction executed on the table data prior to the first executed transaction.

4. The method of claim 3 , wherein the transactional metadata includes information on at least one addition or at least one deletion performed on the table data when the at least one additional transaction is executed.

5. The method of claim 1 , further comprising:
updating the change tracking column with a plurality of change tracking entries associated with the executed transactions.

6. The method of claim 5 , wherein updating the change tracking column comprises:
recording a first change tracking entry of the plurality of change tracking entries, the first change tracking entry indicating one or both of at least a first deletion and at least a first addition to the table data as a result of the first modification.

7. The method of claim 6 , further comprising:
storing transaction data for the first modification in the first change tracking entry, the transaction data comprising one or more of: time of initiating performing the at least a first deletion and the at least a first addition, time of completion of the performing of the at least a first deletion and the at least a first addition, and identification of rows in the table data affected by the performing of the at least a first deletion and the at least a first addition.

8. The method of claim 6 , wherein updating the change tracking column comprises:
recording a second change tracking entry of the plurality of change tracking entries, the second change tracking entry indicating one or both of at least a second deletion and at least a second addition to the table data as a result of the second modification.

9. The method of claim 8 , further comprising:
storing transaction data for the second modification in the second change tracking entry, the transaction data comprising one or more of: time of initiating performing the at least a second deletion and the at least a second addition, time of completion of the performing of the at least a second deletion and the at least a second addition, and identification of rows in the table data affected by the performing of the at least a second deletion and the at least a second addition.

10. The method of claim 2 , further comprising:
configuring the plurality of partitions as immutable storage partitions associated with the storage device,
wherein the executed transactions comprise one or more of an insert, a delete, an update, and a merge of the table data.

11. A system, comprising:
one or more processors; and
data storage containing instructions executable by the one or more processors to perform operations comprising:
detecting a first executed transaction causing a first modification to table data stored in a table, the table data associated with a corresponding metadata file with metadata information of the table;
generating a new metadata file responsive to the first executed transaction, the new metadata file being distinct from the metadata file, and the new metadata file including the metadata information and additional metadata associated with the first modification;
detecting a second executed transaction causing a second modification to the table data; and
updating the table data with a change tracking column, the change tracking column including lineage of executed transactions on the table data, the lineage indicating at least the first transaction, the second transaction, timestamps when the first and second transactions were executed, and identification information of at least one user account initiating the first and second transactions.

12. The system of claim 11 , the operations further comprising:
configuring the table data in at least a first partition of a plurality of partitions of a storage device; and
configuring the metadata file in at least a second partition of the plurality of partitions.

13. The system of claim 12 , the operations further comprising:
updating the metadata file to include the metadata information of the table and transactional metadata associated with at least one additional transaction executed on the table data prior to the first executed transaction.

14. The system of claim 13 , wherein the transactional metadata includes information on at least one addition or at least one deletion performed on the table data when the at least one additional transaction is executed.

15. The system of claim 11 , the operations further comprising:
updating the change tracking column with a plurality of change tracking entries associated with the executed transactions.

16. The system of claim 15 , wherein the operations for updating the change tracking column comprise:
recording a first change tracking entry of the plurality of change tracking entries, the first change tracking entry indicating one or both of at least a first deletion and at least a first addition to the table data as a result of the first modification.

17. The system of claim 16 , the operations further comprising:
storing transaction data for the first modification in the first change tracking entry, the transaction data comprising one or more of: time of initiating performing the at least a first deletion and the at least a first addition, time of completion of the performing of the at least a first deletion and the at least a first addition, and identification of rows in the table data affected by the performing of the at least a first deletion and the at least a first addition.

18. The system of claim 16 , wherein the operations for updating the change tracking column comprise:
recording a second change tracking entry of the plurality of change tracking entries, the second change tracking entry indicating one or both of at least a second deletion and at least a second addition to the table data as a result of the second modification.

19. The system of claim 18 , the operations further comprising:
storing transaction data for the second modification in the second change tracking entry, the transaction data comprising one or more of: time of initiating performing the at le

Assignees

• Snowflake Inc

Inventors

• Muralidhar Subramanian
• Cseri Istvan
• Grabs Torsten
• Dageville Benoit

Classifications

• G06F16/2358 Primary

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

• G06F16/1865

  Transactional file systems · CPC title

• G06F16/2322

  using timestamps · CPC title

• G06F16/2365

  Ensuring data consistency and integrity · CPC title

• G06F16/245

  Query processing · CPC title