If you've read the incremental models guide, you know the basic pattern: filter the source to new rows, merge them in, repeat. The gap in that pattern is that "new rows" is defined by when data arrived — not necessarily when it happened. Those two things are often the same. When they're not, your model quietly produces incomplete results.
No error. No failed test. Just missing data, discovered later.
What is late-arriving data?
Late-arriving data is any record that reaches your pipeline after the time window it belongs to has already been processed.
An event that happened on Monday but didn't reach your warehouse until Wednesday is late-arriving data. Your incremental model ran on Monday night with a filter of event_time > last_run. Wednesday's run has a watermark of Wednesday. Monday's late event arrives and falls below the watermark. It never gets processed.
The event exists in the source system. It exists in the staging table. It just never made it into your marts — and nothing told you that.
Think of a transit app's live arrivals board. A bus actually passes your stop at 8:02 — that's the event. But the bus's GPS unit only reports back to the server once it clears a dead zone five minutes later — that's when the data arrives. If the app's board only shows "pings received since I last checked," and it checked at 8:04, that 8:02 bus never appears. It happened. The board just never heard about it in time. Your incremental model has the exact same blind spot, just with rows instead of buses.
Why it happens
Late arrivals have several root causes, and most production data pipelines encounter more than one of them.
Mobile apps and offline events
A user opens your app on a train with no signal and generates a session's worth of events — clicks, views, purchases. Their phone queues those events locally. Two hours later they connect to WiFi, and all of them flush to your backend at once. The event_time values are from the train. The arrival_time is from the airport.
This is expected behavior from the SDK. It's also a guarantee that if you filter by event_time > last_run, the offline session will be missed on any incremental run that happened before the user reconnected.
Your incremental model, meanwhile, has no idea anyone was ever on a train.
Webhook retries
Payment processors, subscription billing platforms, and CRMs send webhooks for every state change. When the receiving endpoint is down or responds with a non-2xx status, they retry — sometimes for hours, sometimes across days. The webhook body contains the original created_at timestamp of the event. The delivery happens later.
A failed payment on Monday, a webhook that didn't deliver until Tuesday night, and an incremental subscription model that ran Monday at midnight: the failed payment event is missing from Monday's data. Your churn calculation for Monday is understated by exactly those records.
Third-party batch exports
Many SaaS CRMs and billing systems don't have real-time APIs — they export data in daily or hourly batches. A deal closed on Tuesday at 4pm may not appear in your Salesforce export until Wednesday's 3am sync. Your Tuesday incremental run finished before that data existed in the source.
Worse: some systems export only the current state of records in each batch. Historical closes, retroactive adjustments, and backdated entries are delivered in a later batch with their original effective date intact.
ETL pipeline failures and backfills
Your Fivetran or Airbyte connector fails at 2am during a network disruption. It recovers at 6am and backfills the four-hour window it missed. The records arrive at 6am with event_time values from the 2–6am window. If your incremental model ran at 5am and filtered on ingested_at rather than event_time, the backfill is fine — it arrives after the model ran and will be picked up next time. If your model filtered on event_time, the backfill lands in a window that's already been processed.
Database replication lag
Log-based CDC replication (Debezium, DMS, Fivetran change data capture) can fall behind under load. Updates and inserts that happened at 10am can arrive in your warehouse at 11am or later, attributed to their original transaction timestamps.
Why it is dangerous
The core risk is not that your data is wrong — it's that it looks correct while being wrong. A data quality check that passes is doing its job. A count of events per day that looks plausible is not the same as a count that is complete.
The practical consequences compound over time:
- A daily subscription churn model counts 14 churned accounts. Three more arrive the next morning as late webhook retries. The snapshot for yesterday is now permanently short by three records — and whatever report referenced it is quietly incorrect.
- A conversion funnel missing 1.5% of mobile events consistently understates conversion rates. The optimization recommendations built on top of that funnel are based on wrong data for months before anyone notices.
- An MRR calculation built on incremental staging models is off by a small but consistent amount because backfilled Chargebee events keep landing outside the watermark window. The discrepancy surfaces during a quarterly reconciliation, not when it happens.
- Data quality alerts on row counts don't fire because the counts look reasonable — slightly low, but within tolerance. The late data doesn't make the number obviously wrong, just slightly wrong.
The warehouse doesn't know data is missing. It reports what it has. The model succeeded. The tests passed. Everything is fine, except for the data that isn't there.
Ask a warehouse if it's missing anything and it will cheerfully say no. It does not know what it does not know, and it is remarkably confident about that.
The timestamp problem
Before choosing a pattern, it helps to be precise about which timestamps you're dealing with. Most event-generating systems produce at least three distinct timestamps, and conflating them is the root cause of most late-data handling failures.
The correct split for incremental models:
- Use
ingested_atin your incremental filter — "process everything the pipeline received since the last run" - Use
event_timein your business logic — "count events that occurred in this calendar window"
Filtering on event_time for both looks simpler. It misses any record where the pipeline received it before your watermark, regardless of when the event happened. Filtering on ingested_at guarantees pipeline completeness — every record the connector delivered is processed — while event_time attributes it to the correct business window.
The tradeoff: filtering on ingested_at means your incremental model sees late-arriving records as "new" when the pipeline delivers them. With a merge strategy and a good unique_key, those records are inserted correctly rather than silently dropped.
Patterns for handling late-arriving data
Pattern 1: The lookback window
The simplest and most widely applicable fix. Instead of filtering to rows newer than the last run's watermark, reprocess a fixed window of recent time.
{{ config(
materialized='incremental',
unique_key='event_id',
incremental_strategy='merge'
) }}
SELECT
event_id
, user_id
, event_type
, event_time
, _fivetran_synced AS ingested_at
FROM {{ source('app', 'raw_events') }}
{% if is_incremental() %}
WHERE _fivetran_synced >= DATEADD(day, -3, (SELECT MAX(ingested_at) FROM {{ this }}))
{% endif %}
On every run, this reprocesses the last 3 days of ingested records. The merge strategy deduplicates using event_id — records already in the table are updated if they changed, genuinely new late-arriving records are inserted.
How far back to look: match your window to the SLA of your slowest source. Webhook retry windows are typically 24–72 hours. Batch CRM exports are usually 24–48 hours. CDC replication lag is usually minutes to hours. A 3-day window covers the majority of real-world cases. A 7-day window is conservative. More than that is usually an upstream pipeline problem that the lookback window is masking rather than fixing.
Pattern 2: Partition-based delete+insert
Instead of merging individual rows, delete the last N date partitions entirely and rebuild them from the source. Better suited for aggregation models where the partition is the natural unit of correctness.
{{ config(
materialized='incremental',
incremental_strategy='delete+insert',
unique_key='event_date',
incremental_predicates=[
"DBT_INTERNAL_DEST.event_date >= dateadd(day, -7, current_date)"
]
) }}
SELECT
DATE(event_time) AS event_date
, event_type
, COUNT(*) AS event_count
, COUNT(DISTINCT user_id) AS unique_users
FROM {{ source('app', 'raw_events') }}
{% if is_incremental() %}
WHERE _fivetran_synced >= DATEADD(day, -7, CURRENT_DATE)
AND DATE(event_time) >= DATEADD(day, -7, CURRENT_DATE)
{% endif %}
GROUP BY 1, 2
Every run deletes the last 7 days of aggregated results and rebuilds them from source. Late-arriving events that fall within that 7-day window are automatically incorporated. Records older than 7 days are considered final and not touched.
The incremental_predicates limits the DELETE statement to the same 7-day window, so the warehouse doesn't scan the full history on every run.
Both filters in the WHERE clause are required. The _fivetran_synced filter captures recently ingested records; the DATE(event_time) filter ensures the SELECT only produces event_date values that fall within the same window as the DELETE predicate. Without the second filter, a late-arriving record with event_time from 30 days ago would produce an aggregate row for that old partition — but the DELETE step would skip it (outside the 7-day window), leaving a duplicate partition row in the target.
Important: the partition key (event_date) must be derived from event_time, not from ingested_at. You want to correct the business date of the event, not the date it arrived. An event that happened on Monday arriving on Wednesday should land in Monday's partition — as long as Monday is still within the lookback window.
Pattern 3: The explicit watermark
For cases where you need precise control over what's been processed and what hasn't. Instead of inferring the watermark from the model's own max timestamp, track it explicitly.
{% if is_incremental() %}
{% set watermark_query %}
SELECT DATEADD(day, -3, MAX(ingested_at))
FROM {{ this }}
{% endset %}
{% set watermark = run_query(watermark_query).columns[0].values()[0] %}
WHERE _fivetran_synced >= '{{ watermark }}'
{% endif %}
This makes the lookback calculation explicit in the query rather than embedded in config. Useful when the lookback window varies by model, or when you want to document the completeness guarantee clearly in the SQL itself.
Pattern 4: microbatch (dbt 1.9+)
dbt 1.9 introduced a native microbatch incremental strategy that handles late-arriving data declaratively. You define the event time column and the batch size, and dbt manages the windowing, partitioning, and backfill logic automatically.
{{ config(
materialized='incremental',
incremental_strategy='microbatch',
event_time='event_time',
begin='2024-01-01',
batch_size='day',
lookback=3
) }}
SELECT
event_id
, user_id
, event_type
, event_time
FROM {{ source('app', 'raw_events') }}
dbt executes a separate query per batch window (day, hour, etc.) and retries failed batches automatically. The lookback: 3 setting reprocesses the last 3 days on every run, incorporating late arrivals. The begin date is required — it tells dbt where to start when building the target table for the first time. No manual is_incremental() filter needed — the framework handles the windowing.
Microbatch is the direction the dbt ecosystem is moving, and it's worth adopting if you're on 1.9 or later. The caveat: event_time must be reliable end-to-end. If the source sends events with incorrect timestamps, microbatch will partition them into the wrong windows with no way to override. The patterns above give you more control when the timestamp can't be trusted.
Monitoring for late arrivals
The hardest part of late data is that it's invisible until something downstream breaks. A few monitoring patterns help catch it early.
dbt source freshness
dbt's built-in source freshness checks verify that new data has been ingested recently. Configure it on the _fivetran_synced or equivalent column in your sources YAML:
sources:
- name: app
tables:
- name: raw_events
loaded_at_field: _fivetran_synced
freshness:
warn_after: {count: 1, period: hour}
error_after: {count: 6, period: hour}
Run with dbt source freshness in your pipeline. This catches the case where ingestion has stalled entirely — the most severe form of late data. It doesn't catch the subtler case of events with old event_time values arriving in a fresh batch.
Late arrival detection test
A custom singular test that checks whether any records are still arriving with timestamps more than N days in the past. Add this as a test in your dbt project:
-- tests/assert_no_events_older_than_5_days.sql
SELECT
DATE(event_time) AS event_date
, COUNT(*) AS late_records
FROM {{ ref('stg_events') }}
WHERE
ingested_at >= CURRENT_TIMESTAMP - INTERVAL '1 hour'
AND event_time < CURRENT_DATE - INTERVAL '5 days'
HAVING COUNT(*) > 0
This returns rows only when records with event timestamps older than 5 days are still being ingested in the last hour. If it fires, you have a source with a late-arrival window wider than your current lookback buffer. Either extend the lookback or escalate to the source team.
Row count reconciliation
For sources with a known total (subscription billing systems, CRMs with accessible record counts), periodically compare your warehouse total against the source. Gaps that grow consistently over time indicate systematic late arrivals that the lookback window is not catching.
This is less automated than a dbt test but more reliable for catching structural completeness gaps. A weekly reconciliation query that compares warehouse counts to the source's own reporting API is worth the 20 minutes it takes to set up.
Choosing the right pattern
The patterns above are not mutually exclusive, and the right choice depends on the grain of the model, the reliability of the source timestamps, and the lateness SLA your stakeholders are willing to accept.
The most important step is making the lateness SLA explicit. A model that reprocesses 3 days should say so in its schema.yml description. Downstream consumers need to know the completeness guarantee — not because they will read it (they won't), but because it forces you to have the conversation about whether the business can tolerate data that may be up to 3 days incomplete before being corrected.
models:
- name: fct_daily_events
description: >
Daily event counts by type. Rebuilt for the last 7 days on every run
to incorporate late-arriving mobile events. Data older than 7 days is
considered final and will not be corrected by subsequent pipeline runs.
Source late-arrival SLA: 72 hours.
columns:
- name: event_date
description: Calendar date of the event (event_time, not ingested_at).
tests:
- not_null
- unique
That description is more useful than most schema.yml descriptions in production. It tells you what the model does, what it doesn't do, and where the boundary of "correct" sits.
When the window isn't enough
If your source has a late-arrival window wider than you can practically accommodate with a lookback buffer, the problem is upstream of dbt. A 30-day late-arrival window means either the source system has a fundamental data delivery problem, or the data team is being asked to correct history that the business process generates continuously.
In those cases, the answer is usually one of:
- Negotiate an SLA with the source team to reduce the late window (with monitoring to enforce it)
- Move to a full rebuild on a schedule — nightly for the last 30 days, weekly for history — and accept the cost
- Separate the "complete but slow" mart from a "fast but potentially incomplete" mart, and be explicit with stakeholders about which view they're using
The worst outcome is a model with no documented lateness guarantee, a 3-day lookback that doesn't match the actual source SLA, and stakeholders who don't know they're looking at incomplete data. Make the tradeoff visible and let the business decide what's acceptable. That is a more important conversation than any of the implementation patterns above.
Takeaways
- Late-arriving data doesn't fail loudly — it produces results that look correct and aren't. Silent incorrectness is harder to detect than a failing run.
- Store both
event_timeandingested_at— they answer different questions. Useingested_atfor incremental filtering; useevent_timefor business logic. - Filtering on
ingested_atis more reliable for pipeline completeness — every record the connector delivered gets processed, regardless of when the underlying event occurred. - A 3-day lookback window with merge strategy handles most real-world cases — match the window width to the SLA of your slowest source, not to an arbitrary number.
delete+insertby date partition is cleaner for aggregations — rebuild the affected date ranges entirely rather than merging individual rows into an aggregate.- dbt 1.9 microbatch automates the windowing — worth adopting on recent versions when
event_timeis reliable end-to-end. - Document the lateness SLA in
schema.yml— downstream consumers need to know the completeness guarantee. Making it explicit forces the business conversation about what's acceptable. - A lookback window masks a source problem; it doesn't fix it — if the window needs to be 30 days, escalate to the source team before setting that as the new normal.
Every data platform reaches a point where the source SLAs, pipeline architecture, and business expectations need to be aligned. Late-arriving data is often where that conversation finally surfaces — usually because someone noticed the numbers don't add up. Better to design for it explicitly than to discover it under pressure.