Why Does Syncing Slow Down When the Destination Table Is Partitioned and Primary Keys Don’t Match?

Question

Why does syncing slow down when the destination table is partitioned, the source table is not partitioned, and the destination primary key does not match the source primary key?

Environment

Connector: Connector: Amazon RDS for SQL Server
Destination: Aurora PostgreSQL

Answer

Fivetran supports syncing to partitioned destination tables even when the source tables are not partitioned. However, when the destination primary key differs from the source primary key, we cannot use the source primary key for efficient row-level updates. In this case, we create a staging identifier column and run updates that can scan the full destination table. On partitioned tables, this can increase sync time and resource usage.

We rely only on the primary key defined in the source table for incremental updates, and you cannot configure a connection to use a unique constraint or unique index instead.

If you cannot align primary keys, you can sync from a SQL Server view that includes a surrogate key column and set that column as the primary key in the Schema tab after the initial sync. If the view has no primary key, we add a _fivetran_id column to identify rows.