14. Late Data: Incremental with a Lookback
Difficulty: Hard · Topic: Incremental models
daily_page_views aggregates {{ source('web', 'page_views') }} to one row per day: view_date (the date of viewed_at), views (count) and users (distinct user_id).
Mobile apps upload views late, up to two days after they happened, so the newest days in the table are often incomplete. Make it incremental so that each run recomputes, from the source, every day from 2 days before the latest view_date already in the table onwards, replacing those days. Older days are final and are not touched.
Example: if the table's latest day is 10 June, the run recomputes 8 June, 9 June, 10 June and anything newer.
Your model is compiled like dbt does and run in DuckDB against 3 test cases, including hidden edge cases.
Sample inputs
{{ source('web', 'page_views') }}
| view_id | user_id | viewed_at |
|---|---|---|
| 1 | 1 | 2024-06-07 10:00:00 |
| 2 | 1 | 2024-06-07 11:00:00 |
| 3 | 2 | 2024-06-07 12:00:00 |
| 4 | 1 | 2024-06-08 09:00:00 |
| 5 | 3 | 2024-06-08 10:00:00 |
| 6 | 2 | 2024-06-09 08:00:00 |
| 7 | 4 | 2024-06-09 23:00:00 |
| 8 | 1 | 2024-06-10 07:00:00 |
| 9 | 5 | 2024-06-10 13:00:00 |
| 10 | 5 | 2024-06-11 01:00:00 |
| 11 | 6 | 2024-06-06 22:00:00 |
The table before the run
| view_date | views | users |
|---|---|---|
| 2024-06-07 | 3 | 2 |
| 2024-06-08 | 2 | 2 |
| 2024-06-09 | 1 | 1 |
| 2024-06-10 | 1 | 1 |
Expected output
| view_date | views | users |
|---|---|---|
| 2024-06-07 | 3 | 2 |
| 2024-06-08 | 2 | 2 |
| 2024-06-09 | 2 | 2 |
| 2024-06-10 | 2 | 2 |
| 2024-06-11 | 1 | 1 |
Hints
Hint 1
The grain is a day, so the unique key isview_date: recomputed days replace the old rows for those days.Hint 2
Filter on whole days, not timestamps:where cast(viewed_at as date) >= (select max(view_date) from {{ this }}) - interval 2 day. A filter on the newest timestamp would recompute a day from only part of its rows.