How Do You Detect Breaking Fivetran Schema Changes Before They Break dbt Models?
Five layers that turn connector drift from a 3 a.m. failure into a gated, reviewed change
To catch a breaking Fivetran schema change before it breaks dbt, compare the destination's column metadata against a stored snapshot before each dbt run, and block the run when a column was removed, renamed or retyped. The Fivetran schema migration docs list renames, primary key changes, type changes and column removal as the kinds of change a connector can push into your destination. dbt then enforces the shape you declared with model contracts. This guide wires those pieces into five layers.
Key Takeaways
- •Fivetran's schema migration docs list column and table renames, primary key changes, column data type changes and column removal as change types your destination can receive.
- •A column rename reaches the destination as one column going missing and another appearing, so a snapshot diff must treat removed columns as the breaking signal.
- •dbt's default on_schema_change setting is ignore: a new column never reaches an incremental target, and a removed column makes dbt run fail.
- •An enforced dbt model contract stops a model from building when column names, data types or column counts no longer match the declaration.
- •The cheapest reliable defence is a pre-run gate: diff the destination schema against a snapshot, then let the orchestrator skip dbt for affected models and open a review instead of failing at 3 a.m.
Why connector drift breaks dbt in the first place
A connector copies whatever the source system looks like today. When an application team renames a column, changes a type or drops a field, the source is correct and your pipeline is not. Fivetran's page describes these as schema migrations it handles in the destination, but it cannot know that a downstream dbt model selects the old column by name. That knowledge lives in your project, not in the connector.
The failure usually shows up late. The sync succeeds, the freshness check passes, and the first symptom is a dbt run failing on a missing column or, worse, a silent change in a downstream number because a type change altered a join or a cast. The people who changed the source are rarely the people paged for the failure.
So the goal is not to stop the source from changing. The goal is to find out about the change between the sync and the dbt run, classify it, and decide whether the run should proceed. The five layers below do that in order, from cheapest detection to hardest enforcement.
Layer 1: snapshot the destination schema
You cannot detect a change without a baseline. Create a small table that stores the column list of every Fivetran-managed schema as of the last successful dbt run: table name, column name, data type and the timestamp it was captured. Populate it from the warehouse's own metadata, such as the Snowflake INFORMATION_SCHEMA.COLUMNS view, and refresh it only after a run finishes cleanly.
Refreshing only after success matters. If you refresh the snapshot on every check, a breaking change is reported once and then becomes the new normal. Keeping the baseline pinned to the last good run means the change stays flagged until someone either fixes the models or deliberately accepts the new shape.
Scope the snapshot to the schemas your dbt sources actually read. Fivetran may sync hundreds of tables you never model, and diffing all of them just produces noise that teaches the team to ignore the alert.
Layer 2: diff and classify the change
Run a diff query before dbt starts. On Snowflake, a minimal version looks like this: select s.table_name, s.column_name, s.data_type as old_type, c.data_type as new_type, iff(c.column_name is null, 'removed', 'type_change') as change from schema_snapshot s left join information_schema.columns c on c.table_schema = 'SHOP' and c.table_name = s.table_name and c.column_name = s.column_name where c.column_name is null or c.data_type <> s.data_type. Each row is a candidate break.
Notice what the query does not report: new columns. An added column is usually safe, so it goes to a separate informational list. A rename shows up as one removed row plus one new column on the same table, and that pair is the pattern to flag as a probable rename. Primary key changes need a second check against the key columns, because the data type may be unchanged.
Then classify. Removed and retyped columns that a dbt source or model references are blocking. Removed columns nothing references are warnings. You get the reference list from dbt's manifest, which records every source column a model touches in its compiled SQL and documented columns.
Layer 3: put contracts on the models that face consumers
The diff protects the boundary between Fivetran and your staging layer. Contracts protect the boundary between your models and their consumers. With an enforced contract, dbt compares the model's output to the declared names and types before building, so a drift that slipped past the gate fails loudly instead of changing a dashboard quietly.
A minimal contract looks like this: models: - name: stg_shop__orders, config: {contract: {enforced: true}}, columns: [{name: order_id, data_type: int, constraints: [{type: not_null}]}, {name: order_total, data_type: numeric(18,2)}]. The dbt contract docs say the contract must include every column's name and data_type, and that constraint support varies by platform.
When the connector retypes order_total, the build stops with the format dbt documents: This model has an enforced contract that failed. Please ensure the name, data_type, and number of columns in your contract match the columns in your model's definition. followed by a table naming the column, the definition type, the contract type and data type mismatch. That message is far better than a wrong number in a board deck.
Layer 4: set on_schema_change deliberately on incremental models
Incremental models are where drift hides longest, because they do not rebuild from scratch. The dbt incremental models docs state that ignore is the default. Under it, a column added upstream is not added to the target table, and a removed column causes dbt run to fail. A new field can be missing for weeks without any error.
The alternatives trade safety for convenience. fail raises an error when source and target schemas diverge. append_new_columns adds new columns but, per the docs, does not remove columns missing from the new data. sync_all_columns adds new columns and removes columns that are now missing, and the docs note it includes data type changes.
For models behind a contract we choose fail or append_new_columns, since the contract docs only allow those two for incremental models. We avoid sync_all_columns on anything a consumer reads, because it will remove a column in the target to match the source. Dropping a column is an action that deserves a human decision. None of these settings backfill values for a newly added column, so a full refresh is still needed to populate history.
Layer 5: gate the orchestrator, not just the model
The last layer connects detection to action. Make the diff a task that runs after the Fivetran sync and before dbt in Airflow or Dagster. If it returns blocking rows, skip only the dbt models downstream of the affected sources, run everything else, and send the diff to whoever owns those models. The pipeline keeps moving and the broken slice waits for a decision.
Each blocking diff should carry three facts: what changed, which models and exposures are downstream, and a proposed fix. For a rename the fix is usually an alias in the staging model, such as select new_col as old_col, so downstream models keep working while the team migrates names on its own schedule.
This is also the layer where manual work piles up. Someone has to read the diff, trace the blast radius and write the patch. A short runbook plus a clear owner for each source schema keeps it manageable, and it is the part worth automating once the first four layers are stable.
Where Data Workers fits
The five layers above are plain SQL, YAML and orchestrator config. You can build them yourself. Our Schema Agent is designed to take on the repetitive parts: watching column-level drift on synced tables, tracing which models sit downstream, and drafting a migration or alias for a person to review.
It runs as an MCP server, so you can ask from Claude Code or Cursor, "What's the blast radius if I drop this column?" or "What changed in the orders schema since last deploy?" The open-source edition is read-only by default, so it reports and proposes. Writes and approval workflows belong to the Pro edition.
The Pipeline Agent is the natural partner for layer 5, since the skip-and-review decision touches the orchestrator. We would still start with the contracts and the diff query, because they work with or without an agent and make the agent's job easier.
Frequently Asked Questions
Does Fivetran tell me when a schema change will break dbt?
No. Fivetran documents how it handles schema migrations in the destination, but it has no view of your dbt project. Whether a rename or retype breaks anything depends on which models select the affected column. That is why the check has to compare the destination against what your models reference, which is what the diff in layer 2 does.
Should I use sync_all_columns to keep dbt in step with the source?
Only for models nobody depends on yet. The dbt docs say sync_all_columns adds new columns and removes missing ones, including data type changes. That keeps the target tidy, but it can drop a column a dashboard still reads. For consumer-facing models, fail or append_new_columns plus a contract is safer.
How do I tell a rename from a deletion?
A diff cannot know intent, but it can show the pattern. A rename appears as one removed column and one added column on the same table, often with the same data type and position. Flag that pair as a probable rename and ask the owner to confirm. A lone removed column with no new partner is more likely a deletion.
Can I do this without an agent?
Yes. A snapshot table, a diff query, dbt contracts and an orchestrator task cover the detection and gating. An agent adds value when the volume of diffs makes triage and drafting patches the bottleneck. Start with the manual version so you know what normal drift looks like in your estate.