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/behaviorfail: stop if schemas differ (safe, explicit)append_new_columns: add new columns to the existing table; do not remove old onessync_all_columns: attempt to add/remove columns to match the model (adapter-specific; use carefully)
{{ config(
materialized='incremental',
unique_key='id',
on_schema_change='append_new_columns'
) }}Mental model
model SQL gains column "discount"
|
on_schema_change decides:
fail | ignore | append_new_columns | sync_all_columnsPractical advice
- Use
append_new_columnsfor additive, backward-compatible changes. - Use
failin strict CI to force a conscious--full-refreshor migration. - Do not assume
sync_all_columnsis 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.
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 droppedNeither 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.