Soft Delete Mode

Soft delete is the default Fivetran approach for for objects. The alternative approach we use is .

---

Supported connectors

To learn if your connector supports soft delete mode, open the connector's overview in our documentation and see and see if it 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 and past versions of the rows changed in the source 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 , we use the is_deleted column in the soft delete mode.

Primary keyless tables

For tables without primary keys - those that use the Fivetran-generated _fivetran_id primary key - and for connectors like 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 to TRUE

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