15. Incremental Model with a Composite Key
Difficulty: Hard · Topic: Incremental models
The ERP extracts stock levels into {{ source('erp', 'stock_snapshots') }}. The stock level of a SKU in a warehouse on a day can be re-extracted later with a corrected on_hand; the row with the latest extracted_at is the truth. Corrections arrive at most one day late.
Build the incremental model fct_inventory_daily with one row per warehouse_id, sku and snapshot_date: warehouse_id, sku, snapshot_date, on_hand.
On an incremental run, take every source row whose snapshot_date is at or after the latest snapshot_date in the table minus 1 day, keep the latest extract per key, and upsert it on the three-column key. Older days are left as they are.
Your model is compiled like dbt does and run in DuckDB against 4 test cases, including hidden edge cases.
Sample inputs
{{ source('erp', 'stock_snapshots') }}
| warehouse_id | sku | snapshot_date | on_hand | extracted_at |
|---|---|---|---|---|
| 1 | A | 2024-09-01 | 10 | 2024-09-01 23:00:00 |
| 1 | B | 2024-09-01 | 5 | 2024-09-01 23:00:00 |
| 1 | A | 2024-09-02 | 8 | 2024-09-02 23:00:00 |
| 1 | B | 2024-09-02 | 4 | 2024-09-03 01:00:00 |
| 1 | A | 2024-09-03 | 7 | 2024-09-03 23:00:00 |
The table before the run
| warehouse_id | sku | snapshot_date | on_hand |
|---|---|---|---|
| 1 | A | 2024-09-01 | 10 |
| 1 | B | 2024-09-01 | 5 |
| 1 | A | 2024-09-02 | 8 |
Expected output
| warehouse_id | sku | snapshot_date | on_hand |
|---|---|---|---|
| 1 | A | 2024-09-01 | 10 |
| 1 | B | 2024-09-01 | 5 |
| 1 | A | 2024-09-02 | 8 |
| 1 | B | 2024-09-02 | 4 |
| 1 | A | 2024-09-03 | 7 |
Hints
Hint 1
A composite key is a list:unique_key=['warehouse_id', 'sku', 'snapshot_date'].Hint 2
Deduplicate the batch to the latestextracted_at per key, or dbt inserts two rows for one key.Hint 3
Filter:snapshot_date >= (select max(snapshot_date) from {{ this }}) - interval 1 day.