_fivetran_deleted

Not specified by the original author.

TL;DR

_fivetran_deleted is a boolean Fivetran adds to mark rows that were deleted in the source, because Fivetran syncs deletes as flags rather than removing rows. Filter WHERE NOT _fivetran_deleted in every downstream model and report, or your counts will include ghosts.

_fivetran_deleted

Use this when

• A synced table has a _fivetran_deleted column you didnt create
• Row counts from Fivetran tables are higher than the source shows
• Building dbt staging models on top of Fivetran sources

Not for this skill when

• The connector runs in hard-delete mode (no flag column is added then)
• The table uses history mode with _fivetran_start / _fivetran_end (different pattern)
• The source isnt Fivetran at all

Steps

1. Confirm the column exists and see how many rows are flagged:

SELECT _fivetran_deleted, COUNT(*)
FROM your_schema.your_table
GROUP BY 1;

Expected output: two groups, false (live rows) and true (soft-deleted). If true is nonzero, every unfiltered query over this table is overcounting.

2. Add the filter to your staging models so downstream never sees deleted rows:

SELECT *
FROM {{ source('fivetran_source', 'your_table') }}
WHERE NOT _fivetran_deleted

Expected output: the staging model returns only live rows. Put the filter in staging once rather than repeating it in every mart.

3. Check the connector's sync mode if the column is missing but you expected deletes to sync:

Connector settings: sync mode (soft delete vs hard delete)

Expected output: soft-delete mode explains the column; hard-delete mode explains its absence. Switching modes changes history, so decide deliberately.

4. For incremental models, keep the filter AND handle reappearing rows (a deleted row can come back as not-deleted on a later sync):

-- dedupe on the natural key, keeping the latest sync
QUALIFY ROW_NUMBER() OVER (PARTITION BY id ORDER BY _fivetran_synced DESC) = 1

Expected output: one row per key, with the current deleted flag. Without this, a row deleted then restored appears twice in incremental builds.

Variant phrasings

fivetran deleted rows still showing in dashboard

The dashboard query lacks the NOT _fivetran_deleted filter. Fix it at the staging layer so all dashboards inherit it.

fivetrandeleted is null for some rows

Very old synced rows predate the column. Treat null as not-deleted with COALESCE(_fivetran_deleted, false).

Why it happens

Fivetran avoids destructive syncs: instead of DELETEing rows when the source deletes them, it flips a boolean. This preserves history and makes syncs idempotent, but it means "all rows in the table" no longer means "all live rows." The _fivetran_synced timestamp next to it tells you when each row was last seen.

Edge cases

• Some connectors add the column only after the first delete is observed, so its absence today doesnt mean it wont appear tomorrow. Write the filter defensively.
• In BigQuery, the column is BOOL; in Snowflake it is BOOLEAN. Same semantics, mind the dialect in filters.
• Hard-delete mode physically removes rows, which breaks incremental models that rely on seeing the delete event. Prefer soft delete for analytics.

Provenance

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

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

Thread query: fivetrandeleted

TL;DR: _fivetran_deleted is a boolean Fivetran adds to mark rows that were deleted in the source, because Fivetran syncs deletes as flags rather than removing rows. Filter WHERE NOT _fivetran_deleted in every downstream model and report, or your counts will include ghosts.

Not specified by the original author.

TL;DR

When building on Fivetran-synced tables, always filter WHERE fivetrandeleted = FALSE (or fivetranactive = TRUE in history mode) in every downstream query, or deleted rows will pollute aggregates. For GDPR-style hard deletes, add a post-load transformation step.

Steps

1. When building on Fivetran-synced tables, always filter WHERE fivetrandeleted = FALSE (or fivetranactive = TRUE in history mode) in every downstream query, or deleted rows will pollute aggregates. For GDPR-style hard deletes, add a post-load transformation step. Agents that count rows or join Fivetran tables without the deleted filter will produce silently wrong numbers.

When to use

Use this skill when you run into "Fivetran never hard-deletes: filter fivetrandeleted in every query".

When not to use

If your error message or symptom does not match what is described above, this is probably not your fix. Search for your exact error text instead of forcing this one to fit.

Versions

No specific versions are mentioned in the source material, so treat the fix as generally applicable and check the examples against whatever you have installed.

Why this happens

The original report does not dig into a root cause. It documents the symptom and the fix that resolved it.

Source: Fivetran docs (deleted source data): Fivetran never hard-deletes in the destination. Deleted source rows are soft-deleted via a fivetrandeleted BOOLEAN column set to TRUE. To actually remove data, run DELETE or DROP in the destination yourself, or write a post-load transformation that deletes where fivetrandeleted = TRUE. In history mode there is no fivetrandeleted column at all; deleted rows are marked with fivetranactive = FALSE and every version is kept.

Verified resolution: When building on Fivetran-synced tables, always filter WHERE fivetrandeleted = FALSE (or fivetranactive = TRUE in history mode) in every downstream query, or deleted rows will pollute aggregates. For GDPR-style hard deletes, add a post-load transformation step. Agents that count rows or join Fivetran tables without the deleted filter will produce silently wrong numbers.

Source URL: https://fivetran.com/docs/destinations/troubleshooting/deleted-source-data

Not specified by the original author.

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

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

Thread query: 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.