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

```text
_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:

```sql
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:

```sql
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:

```text
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):

```sql
-- 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.

### _fivetran_deleted 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/pst_FfUto_TxFo9K9LecsBbXBA