Dynamic lineage tracking of database data
The subject technology executes a change on an existing micro-partition of a table, the executing of the change comprising generating a new micro-partition that embodies the change and deleting the existing micro-partition. The subject technology receives a request for a delta for the table between a first timestamp and a second timestamp. The subject technology responsively determines the delta based on a table history, the delta including information indicating at least one 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.
1. A method comprising:
executing a change on an existing micro-partition of a table, the executing of the change comprising generating a new micro-partition that embodies the change and deleting the existing micro-partition from the table;
receiving a request for a delta for the table between a first timestamp and a second timestamp;
determining in response to the request for the delta, a sequence of dependencies between the first timestamp and the second timestamp, the sequence of dependencies indicating one or more rows of a set of rows of the table that have been updated and one or more transactions that caused each update;
determining the delta based on the sequence of dependencies, the delta including information indicating at least one operation that was performed to at least one row of the set of rows of the table, without including information as to intermediate changes made to the at least one row of the set of rows of the table between the first timestamp and the second timestamp; and
providing an output with the delta, the delta further indicating completion status of a transaction of the one or more transactions associated with executing the change.
2. The method of claim 1 , further comprising:
subsequent to executing the change, 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 , wherein the timestamp for each change in the log of changes indicates when a corresponding change was made.
4. The method of claim 2 , wherein the log of changes includes historical data indicating: one or more of: rows that changed, a micro-partition where a row was originally stored, a prior row identifier for the row, or whether a row in the table was updated.
5. The method of claim 1 , wherein the existing micro-partition is a first existing micro-partition in a plurality of existing micro-partitions and the new micro-partition is a first new micro-partition in a plurality of new micro-partitions, wherein determining the delta further comprises performing a comparison operation between one or more existing micro-partitions and one or more new micro-partitions.
6. The method of claim 5 , wherein performing the comparison operation generates one or more null values indicating that one or more rows were deleted from the table.
7. The method of claim 5 , wherein performing the comparison operation generates one or more null values indicating that one or more rows were inserted into the table.
8. The method of claim 5 , wherein performing the comparison operation generates one or more null values indicating that one or more rows of the table were updated.
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 an existing micro-partition of a table, the executing of the change comprising generating a new micro-partition that embodies the change and deleting the existing micro-partition from the table;
receiving a request for a delta for the table between a first timestamp and a second timestamp;
determining in response to the request for the delta, a sequence of dependencies between the first timestamp and the second timestamp, the sequence of dependencies indicating one or more rows of a set of rows of the table that have been updated and one or more transactions that caused each update;
determining the delta based on the sequence of dependencies, the delta including information indicating at least one operation that was performed to at least one row of the set of rows of the table, without including information as to intermediate changes made to the at least one row of the set of rows of the table between the first timestamp and the second timestamp; and
providing an output with the delta, the delta further indicating completion status of a transaction of the one or more transactions associated with executing the change.
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 timestamp indicates a transaction that initiated a corresponding change.
12. The system of claim 11 , wherein the log of changes include historical data indicating one or more of: rows that changed, a micro-partition where a row was originally stored, a prior row identifier for the row, or whether a row in the table was updated.
13. The system of claim 9 , wherein the existing micro-partition is a first existing micro-partition in a plurality of existing micro-partitions and the new micro-partition is a first new micro-partition in a plurality of new micro-partitions, wherein determining the delta further comprises performing a comparison operation between one or more existing micro-partitions and one or more new micro-partitions.
14. The system of claim 13 , wherein performing the comparison operation generates one or more null values indicating that one or more rows were deleted from the table.
15. The system of claim 13 , wherein performing the comparison operation generates one or more null values indicating that one or more rows were inserted into the table.
16. The system of claim 13 , wherein performing the comparison operation generates one or more null values indicating that one or more rows of the table were updated.
17. 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 an existing micro-partition of a table, the executing of the change comprising generating a new micro-partition that embodies the change and deleting the existing micro-partition from the table;
receiving a request for a delta for the table between a first timestamp and a second timestamp;
determining in response to the request for the delta, a sequence of dependencies between the first timestamp and the second timestamp, the sequence of dependencies indicating one or more rows of a set of rows of the table that have been updated and one or more transactions that caused each update;
determining the delta based on the sequence of dependencies, the delta including information indicating at least one operation that was performed to at least one row of the set of rows of the table, without including information as to intermediate changes made to the at least one row of the set of rows of the table between the first timestamp and the second timestamp; and
providing an output with the delta, the delta further indicating completion status of a transaction of the one or more transactions associated with executing the change.
18. The non-transitory computer readable storage media of claim 17 , the operations further comprising:
subsequent to executing the change, 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.
19. The non-transitory computer readable storage media of claim 18 , wherein the log of changes includes historical data indicating one or more of: rows that changed, a micro-partition where a row was originally stored, a prior row identifier for the row, or an indication of whether the row was updated.
20. The non-transitory computer readable storage media of claim 17 , wherein the timestamp indicates a transaction that initiated a corresponding change.
21. The non-transitory computer readable storage media of claim 17 , wherein the existing micro-partition is a first existing micro-partition in a plurality of existing micro-partitions and the new micro-partition is a first new micro-partition in a plurality of new micro-partitions, wherein determining the delta further comprises performing a comparison operation between one or more existing micro-partitions and one or more new micro-partitions.
22. The non-transitory computer readable storage media of claim 21 , wherein performing the comparison operation generates one or more null values indicating that one or more rows were deleted from the table.
23. The non-transitory computer readable storage media of claim 22 , wherein performing the comparison operation generates one or more null values indicating that one or more rows were inserted into the table.
24. The non-transitory computer readable storage media of claim 22 , wherein performing the comparison operation generates one or more null values indicating that one or more rows of the table were updated.