## 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.

```text
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:

```yaml
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.

2. Choose an on_schema_change policy for incremental models instead of accepting the default behavior blindly:

```yaml
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.

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

```yaml
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.

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

```sql
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.

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

```shell
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 on_schema_change append_new_columns vs sync_all_columns
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 on_schema_change 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