12. Append-Only Incremental Events
Difficulty: Medium · Topic: Incremental models
fct_events copies {{ source('app', 'events') }} (event_id, user_id, event_type, event_at). Rebuilding it every hour is too slow, so make it an incremental model: the first run builds the table, and every later run only appends events newer than the newest event already in the table.
Events never change once written. The raw source keeps only 30 days of events, so the table holds history the source no longer has: a rebuild would lose it.
Some tests start from an existing table (an incremental run), some from nothing (the first run). The table you end up with is what is graded.
Your model is compiled like dbt does and run in DuckDB against 4 test cases, including hidden edge cases.
Sample inputs
{{ source('app', 'events') }}
| event_id | user_id | event_type | event_at |
|---|---|---|---|
| 2 | 11 | click | 2024-05-01 09:00:00 |
| 3 | 10 | purchase | 2024-05-01 10:00:00 |
| 4 | 12 | view | 2024-05-02 00:00:00 |
The table before the run
| event_id | user_id | event_type | event_at |
|---|---|---|---|
| 1 | 10 | view | 2024-05-01 08:00:00 |
| 2 | 11 | click | 2024-05-01 09:00:00 |
Expected output
| event_id | user_id | event_type | event_at |
|---|---|---|---|
| 1 | 10 | view | 2024-05-01 08:00:00 |
| 2 | 11 | click | 2024-05-01 09:00:00 |
| 3 | 10 | purchase | 2024-05-01 10:00:00 |
| 4 | 12 | view | 2024-05-02 00:00:00 |
Hints
Hint 1
{{ config(materialized='incremental') }} at the top. Without a unique_key, new rows are simply inserted.Hint 2
Wrap the filter in{% if is_incremental() %} ... {% endif %}: it is false on the first run, when the table does not exist yet.Hint 3
Newer than the newest:where event_at > (select max(event_at) from {{ this }}). With >= the newest event is inserted a second time on every run.