System Design · hard
Design an incremental dbt model for a table where events can arrive late. What do you use as the incremental filter?
Asked in Analytics Engineer interviews, in the System Design round.
Short answer
Filter on event time with a lookback window (in is_incremental(), where event_time >= (select max(event_time) from this model) minus a few days) and set a unique_key so late rows update instead of duplicating. Use merge or delete+insert on the partition, not append. State the tradeoff: a wider lookback is more correct but reprocesses more each run.
How to answer it
The key decision
Filter on ingestion time, not event time. This is the whole question. An incremental run asks what is new since it last ran, and new means newly arrived, not newly occurred. Filtering on event time silently skips every row that arrived late, and nothing errors.
Example
{{ config(
materialized='incremental',
unique_key='event_id',
incremental_strategy='merge'
) }}
select *
from {{ source('raw', 'events') }}
{% if is_incremental() %}
-- ingestion time, with a lookback wider than the known lateness
where ingested_at >= (
select coalesce(max(ingested_at), '1900-01-01') from {{ this }}
) - interval '3 days'
{% endif %}Why the lookback
Even filtering on ingestion time, a run that overlaps a write in progress can miss rows sitting between the last watermark and the moment the query ran. Subtracting a window slightly wider than the known lateness covers that, and the merge strategy with a unique key makes reprocessing the same rows harmless rather than duplicating them.
That pairing is the point: an overlapping window is only safe because the write is idempotent.
Key points
- Incremental filter on ingestion time; aggregate downstream on event time.
- Always pair a lookback window with a unique key and merge, so overlap is harmless.
max(ingested_at)from the target is the natural watermark, and needs a coalesce for the first run.- Partition by event date so a late row rewrites one partition rather than the table.
- Schedule a periodic full refresh, because incremental logic drifts and back-dated corrections upstream will be missed.
FAQ
- What if there is no ingestion timestamp? Add one. Without it, correct incremental loading is not really possible.
- Why not insert_overwrite? It works well when partitioned by event date and the lookback rebuilds whole partitions. Merge suits row-level updates.
- How wide should the lookback be? Wider than the observed lateness, plus margin. Measure the actual distribution rather than guessing.
The 30-second answer
The filter is on ingestion time, not event time, and that is the whole question. An incremental run wants rows that arrived since last time, and if I filter on when the event happened, every late-arriving row is silently skipped with no error.
So the predicate is ingested_at greater than the max ingested_at already in the table, minus a lookback window a bit wider than the known lateness. That overlap is only safe because I also set a unique key and use the merge strategy, so reprocessing the same rows updates them rather than duplicating them.
Downstream I still aggregate by event time, and I would partition by event date so a late row rewrites one partition. I would also schedule an occasional full refresh, because incremental logic drifts.
In the interview
- What they are checking: the lookback, the unique key, and that you can say why max-timestamp alone is wrong.
- Common mistake: append strategy with the max-timestamp filter. Fast, and it silently drops every late event.