Soft Delete Mode
Soft delete is the default sync mode in Fivetran. In this mode, Fivetran marks rows as deleted in the destination after they are deleted in the source. The alternative sync mode we use is history mode.
Supported connectors
To learn if your connector supports soft delete mode, check 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 the 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. Deleted rows are marked as TRUE in this column:
| ID | _fivetran_deleted | timestamp_col |
|---|---|---|
| 1 | FALSE | 2024-01-01T00:00:00Z |
| 2 | TRUE | 2024-01-02T00:00:00Z |
Now, if a record with id = 2 appears in the source again and we receive the change, the destination will contain the following values:
| ID | _fivetran_deleted | timestamp_col |
|---|---|---|
| 1 | FALSE | 2024-01-02T00:00:00Z |
| 2 | FALSE | 2024-01-03T00:00:00Z |
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_deletedcolumn 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
INSERTthat adds the new version of the row - a
DELETEthat marks the old version of the row as deleted by setting the_fivetran_deletedcolumn 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 |