Why Can't I Rely on System Columns in Destination During Re-sync

Issue

During a re-sync or table re-sync, the queries or transformations run on a destination return or evaluate all or some records as deleted even though they are not deleted in the source, or as active even though they are deleted in the source.

Environment

Connectors

Resolution

Do not use system columns like _fivetran_deleted to drive automations, alerts, or reports while a re-sync or table re-sync is running. Wait until the re-sync completes successfully before treating system columns as a reliable indicator of source deletes.

Cause

Soft delete mode

In soft delete mode, Fivetran maintains the _fivetran_deleted column in every managed table and sets it to TRUE when the source reports a delete.

When you trigger a re-sync or a table re-sync in soft-delete mode, Fivetran does the following:

1. Merges rows fetched from the refreshed snapshot.
2. Sets those to _fivetran_deleted = FALSE as they land.
3. After each batch is merged, Fivetran sets _fivetran_deleted = TRUE in rows that have _fivetran_synced < <re-sync start time>.

If the re-sync spans multiple batches, you will briefly see a mix of reactivated rows from completed batches and stale rows awaiting their turn until the final sweep runs. This sequence means we do not flip _fivetran_deleted to FALSE for an entire table or the whole schema up front. Only rows that arrive in the batch are reactivated, and all other rows are marked _fivetran_deleted = TRUE after the merge. We recommend that you pause downstream readers until the re-sync finishes to avoid acting on that transient state.

History mode

History mode tables always contain _fivetran_active, _fivetran_start, and _fivetran_end, which collectively implement slowly changing dimension (SCD) Type 2 storage:

• _fivetran_start replaces the source timestamp
• _fivetran_end records when a version became inactive
• only one version per primary key row can be _fivetran_active = TRUE.

Re-syncs in history mode do not impact the previously tracked changes.

When you trigger a re-sync or a table re-sync in history mode, Fivetran records the re-sync start time T and then performs two kinds of actions that update Fivetran system columns in the destination:

• Writes for re-fetched rows — the rows the connector re-fetched from the source are written (inserted or upserted). Rows written by Fivetran receive history mode system columns _fivetran_start, and _fivetran_active = TRUE.
• Invalidation or version-close for prior rows — rows that are not re-fetched or that the connector indicates are deleted are marked no longer active by setting _fivetran_active = FALSE and by setting _fivetran_end (the row’s end/invalid timestamp). Fivetran uses T as the cutoff when deciding which prior rows to invalidate (rows whose last sync timestamp is older than the re-sync start are candidates for being closed).

Fivetran can apply those two kinds of actions in different legitimate orders.

Because Fivetran can invalidate destination rows either before or after refreshed rows are written — and because deletes, explicit per-primary-key closes, and streaming changes can interleave with upserts — system columns are actively changing during a re-sync, and downstream code must not rely on these columns being stable until the re-sync (the run) completes.