Delta tables in a Databricks Delta Lake
In a Databricks Delta Lake, for each delta table there is a version history (also called a transaction log) that contains a version entry for each transaction executed on that table. Besides other information every history entry contains a version number and a timestamp.
The followings example describe how Process Extractor uses the version history of a Databricks Delta Lake for data extraction.
Example
Let us assume, that we have this scenario on 01 January 2023 12:00:00:
The version history of the clients table looks like this:
Version | Timestamp |
|---|---|
1 | 2023-01-01 11:01:23.123 |
0 | 2023-01-01 11:00:01.562 |
The clients table looks like this:
ID | Name | City |
|---|---|---|
1000 | Smith, John | New York |
1001 | Doe, John | New York |
1002 | Doe, Jane | Miami |
The data extraction is configured to extract all fields of the clients table using the history and the automatically generated dynamic parameter LATEST_VERSION_READ_TIME_clients has an empty value. The clients table extraction is configured to use the latest table change date for delta extraction.
The source table type is configured as a table type Table.
First data extraction (initial extraction)
We start the data extraction in ARIS Process Mining on 01 January 2023 12:00:00 with an empty default value for the LATEST_VERSION_READ_TIME_clients parameter. This means that the entire table is extracted.
Process Extractor starts data extraction on 01 January 2023 at 12:01:34, but nothing was changed in Delta Lake in those 94 seconds.
Process Extractor first determines the timestamp of the latest version, that is "2023-01-01 11:01:23.123" of version 1. Then the data extraction of the table version 1 is performed and the data is extracted and imported into the source table in ARIS Process Mining:
ID | Name | City | _ARIS_deltaTblTime | _ARIS_lastChanged |
|---|---|---|---|---|
1000 | Smith, John | New York | 2023-01-01 11:01:23.123 | 2023-01-01 12:02:00.342 |
1001 | Doe, John | New York | 2023-01-01 11:01:23.123 | 2023-01-01 12:02:00.342 |
1002 | Doe, Jane | Miami | 2023-01-01 11:01:23.123 | 2023-01-01 12:02:00.342 |
For delta tables extracted using the version history, there will be an additional column _ARIS_deltaTblTime in the source tables, which contains the time of the last history version in the extracted data.
Then the data loading is started in ARIS Process Mining and the dynamic parameter LATEST_VERSION_READ_TIME_clients is calculated. After the data load the current value of the LATEST_VERSION_READ_TIME_clients parameter is "2023-01-01 11:01:23.123".
Second data extraction (delta extraction)
Let's assume that after the first data extraction, two insert transactions were executed, each of which inserted two rows of data. In addition, in a third transaction, the city of the customer 1001 was changed to Miami. So we have the following status after these three additional transactions.
The version history of the clients table looks like this:
Version | Timestamp |
|---|---|
0 | 2023-01-01 11:00:01.562 |
1 | 2023-01-01 11:01:23.123 |
2 | 2023-01-01 13:02:21.632 |
3 | 2023-01-02 01:01:23.900 |
The clients table looks like this:
ID | Name | City |
|---|---|---|
1000 | Smith, John | New York |
1001 | Doe, John | Miami |
1002 | Doe, Jane | Miami |
1003 | Smith, Jane | Miami |
1004 | Doe, Carl | Boston |
1005 | Doe, Carla | Boston |
1006 | Smith, Carla | New York |
The data extraction is configured to extract all fields of the clients table using the history and the current value of the automatically generated dynamic parameter LATEST_VERSION_READ_TIME_clients is "2023-01-01 11:01:23.123". The clients table extraction is configured to use the latest table change date for delta extraction.
The source table type is configured as a table type Table.
On January 02, 2023 12:00:00, data extraction and data load are started again without manual change of the dynamic parameter LATEST_VERSION_READ_TIME_clients. That means that the data extraction is started with the current value of the LATEST_VERSION_READ_TIME_clients parameter .
Process Extractor starts data extraction at the time 02 Jan 2023 12:00:23.
The extractor first determines the timestamp of the newest version, that is "2023-01-02 07:35:20.409" of version 4. Then the version was determined that was valid at the timestamp "2023-01-01 11:01:23.123", that is version 1.
Then all data rows are extracted that were changed or added after version 1 to version 4 (including changes in version 4). So the following data rows are extracted and imported into the source table in ARIS Process Mining:
ID | Name | City | _ARIS_deltaTblTime | _ARIS_lastChanged |
|---|---|---|---|---|
1001 | Smith, John | Miami | 2023-01-02 07:35:20.409 | 2023-01-02 12:04:01.640 |
1003 | Smith, Jane | Miami | 2023-01-02 07:35:20.409 | 2023-01-02 12:04:01.640 |
1004 | Doe, Carl | Boston | 2023-01-02 07:35:20.409 | 2023-01-02 12:04:01.640 |
1005 | Doe, Carla | Boston | 2023-01-02 07:35:20.409 | 2023-01-02 12:04:01.640 |
1006 | Smith, Carla | New York | 2023-01-02 07:35:20.409 | 2023-01-02 12:04:01.640 |
After the data load, during which the dynamic parameter LATEST_VERSION_READ_TIME_clients is calculated, the current value of the parameter LATEST_VERSION_READ_TIME_clients is "2023-01-02 07:35:20.409".
Third data extraction (no changes)
After the successful second extraction and the data load on 02 January 2023 13:34:00, another data extraction was started. However, since the table contents in the Delta Lake were not changed, no data was extracted and the value of the dynamic parameter was not changed.
Forth data extraction (row deleted)
Let's assume that after the third data extraction the client with ID 1003 was deleted. So we have the following status:
The version history of the clients table looks like this:
Version | Timestamp |
|---|---|
5 | 2023-01-02 15:00:01.340 |
4 | 2023-01-02 07:35:20.409 |
3 | 2023-01-02 01:01:23.900 |
2 | 2023-01-01 13:02:21.632 |
1 | 2023-01-01 11:01:23.123 |
0 | 2023-01-01 11:00:01.562 |
The clients table looks like this:
ID | Name | City |
|---|---|---|
1000 | Smith, John | New York |
1001 | Doe, John | Miami |
1002 | Doe, Jane | Miami |
1004 | Doe, Carl | Boston |
1005 | Doe, Carla | Boston |
1006 | Smith, Carla | New York |
The data extraction is configured to extract all fields of the clients table using the history and the current value of the automatically generated dynamic parameter LATEST_VERSION_READ_TIME_clients is "2023-01-02 07:35:20.409". The clients table extraction is configured to use the latest table change date for delta extraction.
The source table type is configured as a table type Table.
On 03 January 2023 12:00:00, the data extraction and data load is started again without manually changing the dynamic parameter LATEST_VERSION_READ_TIME_clients. This means that the data extraction is started with the current value of the LATEST_VERSION_READ_TIME_clients parameter.
Process Extractor starts data extraction at the time 03 January 2023 12:02:11.
The extractor first determines the timestamp of the newest version, that is "2023-01-02 15:00:01.340" of version 5. Then the version is determined, which was valid at the timestamp "2023-01-02 07:35:20.409", that is version 4.
Then all data rows are extracted that were changed or added after version 4 to version 5 (including changes in version 5). So no data rows are extracted, because no data rows were added or changed. Deleted data rows cannot be extracted. Since no data rows are extracted and imported, nothing was changed in ARIS Process Mining.
Fifth data extraction (timestamp after the end of the version history)
On January 03 2023 16:00:00, the data extraction and data load are restarted with a manually changed value of the dynamic parameter LATEST_VERSION_READ_TIME_clients. The value of the LATEST_VERSION_READ_TIME_clients parameter was changed to "2023-01-03 01:00:00".
Process Extractor starts data extraction at the time 03 January 2023 16:00:20.
The extractor first determines the timestamp of the newest version, that is "2023-01-02 15:00:01.340" of version 5. Then the version that was valid at the timestamp "2023-01-03 01:00:00.000" was determined ("2023-01-03 01:00:00" with "000" for the milliseconds). Since this timestamp is newer than the timestamp of the highest version in the version history table, this also results in the newest version, that is version 5. Since the difference between two identical versions is always empty, the extraction does not return any rows.
Sixth data extraction (timestamp before the beginning of the version history)
On 03. January 2023 17:00:00, the data extraction and data load are restarted with a manually changed value of the dynamic parameter LATEST_VERSION_READ_TIME_clients. The current value of the LATEST_VERSION_READ_TIME_clients parameter was changed to "2023-01-01 00:00:00".
Process Extractor starts data extraction at the time 03 January 2023 17:00:15.
The extractor first determines the timestamp of the newest version, that is "2023-01-02 15:00:01.340" of version 5. Then the extractor finds that there is no table history at the specified time "2023-01-01 00:00:00.000". Since there is no history, no delta can be determined and the extraction fails with an error message.
Note
Data extraction of a table "x" also fails if the value of the dynamic parameter LATEST_VERSION_READ_TIME_"x" is set to a value matching the version history, but the data of one of the corresponding versions is no longer available (vacuumed).