History Mode
Use History Mode to track the history of the entries in your database. Enable it for the chosen objects in Source to store every change made to the objects’ rows in Target using the type 2 slowly changing dimension concept.
If you enable History Mode, Skyvia will replace the Update operation with Insert and will add three more columns to the table in Target: _history_start, _history_end, and _active_value. Use these columns to perform the data analysis for the specific date or period of time.
| Column | Type | Details |
|---|---|---|
| _active_value | BOOLEAN | Is true for the most recent record. Otherwise is false. Only one record among records with the same Id can have _active_value = true. |
| _history_start | DATETIME | Stores either the CreatedDate value for the unmodified records or LastModifiedDate value for the modified ones. |
| _history_end | DATETIME | Stores the date of the last time the record was active minus a millisecond. Is Null for the records with _active_value = true. |
Example
Let’s check what happens when you enable History Mode on the example. You have the following table synced between Source and Target:
| Id | FullName | CreatedDate | LastModifiedDate |
|---|---|---|---|
| 1 | Mary Johns | 2023-01-01T23:59:59.999Z | 2023-01-01T23:59:59.999Z |
| 2 | Steve Smith | 2023-01-02T23:59:59.999Z | 2023-01-02T23:59:59.999Z |
Mary’s last name has changed to Smith on 2023-03-04:00:00:00:000. When you sync this changes from Source to Target with the History Mode enabled, the table in Target will look like this:
| Id | FullName | CreatedDate | LastModifiedDate | _active_value | _history_start | _history_end |
|---|---|---|---|---|---|---|
| 1 | Mary Johns | 2023-01-01T23:59:59.999Z | 2023-01-01T23:59:59.999Z | false | 2023-01-01T23:59:59.999Z | 2023-03-03T23:59:59.999Z |
| 2 | Steve Smith | 2023-01-02T23:59:59.999Z | 2023-01-02T23:59:59.999Z | true | 2023-01-02T23:59:59.999Z | Null |
| 1 | Mary Smith | 2023-01-01T23:59:59.999Z | 2023-03-04T00:00:00.000Z | true | 2023-03-04T00:00:00.000Z | Null |
If you work with Source that supports soft delete, during the sync of a soft deleted record Skyvia will find a corresponding record by Id with an _active_value = true, will change it to false, and replace _history_end date with the date of a replication run. So, if you soft delete Mary’s record in Source and run the replication on 2023-05-04:00:00:00:000, the table in Target will look like this:
| Id | FullName | CreatedDate | LastModifiedDate | _active_value | _history_start | _history_end |
|---|---|---|---|---|---|---|
| 1 | Mary Johns | 2023-01-01T23:59:59.999Z | 2023-01-01T23:59:59.999Z | false | 2023-01-01T23:59:59.999Z | 2023-03-03T23:59:59.999Z |
| 2 | Steve Smith | 2023-01-02T23:59:59.999Z | 2023-01-02T23:59:59.999Z | true | 2023-01-02T23:59:59.999Z | Null |
| 1 | Mary Smith | 2023-01-01T23:59:59.999Z | 2023-03-04T00:00:00.000Z | false | 2023-03-04T00:00:00.000Z | 2023-05-04T00:00:00.000Z |
Limitations
History mode is not supported for the tables that lack timestamps as Skyvia relies on them to track changes.
How to Enable History Mode
To enable History Mode:
- Select an object to apply History Mode to, and click Edit.
- Select History from the mode dropdown.
