Handling Deletes in Fivetran: Soft Deletes, _fivetran_deleted and GDPR Erasure Requests

A customer emails asking to be deleted. Someone deletes the record in Salesforce, ticks the box in the CRM, and tells legal it is done. Six weeks later an auditor asks where else that person's data lives, and the answer is: in the warehouse, in three dbt models, in a BI extract, and in a Census sync that pushed it back into a marketing tool.

None of that is a bug. It is the default behaviour of every well-designed ELT pipeline, Fivetran included, and it catches teams out constantly. This tutorial explains exactly how deletes travel from source to destination in Fivetran, what the _fivetran_deleted column does and does not guarantee, and how to build an erasure workflow that actually removes data without blowing up your history or your bill.

The default: Fivetran soft-deletes

When a row disappears at the source, Fivetran does not issue a DELETE against your destination table. It marks the row instead. In current connectors that mark is the system column _fivetran_deleted (a boolean), sitting alongside the other system columns _fivetran_synced and, where relevant, _fivetran_id.

So a deleted row looks like this:

id	email	_fivetran_deleted	_fivetran_synced
4417	ada@example.com	true	2026-02-11 04:12:09

The row is still there. The email is still there. Everything a downstream query can read is still readable. All that changed is a flag.

That design is deliberate and mostly correct:

• Analytics needs the history. If a sales rep deletes an opportunity, revenue reporting for last quarter should not silently change.
• Deletes are reversible at the source. Salesforce recycle bins, soft deletes in application tables, and accidental bulk operations are common. A hard delete in the warehouse would lose data you cannot recover without a re-sync.
• Re-syncs are expensive. Reconstructing a deleted row means re-reading history, which for a high-MAR connector is a real invoice line.

The trap is that "the pipeline is compliant because Fivetran replicates deletes" and "the personal data is gone from the warehouse" are two completely different statements.

How deletes reach Fivetran in the first place

Before you can handle a delete, the pipeline has to see it. That varies by connector type, and this is where most surprises live.

Database connectors using CDC

Log-based CDC connectors - Postgres logical replication, MySQL binlog, SQL Server CDC, Oracle and SAP via HVR, MongoDB change streams - see deletes as first-class events in the transaction log. A DELETE statement produces a delete event, Fivetran picks it up on the next sync, and the destination row gets flagged. This is the cleanest case. If you want the mechanics of the underlying setup, see Postgres CDC to Snowflake with Fivetran and Oracle and SAP CDC with Fivetran HVR.

Two caveats that bite in practice:

• TRUNCATE is not a row-level delete. Depending on the database and the replication method, a truncate may appear as a DDL event rather than N delete events, and rows may be left un-flagged in the destination until a re-sync. Never assume a truncate at the source cleans the destination.
• Application soft deletes are invisible as deletes. If your app sets deleted_at = now() instead of deleting, the CDC stream sees an update. _fivetran_deleted stays false forever, and your models must respect deleted_at themselves.

Database connectors without CDC (key-based or full-table)

If a table is synced by an incremental key (updated_at, an incrementing id) rather than the log, Fivetran has no way to observe a delete - the row simply stops being returned, and nothing in the incremental window tells the connector it ever existed. Deletes on these tables are only detected when the table is re-read in full. For small tables, full-table sync every run solves this. For large ones, this is a strong argument to move the table onto CDC.

SaaS and API connectors

Behaviour depends entirely on what the source API exposes:

• Some APIs expose a deleted-records endpoint or an isDeleted field (Salesforce is the friendly example) and Fivetran uses it.
• Some expose nothing, and Fivetran detects deletes by periodically re-reading the full object and diffing - which is why certain connectors do heavier syncs on a schedule.
• Some genuinely cannot express deletes at all, and rows persist until you intervene.

Check the connector's documentation page for how it handles deletes before you design any compliance process on top of it. "It is a Fivetran connector so deletes work" is not a safe assumption across a portfolio of 40 sources.

Capturing deletes vs. not capturing them

Several connectors expose a setting along the lines of capture deletes / soft delete behaviour, and destinations differ in what they support. The practical modes you will encounter:

1. Soft delete (default). Row retained, _fivetran_deleted = true. Best for analytics, required if you want history.
2. History mode. Instead of flagging a single current row, the connector keeps versioned records with active-from/active-to semantics, and a delete closes the active record. See Fivetran History Mode for how that changes your modelling.
3. Deletes not captured. Rows accumulate in the destination and never get flagged. Common with key-based incremental syncs and with APIs that cannot report deletions.

Write down which mode each connection is in. It belongs in the same place as your schema-change policy, and if you manage Fivetran with the Terraform provider it belongs in code.

Making downstream models respect deletes

Here is the single most common production defect we find in Fivetran estates: the raw schema honestly records deletions, and the models built on top ignore them.

Any select * off a Fivetran-landed table includes deleted rows. Your revenue number is now wrong in a direction that is very hard to spot.

The fix is a staging layer where every model filters deliberately, and the filter is a decision, not a copy-paste:

-- models/staging/stg_customers.sql
with source as (
    select * from {{ source('salesforce', 'account') }}
),

renamed as (
    select
        id                as customer_id,
        name              as customer_name,
        email,
        created_date      as created_at,
        coalesce(_fivetran_deleted, false) as is_deleted,
        _fivetran_synced  as synced_at
    from source
)

select * from renamed
where not is_deleted   -- current-state model: deleted rows excluded

