History Mode PRIVATE PREVIEW

Fivetran history mode records every version of each record in your destination. With history mode, you can analyze data from a particular point in time or analyze how data has changed over time.

History mode is activated on a per-table basis. You can decide which tables have analytically valuable historical information and keep only those. You can switch a table back to live mode if you no longer want to collect historical data from that table.

Supported connectors

• Salesforce
• Zendesk Sunshine

Sync overview

It’s easiest to understand history mode in contrast with live mode. In live mode, when a record is updated in your source, Fivetran updates the corresponding record that exists in your data warehouse. Likewise, when a record is deleted in your source, we delete the corresponding record in the warehouse.

Once you activate history mode for a table, Fivetran keeps all versions of the record using the type 2 slowly changing dimension format. To keep all versions of the record, we add three columns to each record in that table. The next section examines these columns and their functions in detail.

Note: There is a third sync mode, legacy mode, which operates as a hybrid. Once you activate history mode for a table, you can’t return to legacy mode. You can only switch to live mode. In legacy mode, when a record is deleted in your source, we soft delete it in the warehouse by setting the system column fivetran_deleted to TRUE. Once you activate history mode for a table, we delete the fivetran_deleted column and add a set of columns that collect more detailed information about previous versions of your records. If you'd rather not keep all of those deleted records, you can switch to live mode directly from legacy mode.

System columns for records in tables with history mode

History mode example using mock data

The following example illustrates how Fivetran’s history mode handles data changes in the source and writes those changes in the warehouse.

Note: The bold text shows the change in the data after each sync cycle completes.

Suppose the following data is present in the source:

After Fivetran syncs from the source to the warehouse, the warehouse will contain the following records:

Now, let’s assume we receive the following records from the source in the next sync:

After Fivetran syncs from the source to the warehouse, the warehouse will contain the following records:

Now, let’s assume the record with id = b is deleted at time ‘2019-06-06T10:58:35Z’, and we receive that change in the next sync cycle. Then the warehouse will contain the following values:

Switching modes

We detect changes to sync mode instantly and start the migration. If you switch modes while your sync is running, we pause that sync. Then, we run the migration queries. After we run the migration queries, we restart the sync.

Switching from live to history mode

To update previously synced data when switching from live to history mode, we run a series of migration queries to make the following changes:

1. First, Fivetran adds three history mode system columns: _fivetran_start, _fivetran_end, and _fivetran_active.

2. If the record is not deleted, then we:

   • update _fivetran_start to _fivetran_synced value
   • update _fivetran_end to the warehouse max timestamp value
   • update _fivetran_active to TRUE

3. If the record is deleted then we:

   • update _fivetran_start to the warehouse min value (1970-01-01T00:00:01Z for MySQL, 01-01-01T00:00:00Z for other warehouses)
   • update _fivetran_end to _fivetran_synced value
   • update _fivetran_active to FALSE

4. We generate an optional cleanup task to delete the _fivetran_deleted column if it is present.

Note: These steps are not required for newly-created connectors.

Switching from history to live mode

To update previously synced data when switching back from history to live mode, we perform the following steps, in order:

1. We delete all the rows from the source where _fivetran_active = FALSE.

2. We drop _fivetran_start, _fivetran_end, and _fivetran_active.

Note: When you switch a table from history mode to live mode, we delete all the historical versions of your records in that table. Make sure you definitely don’t care about old versions of your records before you switch a table to live mode.

Limitations

Unsupported tables

To keep the history in the data warehouse, Fivetran relies on a timestamp value that monotonically increases over time with the data change or update. So if a table lacks that timestamp column, we don’t support history mode for that table. Fivetran also doesn’t support history mode for re-imported tables. That means if we are syncing the entire table during each sync, then we don’t support history mode for that table.

History mode support for re-imported tables is coming soon.

Changes to data between syncs

If a source lacks logs that capture data changes between syncs, then we can only capture data changes during syncs. That means that if a field changes multiple times between syncs, we will not be able to capture those in-between states. For example, suppose the sync runs once a day at midnight. At midnight, the value of the field foo is 10, and the Fivetran sync captures it. At 6 a.m., the value changes to 15. Then, at 11 p.m., the value changes to 20. At midnight the next day, the Fivetran sync runs and notes the value of foo was updated to 20 at 11 p.m. The in-between state, where foo was updated to 15 at 6 a.m., is not captured.

Consider another example. Suppose a sync runs once a day at midnight. Then, at 8 a.m. a record is created. Then, at 10 a.m. the same record is deleted. When the next sync runs at midnight, the existence of this record will not be recorded.

Salesforce doesn't provide logs, so it is affected by this limitation. To mitigate the limitation, run the connector more frequently to capture higher fidelity. Many other sources capture a record of changes between syncs and will not have this limitation.

Identical _fivetran_start and _fivetran_end values

If, while fetching data, we see a new record upsert followed be a delete for that same record with the same timestamp, we will:

• assign the same timestamp value to _fivetran_start and _fivetran_end, and
• set _fivetran_active to FALSE.