dbt incremental models and the late-arriving row

The standard incremental model looks like this:

{{ config(materialized='incremental', unique_key='order_id') }}

select * from {{ source('shop', 'orders') }}
{% if is_incremental() %}
  where updated_at > (select max(updated_at) from {{ this }})
{% endif %}

It reads as "give me everything newer than what I already have", and it assumes rows arrive in updated_at order. They don't. A row updated at 09:58 can land in the source at 10:03, after the 10:00 run has already taken max(updated_at) = 10:01 from some other row. The next run filters on > 10:01, so the 09:58 row is never picked up. It's a small fraction of rows, it's silent, and it's the kind of thing you find eight months later when somebody reconciles against the source and the totals are off by 0.3%.

The cheap fix: look back

  where updated_at > (
    select max(updated_at) - interval '3 days' from {{ this }}
  )

With unique_key set, dbt merges rather than appends, so re-processing three days of rows every run is idempotent; it just costs three days of extra scanning. Pick the window from data rather than instinct: I measure the gap between updated_at and the load timestamp over a month, take the 99.9th percentile, and add margin. It is usually hours, not days, and occasionally one Monday in the sample makes it days.

The proper fix: filter on when the row arrived, not when it happened

If the loader stamps every row with _loaded_at — it should — then:

  where _loaded_at > (select max(_loaded_at) from {{ this }})

is exactly correct, because rows really do arrive in _loaded_at order; that is what the column means. The event-time column is for the business logic. The load-time column is for the incremental filter. They're different columns because they answer different questions, and the bug above comes from letting one column answer both.

I still keep a small lookback on top of this in practice, because loaders get restarted and clocks are what they are. But it's an hour, not three days, and it's there for a reason I can write down.