You can either --full-refresh all the incremental models or perform this schema migration outside of dbt.
I would recommend using --full-refresh if you are able to. since --full-refresh rebuilds the table, it takes care of the schema changes and the historical values of the new column.
On the current version of dbt v0.21.0, a new incremental setting was introduced, on_schema_change. You can set it to append_new_columns
Quoting some relevant sections from the documentation:
New on_schema_change config in dbt version v0.21.0
Incremental models can now be configured to include an optional on_schema_change parameter to enable additional control when incremental model columns change. These options enable dbt to continue running incremental models in the presence of schema changes, resulting in fewer --full-refresh scenarios and saving query costs.
append_new_columns: Append new columns to the existing table. Note that this setting does not remove columns from the existing table that are not present in the new data.
Note: None of the on_schema_change behaviors backfill values in old records for newly added columns. If you need to populate those values, we recommend running manual updates, or triggering a --full-refresh.
If --full-refresh is not an option or you're on an older dbt version, then your schema migration would have to be done manually.
The steps are:
- add the new column via an alter table
alter table my_incremental_table add column new_column_name data_type
- run an update query to hydrate the new column
- edit the dbt model for
my_incremental_table to add new_column_name to the end of the select query's list of columns
This will work because dbt is stateless, but as this is a manual operation, I don't recommend this if you can avoid it.
note also, that if you use the on_schema_change method, you'd still need to do a backfill of the new column manually.
