Fivetran: Connectors to Postgres destinations are failing with an Alert: Drop Deprecated Non-null Column

08/19/2024 - Postgres Deprecated Column Issue

‌

Summary

Fivetran deployed a pull request (PR) changing how we handle the JSONB datatype in Postgres destinations. A bug in the logic resulted in Fivetran incorrectly identifying datatype changes on columns with a type of String. Fivetran then attempted to deprecate and DROP the original columns to create new columns. This operation did not work if there were downstream Views that referenced the original columns. This resulted in a Warning on the Dashboard with instructions to manually DROP the deprecated columns and re-create the Views. 

‌

Connectors with tables that had primary keys (PK) with the datatype String also saw syncs fail for a period of time. This happened as PK columns have NOT NULL constraints and cannot be dropped. 

‌

An additional subset of connectors experienced sync failures longer than others because they remained on the PR with the faulty logic longer. 

‌

To resolve the issue, Fivetran took the below steps: 

• Reverted the PR with the faulty logic
• Developed a strategy to DROP deprecated columns for customers
• Developed and shared a Playbook for customers with edge-cases 

‌

Root cause 

The PR that was deployed included logic to handle datatype changes for Postgres destinations. For String columns, the PR incorrectly expected the datatype to be VARCHAR, but the Postgres equivalent of VARCHAR is CHARACTER VARYING. This meant that for each String column, the datatype check failed, signaling there was a change in datatype and leading Fivetran to update the datatypes from String to String.

‌

This faulty logic was originally isolated under a Feature Flag on a testing account. It was inadvertently introduced into the main code base as part of a Feature Flag cleanup task. 

‌

Chronology of Key Events (All Times in UTC):

‌

‌

Mitigation

• The PR with the faulty logic was reverted

• Two clean-up tasks were created to assist customers

  • Our engineers developed a strategy to DROP deprecated columns for customers
  • We created and shared a Playbook to assist customers with edge-cases not covered by clean-up automation

• We’ve added additional processes, testing, and alerting to Feature Flag cleanups including:

  • More robust sign-off checklist for Feature Flag cleanup
  • Additional datatype validation testing for Postgres
  • Validation tests for view dependencies
  • Automated Alerts for an spikes in Warnings