Fivetran Schema Drift: Change-Handling Policies and a Re-Sync Playbook That Will Not Wreck Your Bill

Every Fivetran incident review eventually reaches the same sentence: "the source changed and nobody noticed until the dashboard broke." Schema drift is not an edge case — it is the normal behaviour of every production source system. A Salesforce admin adds a custom field on Tuesday, an application team drops a deprecated column in a Postgres migration on Thursday, and a SaaS vendor renames an API object in a minor release you never read the changelog for.

Fivetran handles most of this automatically, which is exactly why teams stop thinking about it. The automatic behaviour is a policy with real cost and governance consequences, and when drift does break something, the recovery step people reach for — a full re-sync — is the single most expensive button in the product.

This tutorial covers both halves: configuring schema change handling deliberately, and recovering from drift without re-billing your entire history.

1. Know which policy each connection is running

Every Fivetran connection has a schema change handling setting, found under Connection → Schema tab. There are three values:

Policy	New tables	New columns
Allow all	Synced automatically	Synced automatically
Allow columns	Blocked	Synced automatically
Block all	Blocked	Blocked

The default is Allow all, and for a marketing SaaS source that is usually right. For a production application database with 900 tables, of which you want eleven, it is how a $2,000 monthly bill becomes a $9,000 one after a release adds an audit-log table.

Audit what you actually have before changing anything:

for GROUP in $(curl -s -u "$FIVETRAN_API_KEY:$FIVETRAN_API_SECRET" \
    https://api.fivetran.com/v1/groups | jq -r '.data.items[].id'); do
  curl -s -u "$FIVETRAN_API_KEY:$FIVETRAN_API_SECRET" \
    "https://api.fivetran.com/v1/groups/$GROUP/connections" \
  | jq -r --arg g "$GROUP" \
    '.data.items[] | [$g, .id, .service, .schema_status // "n/a"] | @tsv'
done

Then read the per-connection schema config, which is where the policy lives:

curl -s -u "$FIVETRAN_API_KEY:$FIVETRAN_API_SECRET" \
  https://api.fivetran.com/v1/connections/<connection_id>/schemas \
| jq '{policy: .data.schema_change_handling,
       tables: [.data.schemas[].tables | to_entries[]
                | select(.value.enabled) | .key]}'

Write the output somewhere durable. In most accounts this single query produces the first honest inventory of what is actually being replicated.

2. Choose a policy per source class, not per account

A blanket rule across an account is always wrong somewhere. A workable default:

• Application databases (Postgres, MySQL, SQL Server, Mongo): ALLOW_COLUMNS. New columns on tables you already model are almost always wanted and are cheap — MAR is counted per row, not per column. New tables are the expensive surprise, so make them a decision.
• SaaS CRM and finance sources (Salesforce, NetSuite, Zendesk): ALLOW_ALL. Objects here are added rarely and analysts genuinely want them. The exception is Salesforce orgs with heavy managed packages, where a package install can add hundreds of objects overnight — those deserve ALLOW_COLUMNS too.
• Event and log sources: BLOCK_ALL, with tables enabled explicitly. These sources grow tables the way a laptop grows browser tabs.

Set it via the API so the change is reviewable and repeatable:

curl -s -X PATCH \
  -u "$FIVETRAN_API_KEY:$FIVETRAN_API_SECRET" \
  -H "Content-Type: application/json" \
  -d '{"schema_change_handling": "ALLOW_COLUMNS"}' \
  https://api.fivetran.com/v1/connections/<connection_id>/schemas

If you manage Fivetran with the Terraform provider, this belongs in the fivetran_connection_schema_config resource instead, so the policy and the enabled-table list are reviewed in the same pull request as everything else.

Blocking is not silent. Fivetran records blocked tables and columns and surfaces them in the schema tab and in the schema_change events of the Fivetran Platform Connector — so "block by default" does not mean "never find out". It means you find out in a review queue instead of in an invoice.

3. Make drift visible before it becomes an incident

The Platform Connector logs schema changes into your warehouse. That makes drift queryable, which is the only way it gets noticed by anyone other than the person who broke it.

-- New and altered objects in the last 7 days, per connection
select
    l.connection_id,
    l.time_stamp::date            as change_date,
    l.event                        as event_type,
    l.message_data:table::string   as table_name,
    l.message_data:column::string  as column_name,
    l.message_data:level::string   as severity
from fivetran_platform.log l
where l.time_stamp >= dateadd(day, -7, current_timestamp())
  and l.event in ('alter_table', 'create_table', 'schema_change')
order by change_date desc, connection_id;

Two things to do with that query:

