fivetran primary key changed on source table, sync broken

TL;DR

Fivetran tracks rows by the source primary key, so when the key changes the connector cannot match new rows to old ones and the sync breaks or duplicates. Re-sync the affected table so Fivetran rebuilds its key mapping, then fix downstream models that joined on the old key. Treat a source key change as a breaking change even when the source team calls it minor.

fivetran primary key changed on source table, sync broken

Use this when

• A source changed its primary key and the connector sync broke.
• The destination table suddenly has duplicate or orphaned rows.
• Downstream joins on the old key return wrong results.

Not for this skill when

• You changed the key deliberately with a planned migration. Follow the migration plan.
• The key is unchanged but rows duplicate. Look at history mode or sync bugs.

Steps

1. Confirm the key change in the source: old key definition versus new. Note exactly what changed. Verify: the change is documented
2. Check the connector's sync state and errors for the affected table. Note how the breakage manifests. Verify: the symptom is mapped
3. Re-sync the table so Fivetran rebuilds its mapping under the new key. Note the sync completes. Verify: the connector is healthy again
4. Audit downstream models and joins that used the old key and update them. Note every consumer uses the new key. Verify: downstream is consistent
5. Ask the source team to announce key changes as breaking changes going forward. Note the agreement. Verify: the next change is not a surprise

Variant phrasings

fivetran primary key change resync

The fix-shaped search.

fivetran duplicate rows after key change

The symptom phrasing.

Compatibility: Connectors where Fivetran tracks primary keys. Behavior varies by source: some expose key changes cleanly, others do not.

Why it happens

The primary key is how the connector identifies a row across syncs: updates, deletes, and history versioning all hang off it. Change the key and the connector sees strangers where it used to see familiar rows, so it inserts instead of updating and the table fills with duplicates. Downstream, every join on the old key silently goes wrong. Sources change keys more often than anyone admits, usually during their own migrations, which is why the breakage always feels sudden.

Edge cases / pitfalls

• Re-syncing a large table is expensive and slow. Scope the re-sync to the affected table, not the whole connector.
• History-mode tables compound the problem: the old key's versions linger. Clean or rebuild history tables after the key change.
• Composite keys that gain or lose a column are the sneakiest change. Diff the full key definition, not just the name.
• If the source will not stabilize the key, consider a surrogate key in your own staging layer and join on that instead.

Provenance

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