dbt Snapshot vs Incremental Model
People think a dbt snapshot and an incremental model do the same job, since both add rows to a table over time. An incremental model adds new rows to save time, and a changed row can replace the old one. A snapshot is there to keep history. A changed row is added as a new version, and the old one stays. Use a snapshot when you need to know what a value used to be.
dbt Snapshot
A dbt table that keeps each old version of a row that changes.
A dbt snapshot keeps the history of a table whose rows change in place. Each time dbt snapshot runs, it compares the table with the last run. When a row has changed, it closes the old version and adds the new one. Two columns, dbt_valid_from and dbt_valid_to, say when each version was true. This builds a type 2 slowly changing dimension.
Incremental Model (dbt)
A dbt model that adds just the new rows on each run.
An incremental model is a dbt model that adds only new or changed rows on each run. The first run builds the whole table. Later runs use the is_incremental() check to pick just the new rows and add them. This is much faster than rebuilding a big table every time. The --full-refresh flag rebuilds it from scratch.
In one line
- dbt Snapshot
- A dbt table that keeps each old version of a row that changes.
- Incremental Model (dbt)
- A dbt model that adds just the new rows on each run.