dbt test failed: schema mismatch on incremental model

TL;DR

Incremental models only apply new rows, so a column added, dropped, or retyped in the warehouse (or upstream) never gets reconciled and the test fails on the mismatch. Compare the model's compiled SQL column list against the actual table, then either full-refresh the model or add an onschemachange policy that handles the drift. Do not just rerun and hope the schema converges.

dbt test failed: schema mismatch on incremental model

Use this when

• A dbt test fails on an incremental model with a schema complaint.
• The model passed when it was last full-refreshed but fails now.
• Someone changed an upstream column type or added a column recently.

Not for this skill when

• The test fails on bad values, not on columns. That is a data quality problem.
• A non-incremental model fails the same test. Look at the model SQL itself.

Steps

1. Read the failing test output and identify the exact column and the expected versus actual type. Note the mismatched column. Verify: the mismatch is precise, not vague
2. Compare the model's select list against the live table definition. Note which side changed. Verify: you know whether the model or the table drifted
3. Decide: one-off drift gets a full-refresh of the model; recurring drift gets an onschemachange config like syncallcolumns. Note the policy is set deliberately. Verify: the fix matches the frequency of the problem
4. Apply the fix and re-run the model plus the failing test. Note the test passes. Verify: schema and test agree again
5. Add a schema-change test or contract to the model so the next drift fails fast with a clear message. Note the guard is in place. Verify: future drift is caught early

Variant phrasings

dbt incremental schema change failed

The cause-shaped search.

dbt onschemachange syncallcolumns

The config-shaped search.

Compatibility: dbt Core 1.x. onschemachange policies are supported on BigQuery, Snowflake, Databricks, and Postgres adapters with small differences.

Why it happens

An incremental model is a contract between the SQL you wrote and the table as it exists, and dbt only enforces that contract at full-refresh time. Between refreshes, upstream changes accumulate silently: a widened column, a dropped field, a renamed key. The test is the first thing that notices, because it compares expectations against the real table. The mismatch is the system working; the surprise is that incremental mode lets the two sides drift apart in the first place.

Edge cases / pitfalls

• syncallcolumns handles added columns but type changes can still need a full refresh. Know which policy covers which drift.
• A full-refresh on a huge table is expensive. Schedule drift checks so refreshes happen deliberately, not as panic fixes.
• Dropping a column upstream with syncallcolumns can drop it from the model output silently. Review column drops, do not auto-accept them.
• Tests that select * inherit drift invisibly. Explicit column lists make mismatches loud.

Provenance

Resolved from the public thread: https://vectle.com/posts/psthHQ2Ozq_yPyvvUUTLVjqg