fivetran history mode

TL;DR

History mode keeps every version of every row, marking the current one with fivetranactive = true and stamping each version with fivetransynced. If you query without filtering to the active rows, you get duplicates by design. Filter to the active version for current state, and use the synced timestamps for point-in-time reconstruction.

fivetran history mode

Use this when

• A Fivetran table has multiple rows per primary key and you did not expect that.
• You need to reconstruct what a record looked like at a past date.
• Row counts exploded after switching a connector to history mode.

Not for this skill when

• You only need the current state of each record. Standard mode is cheaper.
• Duplicates appear in a standard-mode table. That is a different bug.

Steps

1. Confirm history mode is on for the connector and identify the fivetranactive, fivetransynced, and fivetrandeleted columns. Note the mode in the connector settings. Verify: you know versioning is intentional
2. For current state, always filter WHERE fivetranactive = true (and fivetrandeleted = false). Note the row count matches the source. Verify: duplicates collapse to one row per key
3. For point-in-time queries, filter fivetransynced <= your timestamp and take the latest version per key. Note the result matches the source as of that time. Verify: history actually answers the audit question
4. Put the active-row filter in a staging model so analysts never query raw history tables. Note all downstream models read the staging layer. Verify: nobody hand-filters again
5. Watch table size growth and set a retention or archival policy for old versions. Note storage stops growing unboundedly. Verify: cost stays predictable

Variant phrasings

fivetran history mode duplicates rows

The symptom search when the filter is missing.

fivetran fivetranactive explained

The column-definition search.

Compatibility: Connectors that support history mode; column names are consistent across warehouse destinations. Not all sources support it.

Why it happens

History mode is type-2 versioning done by the connector: every sync that sees a changed row inserts a new version instead of updating. That is exactly what audit and slowly-changing-dimension use cases want, but it means the raw table is no longer one-row-per-key. The duplicates are the feature; the missing filter is the bug in your query.

Edge cases / pitfalls

• Primary-key changes on the source create a new version chain that looks like a duplicate. Handle key changes before enabling history mode.
• Very wide tables with frequent changes grow fast in history mode. Estimate storage before turning it on.
• Late-arriving deletes can strand an active version that should be closed. Reconcile deletes on a schedule.
• dbt snapshots over history-mode tables double-version. Snapshot the active-filtered staging table instead.

Provenance

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