Enquiry on incremental materialisation

Help
1

Hello,

A few general enquiries on incremental materilisation.

  1. If I add new columns in a table that is materialised as “incremental”. Is the only way to pick up these new columns in the next refresh via --full-refresh? I’d like to confirm that without --full-refresh, and even there are incremental rows that can be picked up, the new columns will not be reflected?

  2. As such, if I add new columns into incremental models. Whenever I merge my dev branch into prod, would I need to manually update my prod job to --full-refresh for once then removing the --full-refresh flag after the 1st run is completed. Would dbt “knows” new columns are added based on the PR such that the user can avoid the manual change of prod job, please?

Thanks,
Oliver

2

I guess it depends on your implementation, but I know that at least on bigquery your build will fail if you’re adding new tables to an incremental table without a --full-refresh.

I’m not sure what your use case is, but if that’s going to cause a problem maybe snapshots would be a better solution?

3

Have a look at the on-schema-change configurations of your incremental model: Incremental models | dbt Developer Hub

If you use append_new_columns, dbt will add the new column and include its values (for new rows). dbt will never go back and do a backfill of old data without you issuing a --full-refresh command.

4

Thanks @joellabes that is exactly what I am looking for.