fivetran history mode duplicates rows, how to fix

TL;DR

History mode versions every change, so querying without the active-row filter returns duplicates by design. Filter to fivetranactive = true for current state. If duplicates persist with the filter, the source key changed or the sync wrote bad versions, and the table needs a targeted cleanup.

fivetran history mode duplicates rows, how to fix

Use this when

• A history-mode table has more rows than the source.
• Counts are wrong even though the connector is healthy.
• Duplicates appeared after a source key or schema change.

Not for this skill when

• The table is in standard mode. Duplicates there are a different bug.
• You want historical versions. Those are not duplicates; they are the point.

Steps

1. Check whether queries filter to the active version: WHERE fivetranactive = true. Note whether the filter is present. Verify: the missing filter is the usual answer
2. With the filter applied, verify counts match the source. Note the corrected count. Verify: the design behavior is confirmed
3. If duplicates remain, check for a source primary key change that split version chains. Note the key change. Verify: key-change duplicates are identified
4. Clean the bad versions: delete or rebuild the affected key ranges, then let the sync continue. Note the table is clean. Verify: the table is repaired
5. Put the active filter in a staging model so no one queries raw history again. Note the staging layer is the contract. Verify: the pattern is enforced

Variant phrasings

fivetran fivetranactive filter

The filter-shaped search.

fivetran history mode too many rows

The symptom phrasing.

Compatibility: History-mode connectors on all warehouse destinations. Column names are consistent.

Why it happens

History mode is versioning, not mirroring: every detected change inserts a new row version. The raw table is therefore supposed to hold multiple rows per key, and the active flag marks the current one. Almost every duplicates complaint is a query missing that filter. The real duplicates, the ones that survive the filter, come from key changes that fork the version chain, and those need cleanup because no filter can reconcile two chains for the same logical row.

Edge cases / pitfalls

• Late-arriving deletes can leave a version active that should be closed. Reconcile on a schedule for delete-heavy sources.
• Aggregations over unfiltered history double-count. Every aggregate must start from the active-filtered staging table.
• Switching a connector from standard to history mode backfills versions for existing rows. Expect a one-time row multiplication.
• Very chatty sources version on every sync and the table grows fast. History mode on high-churn tables needs a retention policy.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_Jm1q05r4EdHpw-EoQF3UKA