Skip to main content

Handling Late-Arriving Data (Lookback Windows)

The biggest challenge with Incremental Models is Late-Arriving Data.

Imagine your standard incremental logic:

{% if is_incremental() %}
WHERE created_at >= (SELECT MAX(created_at) FROM {{ this }})
{% endif %}

Assume your dbt job runs at 2:00 AM.

  1. It looks at the target table and sees the MAX(created_at) is 2023-10-15 01:59:00.
  2. It fetches all new rows from the source created after that timestamp.
  3. It merges them. All is well.

But what if an IoT sensor on a delivery truck in rural Karnataka loses internet connection for 6 hours? When the truck regains connection at 4:00 AM, it uploads its cached data with a created_at timestamp of 2023-10-14 22:00:00.

The next time your dbt job runs, it asks: "Give me everything greater than 2023-10-15 01:59:00". Because the late-arriving data has an older timestamp, it is completely ignored by the incremental query! You have permanently lost data.

Topics Covered​

  • The Lookback Window Strategy
  • Implementing Lookback Windows in dbt
  • Dealing with Updated Rows vs Created Rows

1. The Lookback Window Strategy​

To solve this, we must change our assumption. We cannot assume that data arrives in perfect chronological order.

Instead of asking for data greater than the exact maximum timestamp, we implement a Lookback Window. We ask for data greater than the maximum timestamp minus a few days.

For example: "Give me everything created after the MAX(timestamp) minus 3 days."

If the delivery truck uploads data that is 6 hours old, our 3-day lookback window will safely catch it.

2. Hands-On: Implementing the Lookback Window​

Let's rewrite our standard incremental logic to include a 3-day lookback window using Snowflake's DATEADD function.

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

WITH raw_sensor_data AS (
SELECT * FROM {{ ref('stg_truck_sensors') }}
)

SELECT * FROM raw_sensor_data

{% if is_incremental() %}

-- The Lookback Window Implementation
WHERE created_at >= (
SELECT DATEADD('day', -3, MAX(created_at))
FROM {{ this }}
)

{% endif %}

Why this works:​

  1. The query fetches the last 3 days of data from the source, including the late-arriving truck data.
  2. dbt takes these 3 days of data and executes a MERGE.
  3. For data that already exists in the target table, the MERGE simply overwrites it (no harm done, since the data hasn't changed).
  4. For the newly arrived truck data, the MERGE successfully inserts it!

Note: This relies on using a unique_key and the merge strategy to prevent duplicating the data that was already processed.

3. Dealing with Updates vs Inserts​

If your data source contains rows that get updated (e.g., an Order Status changing from 'Pending' to 'Delivered'), you should base your incremental logic on an updated_at timestamp, not a created_at timestamp.

If an order created in January is finally delivered in October, querying WHERE created_at >= MAX(created_at) will completely miss the update.

Best Practice: Always use an updated_at or last_modified_timestamp column for your incremental filter, and always include a lookback window to catch out-of-order updates.

{% if is_incremental() %}
WHERE updated_at >= (
SELECT DATEADD('hour', -12, MAX(updated_at))
FROM {{ this }}
)
{% endif %}

Summary​

  • Late-Arriving Data occurs when data from the past arrives after you have already processed newer data.
  • Standard incremental logic (>= MAX(timestamp)) will silently drop late-arriving data.
  • Use a Lookback Window (e.g., DATEADD('day', -3, MAX(timestamp))) to overlap your processing window and catch delayed data.
  • Always combine a Lookback Window with a unique_key and a merge strategy to prevent duplicating the overlapping records.
  • Prefer updated_at columns over created_at columns for your incremental filters.