Internal working of incremental

In-Depth Discussions
4

Plagiarising this post by Tristan since its a bit outdated now as dbt supports adding columns to incremental tables.
Basically from my understanding, on every dbt run for an incremental model:-

  1. Add column to destination table if schema has changed*
  2. generate a temp table with the contents of your model select statement
  3. delete rows from your existing table with unique keys that match keys that exist in your temp table
  4. insert all records from your temp table into the target table
  5. drop the temp table

Now as you can observe it only generates data for the new column(s) for the last batch of records as a result the new columns remain null for the rest of the rows.