dbt · Incremental Models & Performance

on_schema_change

Hardon_schema_changeschema evolutionincremental

Interview question

What does on_schema_change do on incremental models?

How to answer

The short answer

on_schema_change tells dbt what to do when the compiled SELECT columns differ from the existing incremental table schema.

Common settings

  • ignore (often default): new columns in SQL may not appear in the table; mismatches can error depending on adapter/behavior
  • fail: stop if schemas differ (safe, explicit)
  • append_new_columns: add new columns to the existing table; do not remove old ones
  • sync_all_columns: attempt to add/remove columns to match the model (adapter-specific; use carefully)
SQL
{{ config(
    materialized='incremental',
    unique_key='id',
    on_schema_change='append_new_columns'
) }}

Mental model

Output
model SQL gains column "discount"
        |
on_schema_change decides:
  fail | ignore | append_new_columns | sync_all_columns

Practical advice

  • Use append_new_columns for additive, backward-compatible changes.
  • Use fail in strict CI to force a conscious --full-refresh or migration.
  • Do not assume sync_all_columns is free or perfectly portable across warehouses.

Interview tip: Schema evolution on incremental tables is a production topic. Mention you pick a policy deliberately instead of discovering missing columns months later.

Worked example

Day 1, the incremental table has order_id and amount. Day 2, the model SQL gains a discount column.

Output
fail                 -> the run stops with an error about the schema change
ignore               -> the run succeeds, but the table still has no discount column
append_new_columns   -> discount is added; older rows hold NULL for it
sync_all_columns     -> discount is added, and columns no longer selected are dropped

Neither append_new_columns nor sync_all_columns old rows. If the new column must have a real value for history, run a --full-refresh of that model after the change.