1. Route it to a channel, not a dashboard. A weekly digest of "these 6 columns and 1 table appeared" in the data team's Slack takes ninety seconds to read and prevents the quarterly surprise.
2. Alert on blocked objects specifically. A blocked table that nobody unblocks is a silent data gap: the analyst asking why the new subscription_tier field is missing will otherwise be your alerting system.

Pair it with a source freshness check, because the second failure mode of drift is not a missing column but a stalled connection:

select connection_id,
       max(time_stamp) as last_event,
       datediff(hour, max(time_stamp), current_timestamp()) as hours_since
from fivetran_platform.log
where event = 'sync_end'
group by 1
having hours_since > 12;

4. When drift breaks a downstream model

Column added: nothing breaks. select * models pick it up, explicit models ignore it. Fine.

Column dropped or renamed at the source: Fivetran keeps the old column in the destination and stops populating it (or, for renames, creates a new one alongside). Your model does not error — it quietly returns nulls, which is worse. Catch it with a dbt test rather than by intuition:

models:
  - name: stg_salesforce__opportunity
    columns:
      - name: forecast_category
        tests:
          - not_null:
              config:
                severity: warn
                error_if: ">1000"

Type widened at the source (an int that becomes a bigint, a varchar(50) that becomes text): Fivetran widens the destination column automatically. Type narrowed or changed incompatibly is the case that needs human attention — Fivetran will typically create a new column with a type suffix rather than destroy data, and your model needs to coalesce across both during the transition.

5. Re-sync surgically, not globally

Here is the rule that saves the most money in this entire article: a connection-level re-sync re-reads every table and re-counts every row as monthly active rows. On a source with 400 million historical rows, that is a five-figure line item to fix one broken table.

Almost every drift-related recovery only needs one table. Fivetran supports a table-scoped re-sync through the API:

curl -s -X POST \
  -u "$FIVETRAN_API_KEY:$FIVETRAN_API_SECRET" \
  -H "Content-Type: application/json" \
  -d '{"scope": {"public": ["opportunity"]}}' \
  https://api.fivetran.com/v1/connections/<connection_id>/schemas/tables/resync

Before you run it, work out what it will cost:

-- Rough MAR impact of re-syncing one table
select count(*) as rows_to_recount
from raw.salesforce.opportunity;

Compare that number against your monthly MAR baseline. If a table re-sync would move the needle by more than a few percent, consider the cheaper alternatives first:

• Re-sync the failed table only for a bounded period where the connector supports a start-date or historical-window setting, rather than from the beginning of time.
• Backfill from an existing snapshot. If the table has history mode, or you keep a data-lake copy, you may already own the rows you would otherwise pay to re-read.
• Repair downstream instead of upstream. A rename that only affected a derived model does not need a re-sync at all; it needs an updated stg_ model.

Reserve the full connection re-sync for genuine corruption: a botched destination migration, a primary key change, or a source that changed its own historical data behind your back.

6. Handling planned source migrations

Drift you cause yourself is the manageable kind, if you sequence it. When an application team plans a schema migration on a replicated database:

1. Pause the connection before the migration window. Fivetran resumes from the last position; for log-based CDC sources, confirm your replication slot or log retention exceeds the pause duration or you will be forced into exactly the re-sync you were avoiding.
2. Let the migration run.
3. Resume and watch the first sync. Check the schema tab for new blocked objects and the Platform Connector log for alter_table events.
4. Update models and unblock intentionally. New columns you actually want get enabled and documented in the same change.

Point 1 is the one with teeth. A Postgres logical replication slot held open through a long pause causes WAL to accumulate on the primary; a slot dropped by a DBA who saw disk filling up costs you the full history. Agree the window with whoever owns the database, and tell them the number.

7. A short checklist

• Every connection has an explicitly chosen schema change handling policy, not a default one.
• Enabled tables and the policy live in version control (Terraform) or, at minimum, in a documented API-generated inventory.
• Weekly digest of schema changes and blocked objects goes to a human channel.
• dbt tests warn on columns that quietly go all-null.
• Nobody has permission or habit to press connection-level re-sync without a row-count estimate first.
• Planned source migrations follow pause → migrate → resume → review, with log retention checked.

Schema drift is not a problem to eliminate; sources will keep changing and that is a sign the business is alive. The goal is that every change arrives as a reviewable line in a weekly digest instead of an urgent message about a broken executive dashboard.

If you would like help auditing schema policies across an account, sizing a re-sync before someone triggers it, or building the drift-monitoring models on top of the Platform Connector, our Fivetran connectors configuration and management and performance tuning teams do this work every week — or get in touch with a connection list and we will take a look.