fivetran soft delete

TL;DR

In soft-delete mode Fivetran sets fivetrandeleted to true instead of removing the row. Deletes are working as designed; your queries just need WHERE fivetrandeleted = false. If you truly want rows gone, switch the connector to hard delete in the dashboard.

fivetran soft delete

Use this when

• Deleted records still appear in warehouse tables after a sync.
• You need to decide between soft-delete and hard-delete mode for a connector.
• Downstream dashboards show more rows than the source system reports.

Not for this skill when

• The connector is in history mode. Deleted-row semantics differ there.
• Deletes are not detected at all. Check whether the source API reports deletes first.

Steps

1. Open the connector setup in Fivetran and confirm the delete mode is soft delete. Note the dashboard shows the active mode. Verify: you know which behavior to expect
2. Run a sync and verify fivetrandeleted flips to true on a known-deleted record. Note the flag changes on the next sync after the delete. Verify: deletes are actually flowing
3. Add WHERE fivetrandeleted = false to staging models so the filter is inherited downstream. Note every consumer reads through the staging layer. Verify: the filter cannot be forgotten per-model
4. Backfill or rebuild any aggregates built before the filter existed. Note their row counts now match the source. Verify: historical numbers are corrected
5. Only if the business needs physical removal, flip to hard delete and run a full re-sync window. Note the mode change is logged in the connector history. Verify: the change is auditable

Variant phrasings

fivetran soft delete vs hard delete

The mode-comparison search.

fivetran deleted rows still in warehouse

The symptom phrasing.

Compatibility: All Fivetran connectors that support delete detection. Some sources (event streams, append-only APIs) never report deletes regardless of mode.

Why it happens

Warehouses are cheap storage and deletes are rare relative to inserts, so the default keeps a full audit trail by flagging instead of removing. The surprise is that the flag is opt-out at read time: nothing filters it for you. Teams that skip the staging-layer filter rediscover this every quarter when finance asks why counts drifted.

Edge cases / pitfalls

• Some sources mark deletes only on full re-syncs, not incremental ones. Expect lag between the delete and the flag.
• Hard-delete mode still leaves old soft-deleted rows from before the switch. Clean them once manually.
• GDPR-style erasure requests need hard delete plus a re-sync; soft delete keeps the personal data in the table.
• dbt snapshots on top of soft-deleted sources double-flag rows. Model the snapshot on the filtered staging table.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_2bM2tAIKev3OtymMBaJVSQ