Why does a dbt incremental model filtered on event_at > (select max(event_at) from {{ this }}) miss late-arriving rows?

## The failure This is the single most common defect in incremental dbt models, and it is invisible: the run is green, the row counts grow, and a slice of data is missing forever. The predicate at issue looks reasonable: ```sql {% if is_incremental() %} where event_at > (select max(event_at) from {{ this }}) {% endif %} ``` It says: only take rows whose business event time is newer than the newest one I already have. That works perfectly when arrival order matches event order. It fails whenever it does not — which is the normal state of real pipelines. Concretely: a mobile client buffers events offline and uploads them a day later. A partner sends a corrected transaction file on Thursday for Tuesday's trades. An upstream extraction job runs late and lands Monday's batch on Wednesday. In each case the target already contains rows with a higher `event_at`, so the late rows sit below the watermark and are never selected — not on this run, and not on any future one, because the watermark only ever moves forward. ## Why nothing warns you dbt has no expectation of what the model should contain. `is_incremental()` only decides whether to apply a filter; the predicate is yours, and dbt executes it faithfully. There is no reconciliation between the model and its source unless you write one. Row counts still rise every day, dashboards still refresh, and the gap is only discovered when someone reconciles against the source system — often months later, and usually by the finance team. ## Fix one: filter on ingestion time The cleanest change is to stop conflating "when it happened" with "when we got it": ```sql where loaded_at > (select max(loaded_at) from {{ this }}) ``` `loaded_at` (or `_ingested_at`, or the loader's own metadata column) increases monotonically with arrival, so a late row still has a fresh ingestion timestamp and is picked up on the run after it lands. Its `event_at` can be arbitrarily old and it does not matter. This requires the column to exist and to be assigned at load time by the loader, not by the source. It is one of the strongest reasons to ensure your ingestion layer stamps arrival metadata on every row. ## Fix two: a lookback window When only business time is available, widen the filter to reprocess a trailing window: ```sql where event_at >= ( select dateadd(day, -3, max(event_at)) from {{ this }} ) ``` Now anything up to three days late is caught. But this re-emits rows already in the table, so it is only safe with a strategy that overwrites: `unique_key` with `merge`, or `insert_overwrite` replacing whole days. Pair a lookback with `append` and you have traded silent data loss for silent duplication, which is a lateral move. Sizing the window is an empirical question, not a guess: measure the observed distribution of `loaded_at - event_at` in your source and pick a window that covers the tail you care about. State the residual risk out loud — anything later than the window is still lost. ## Fix three: periodic full refresh A scheduled `dbt run --full-refresh` on the model rebuilds it from all of history and corrects any accumulated drift, whatever its cause. This is a safety net rather than a design, and it only works while a full rebuild remains affordable — which is precisely the condition that made the model incremental in the first place. Still, for mid-sized tables a weekly refresh is cheap insurance. ## Newer machinery dbt 1.9 introduced a `microbatch` incremental strategy in which you declare an `event_time` column, a `batch_size` and a `lookback`, and dbt splits the run into time-bounded batches, reprocessing the lookback batches on each run. It formalises the lookback pattern rather than leaving it as hand-written Jinja, which reduces the chance of getting the predicate subtly wrong. The underlying trade-off is unchanged: you still choose how far back to reprocess, and anything outside the window is still missed. ## Detection, not just correction Because the failure is silent, the durable fix is a test, not just a better predicate. Useful checks: - A reconciliation test comparing counts (or sums of a key measure) between the model and its source over a trailing window, failing on divergence beyond a tolerance. - Monitoring the observed lateness distribution so you notice when a source's tail lengthens past your lookback window. - A `not_null` and freshness discipline on the arrival timestamp column the filter depends on, since a null `loaded_at` would quietly exclude rows from the comparison too. A related trap in the same family: `>` versus `>=` on the boundary. Using `>` on a timestamp with second granularity can drop rows that share the exact maximum timestamp but had not yet been committed when the previous run read it. Filtering on ingestion time with a small overlap plus a `unique_key` merge sidesteps both problems at once.