how to handle schema changes in dbt sources

TL;DR

Decide up front whether your models tolerate new columns or must fail loudly, then configure dbt to enforce that choice. Use model contracts to lock the shape of critical marts, on_schema_change to control incremental drift, and source freshness checks to notice when the raw data moves under you. Silent schema drift is how dashboards break on a Tuesday with no code change from your team.

how to handle schema changes in dbt sources

Use this when

• a source table gained or lost a column and models broke
• new columns appear in raw data and you are unsure if models picked them up
• you want contracts that fail the build when shapes change

Not for this skill when

• the schema change is inside one model you control, just edit the model
• you are backfilling data for a date range, that is a different skill
• the issue is a renamed source table, update sources yaml

Steps

1. Declare the expected shape with a model contract on critical marts, so drift fails the build instead of the dashboard:

models:
  - name: fct_orders
    config:
      contract: {enforced: true}
    columns:
      - name: order_id
        data_type: integer
      - name: order_total
        data_type: numeric

Expected output: dbt fails the build if the model SQL produces a different shape than declared. The contract is the tripwire, and updating it is a deliberate act.

1. Choose an onschemachange policy for incremental models instead of accepting the default behavior blindly:

models:
  - name: fct_orders
    config:
      materialized: incremental
      on_schema_change: append_new_columns

Expected output: new source columns get added automatically on the next run. Use fail instead when any drift should block the deploy until a human reviews it.

1. Add freshness checks to sources so stale or moved tables get noticed before anyone queries them:

sources:
  - name: raw
    tables:
      - name: orders
        freshness:
          warn_after: {count: 6, period: hour}
          error_after: {count: 24, period: hour}

Expected output: dbt source freshness warns or errors when the table stops updating, which is often the first sign of an upstream change or an outage.

1. Detect unexpected columns with a quick comparison query after any upstream deploy:

SELECT column_name
FROM information_schema.columns
WHERE table_name = 'orders' AND table_schema = 'raw'
EXCEPT
SELECT column_name
FROM information_schema.columns
WHERE table_name = 'stg_orders' AND table_schema = 'analytics';

Expected output: columns in the source that staging does not know about. Review the list after every upstream deploy and decide what to do with each one.

1. Run a full build after any upstream change to flush out breakage early, in CI before merging:

dbt build --select state:modified+ --full-refresh

Expected output: contracts, tests, and models all re-verify against the new shape. Finding the break in CI beats finding it in the Monday metrics review.

Variant phrasings

dbt source table added column breaking model

If the model uses SELECT *, new columns flow through silently and something downstream eventually chokes. Prefer explicit column lists in staging so additions are a conscious choice.

dbt onschemachange appendnewcolumns vs syncallcolumns

Append adds new columns, sync also drops removed ones. Sync is destructive, only use it when you are sure nothing downstream needs the old columns.

dbt contract enforced breaking change

That is the contract doing its job, not a bug. Update the yaml to bless the new shape deliberately, do not just delete the contract to make CI green.

Why it happens

Upstream producers change tables without telling you: columns get added, renamed, or dropped by deploys you never see. dbt models that SELECT * absorb the change silently, and models with contracts fail loudly. Both behaviors are configurable, but the default is silence, which is why drift shows up as a broken dashboard instead of a failed build.

Edge cases

• Renamed columns look like a drop plus an add. No automation distinguishes them, a human has to map old to new.
• Type changes (integer to numeric) break contracts even when the data is fine. Update the declared type and move on.
• BigQuery and Snowflake handle schema evolution differently at the warehouse level. Test onschemachange on your actual warehouse, not from docs alone.
• Seeds with new columns need --full-refresh, since incremental logic does not apply to CSV uploads.

Provenance

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