13. Incremental Upserts with unique_key
Difficulty: Hard · Topic: Incremental models
{{ source('shop', 'orders') }} is an append-only change log: every time an order changes (placed → shipped → delivered) a new row arrives with a later updated_at.
Make fct_orders an incremental model that holds exactly one row per order_id, its latest version (order_id, status, amount, updated_at). On each run, process only change rows newer than the newest updated_at already in the table, and update orders that already exist instead of adding duplicates.
One batch can contain several changes to the same order. No two changes to one order share an updated_at.
Your model is compiled like dbt does and run in DuckDB against 4 test cases, including hidden edge cases.
Sample inputs
{{ source('shop', 'orders') }}
| order_id | status | amount | updated_at |
|---|---|---|---|
| 1 | placed | 20 | 2024-03-01 10:00:00 |
| 2 | shipped | 35 | 2024-03-01 11:00:00 |
| 1 | shipped | 20 | 2024-03-02 09:00:00 |
| 3 | placed | 12 | 2024-03-02 10:00:00 |
The table before the run
| order_id | status | amount | updated_at |
|---|---|---|---|
| 1 | placed | 20 | 2024-03-01 10:00:00 |
| 2 | shipped | 35 | 2024-03-01 11:00:00 |
Expected output
| order_id | status | amount | updated_at |
|---|---|---|---|
| 2 | shipped | 35 | 2024-03-01 11:00:00 |
| 1 | shipped | 20 | 2024-03-02 09:00:00 |
| 3 | placed | 12 | 2024-03-02 10:00:00 |
Hints
Hint 1
unique_key='order_id' makes dbt replace existing rows that have the same key (delete+insert is dbt-duckdb's default; incremental_strategy='merge' works too).Hint 2
dbt replaces rows with whatever your SELECT returns. If the new batch has two versions of order 7, both are inserted: deduplicate to the latest version inside the model.Hint 3
Number each order's changes newest first withrow_number() and keep the first one (DuckDB supports qualify). Do it after the incremental filter, on every run.