Note coalesce(_fivetran_deleted, false). On many connectors the column is null for rows that were never deleted rather than false, and where _fivetran_deleted = false silently returns nothing. That one is worth grepping your whole project for today.

Then split the intent explicitly:

• stg_* models: current state, deleted rows filtered out. This is what 90% of analytics should read.
• *_including_deleted or a history model: retains flagged rows for churn analysis, audit, and reconciliation against source counts.
• Fact tables: think hard. A deleted order usually should still count in last year's revenue if it was genuinely fulfilled; a deleted test order should not. That is a business rule, not a pipeline setting.

Add a test so nobody quietly reintroduces the bug:

models:
  - name: stg_customers
    columns:
      - name: is_deleted
        tests:
          - accepted_values:
              values: [false]

And a reconciliation query you can run whenever a source count and a warehouse count disagree:

select
    count(*)                                              as total_rows,
    count_if(coalesce(_fivetran_deleted, false))          as deleted_rows,
    count_if(not coalesce(_fivetran_deleted, false))      as live_rows,
    max(_fivetran_synced)                                 as last_synced
from raw.salesforce.account;

If live_rows does not match the source system's own count, you have either an un-captured delete or a sync that is behind. Both are worth knowing before finance asks.

The erasure request: a runbook

GDPR Article 17, CCPA/CPRA deletion rights and most internal data-retention policies want the data gone, not flagged. A soft delete does not satisfy that. Here is a workflow that does, without forcing a full historical re-sync.

1. Delete at the source first

Always. If you purge the warehouse but the record still exists upstream, the next sync brings it straight back. Source first, downstream second - that ordering is the whole game.

2. Know every place the data landed

Build and maintain a small PII inventory: which schema, which table, which column holds a direct identifier, and which key ties back to the subject. This is unglamorous and it is the difference between a 20-minute response and a two-week panic. Fivetran's own metadata (via the Platform connector and the information schema) tells you which tables exist and when they last synced; the inventory tells you which ones matter.

Do not forget the places data went after the warehouse: BI extracts, materialised dashboards, downstream reverse-ETL destinations pushed out by Census, and any object storage or Iceberg tables in a managed data lake.

3. Purge or redact in the destination

You own the destination tables, so you can run DML against them. Two patterns:

Hard delete - simplest, removes the row entirely:

delete from raw.salesforce.contact
where id = '0031t00000ABCDE';

Redact in place - keeps the row for referential integrity and counts, destroys the identifiers:

update raw.salesforce.contact
set email      = null,
    first_name = 'REDACTED',
    last_name  = 'REDACTED',
    phone      = null
where id = '0031t00000ABCDE';

Redaction is usually the better answer for fact-linked entities: your order counts stay correct and the personal data is gone.

Be aware of the interaction with syncs: if the record still exists at the source, the next sync will re-write your redaction from source values. That is exactly why step 1 comes first.

4. Sweep the derived layer

Deleting the raw row does not clean models built from it. Either re-run the affected dbt models so incremental tables are rebuilt from the purged raw layer, or run the same redaction against the incremental targets. Full-refresh the incremental models that carry identifiers; a plain dbt run will not retroactively remove a row from an incremental table.

5. Handle time travel and backups

Snowflake Time Travel and Fail-safe, BigQuery time travel, Databricks Delta history: after a hard delete the data is still retrievable for the retention window. Most regulators accept a documented, bounded retention window; what they do not accept is you not knowing about it. Note the window per destination and state it in your process.

6. Log it

Subject, request date, tables touched, statements run, operator, completion date. An erasure process you cannot evidence is an erasure process you did not do.

Reduce the surface area before you need any of this

The cheapest erasure request is one that touches three tables instead of thirty. Two upstream controls do most of the work:

• Do not land what you do not need. Column blocking and hashing keep identifiers out of the destination in the first place - see Blocking and Hashing PII in Fivetran. A hashed email that you never need to reverse is dramatically less painful in an audit.
• Keep your PII in a small number of known tables. Resist the urge to denormalise emails into every mart. One identity dimension, joined on a surrogate key, is one place to redact.

And if your obligation is that personal data must never leave your network at all, the answer is architectural rather than procedural: run the data plane yourself with Hybrid Deployment.

A checklist you can lift

• Every connection's delete-capture behaviour is documented, and key-based incremental tables are flagged as "deletes not detected".
• Application soft-delete columns (deleted_at, is_active) are handled in staging models, not just _fivetran_deleted.
• Every staging model filters deletes explicitly with a coalesce(..., false) guard.
• A dbt test fails the build if deleted rows leak into a current-state model.
• A PII inventory maps subject identifiers to schemas, tables and columns - including reverse-ETL destinations.
• The erasure runbook says source-first, then raw, then derived, then extracts.
• Time-travel and backup retention windows are documented per destination.
• Every executed erasure is logged with the statements run.

Where this usually goes wrong

In practice the failures we get called in to fix are rarely exotic. They are: a where _fivetran_deleted = false that returns zero rows because the column is null; a churn dashboard that never dropped, because deleted accounts were never filtered; a truncate at the source that left 40 million orphan rows in the destination; and an erasure process that purged the warehouse but not the marketing tool it had been syncing into for a year.

All four are cheap to prevent and expensive to discover late.

If you want a second pair of eyes on how deletes and personal data move through your Fivetran estate - or a governance review before an audit rather than after one - get in touch. We do this work as a focused engagement: connector-by-connector delete audit, staging-layer fixes, and a written erasure runbook your compliance team can actually follow.