Soft Delete Mode
Soft delete is the default Fivetran approach for capturing deletes in the source. The alternative approach we use is History mode.
Supported connectors
To learn if your connector supports soft delete mode, open the connector's overview in our documentation and see if it captures deletes in the Features section. If it does, that means this connector supports soft delete mode.
Sync overview
Soft delete is the default sync mode in Fivetran.
In soft delete mode, Fivetran creates a new _fivetran_deleted
column in each table switched to soft delete mode in the destination. Rows deleted are marked as TRUE
in this column.
We get information about deletes directly from the connector. If a connector doesn't provide information about deletes, we try to infer them.
NOTE: For our Salesforce connector, we use the
is_deleted
column in the soft delete mode.
Tables without a primary key
For tables without a primary key - those that use the Fivetran-generated _fivetran_id
primary key - and for connectors like BigQuery that ignore primary keys present in the source tables, when a row is changed in the source, the UPDATE operation in soft delete mode for that row in the destination comprises the following operations performed in the listed order:
- an INSERT that adds the new version of the row
- a DELETE that marks the old version of the row as deleted by setting the
_fivetran_deleted
column value toTRUE
As a result, the _fivetran_synced
timestamp value for the most recent old version of the changed row in soft delete mode is always greater than that for the new version of the row added in the destination.
Assume that the initial value of the counts
column in a source row was 3, then it changed to 2, and, lastly, to 1. The _fivetran_synced
and _fivetran_deleted
column values in the destination table will be as follows:
counts | _fivetran_deleted | _fivetran_synced |
---|---|---|
2 | TRUE | 2023-06-16 07:15:29.566+00 |
1 | FALSE | 2023-06-16 07:15:29.56+00 |
3 | TRUE | 2023-06-16 01:14:40.78+00 |