_fivetran_deleted

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.

1. 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.

1. 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.

1. 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