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

Filter on event time with a lookback window, not on the maximum timestamp alone, and give the model a unique key so late rows update rather than duplicate.

{{ config(
    materialized='incremental',
    unique_key='event_id',
    incremental_strategy='merge'
) }}

select event_id, user_id, event_time, event_type, amount
from {{ source('app', 'events') }}
{% if is_incremental() %}
  where event_time >= (
    select coalesce(max(event_time), '1900-01-01') from {{ this }}
  ) - interval '3 days'
{% endif %}

Why each piece:

• The filter on event_time picks up late events that carry an old timestamp; a filter on max(event_time) alone would skip anything older than the newest row already loaded.
• The 3-day lookback is the lateness window plus margin. Wider is more correct and reprocesses more per run; state the tradeoff.
• unique_key with a merge (or delete+insert on the partition, which is faster on big warehouses) means a row that arrives twice, or a corrected version of a row, replaces itself instead of stacking.
• If the source carries an ingestion timestamp, filtering on that instead is the cleanest: "give me everything that arrived since my last run" naturally includes late events, and the event-time lookback is then only needed for correctness of downstream aggregates.

Test it: a uniqueness test on event_id, and a check that a full refresh and the incremental result agree on a sample window.

• 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.

Related questions

• Design a data pipeline to ingest 1B events/day.
• Batch vs streaming: how do you decide for a given use case?
• A nightly pipeline fails halfway through. How do you design it so re-running is safe?
• Events can arrive up to 48 hours late. How do you handle that in a daily aggregation?
• When would you denormalize a warehouse table, and what do you give up?
• Design a data warehouse schema for an e-commerce company.

Practice this for real

• Check your resume against a Analytics Engineer job description — free, no account needed.
• Every data interview question, by role and topic
• Scored AI mock interviews — two free, no card