Handling Late Arriving Data with Incremental Lookback

Implement window-based lookback logic in incremental models to capture delayed records that arrive after the initial processing window.

In this lesson, you will learn how to ensure your incremental dbt models don't "miss" records that arrive in your source system after your initial processing window has already closed.

The Problem: The "Cutoff" Trap

When you build an incremental model, you typically filter your source data using is_incremental() to only process rows where the event_timestamp is greater than the maximum timestamp already in your destination table.

However, real-world data is rarely perfect. Imagine a batch of sensor data arrives at 9:00 AM. Your pipeline runs and processes everything up to that point. Suddenly, at 9:05 AM, a late record appears with a timestamp of 8:55 AM. If your filter is strictly event_timestamp > (select max(event_timestamp) from this), that 8:55 AM record will be ignored forever.

The Solution: The Lookback Window

To fix this, we implement a lookback window. Instead of looking only at data since the last run, we look back a fixed duration (e.g., 3 days or 24 hours) to re-process and overwrite potentially stale data.

By extending our filter to event_timestamp >= (select max(event_timestamp) - interval '3 days' from this), we capture those tardy arrivals. Because dbt handles the underlying merge logic using a unique_key, these re-processed records simply overwrite their previous placeholders, keeping your dataset accurate without duplicating rows.

Implementation Example

Here is how that looks inside your dbt model:

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

select *
from {{ source('raw', 'events') }}
{% if is_incremental() %}
  -- Look back 3 days to catch late-arriving records
  where event_timestamp >= (select dateadd('day', -3, max(event_timestamp)) from {{ this }})
{% endif %}

In this pattern, if an event was missed, it will be picked up the next time the model runs, and the unique_key ensures that the database cleans up the old version of the record automatically. This simple shift provides a safety net against the unpredictable nature of distributed data sources.

Key Takeaway: By subtracting a buffer interval from your incremental filter, you create a "lookback" period that re-scans recent history to catch late-arriving data while maintaining efficiency.