Skip to main content
Docs
Feature Requests Onboarding Trust Center Status Page

Community

Connector Improvement: Detect DDL-driven field changes in Postgres Connector

Answered
Ryan Delgado User

Hi Fivetran team,

Today we encountered an edge case with one of the tables we sync from Postgres into Redshift via Fivetran. We recently added a new status column to the table, and the column is a non-nullable string column. This table has existed for a while now, and we have millions of pre-existing rows. To "backfill" the status field for those records, we set a default value in the ALTER TABLE ADD COLUMN statement to be "SYNC_COMPLETE." This modified the historical values of this column without an explicit UPDATE statement.

Fivetran correctly picked up the new column and continued with the sync. However, it did not detect the change in the historical records, and the status column showed up as NULL for those records. To fix this, we kicked off a full resync of the table.

This isn't common, but I want to flag it with Fivetran as it will likely happen in the future. We want this type of change to be picked up automatically by Fivetran.

I'm happy to answer any questions you have.

Thanks, Ryan

Please sign in to leave a comment.

Comments

1 comment

Date Votes
  • Official comment
    Kevin Kim Fivetranner

    Hi Ryan,

    Thanks for your post in our feature requests portal! I'm Kevin, database PM at Fivetran.

    This is a product gap we are aware of and are working towards a solution. One difficulty is that Postgres does not log DDL changes in its logs, so we have to detect when schema changes happen in order to backfill that data. While we don't have a timeline for this feature yet, this is one of our top asks and we are hoping to prioritize this very soon.

    Just to confirm, are you using the XMIN or WAL update method?