40,000 Real Orders, Zero Errors, and a Missing Fact Table Rows
Your fct_orders incremental model has run hourly for months:
{{ config(materialized='incremental', unique_key='order_id',
incremental_strategy='merge') }}
select * from {{ ref('stg_orders') }}
{% if is_incremental() %}
where updated_at > (select max(updated_at) from {{ this }})
{% endif %}
No lookback window. A vendor outage today causes a batch reconciliation
job to insert 40,000 real orders into the raw source table — all of
them carrying an original updated_at timestamp from 9 days ago.
dbt run finishes with no errors. What happens to those 40,000 rows?
They're silently and permanently skipped by every future run — not because they're missing from the source, but because they're "too old" relative to the target's current watermark.
The filter compares each row's updated_at to max(updated_at)
already sitting in fct_orders — a watermark that, after months of
hourly runs, sits at roughly "now," not 9 days ago. A row timestamped
9 days in the past will never be greater than that watermark, no
matter how many times the model reruns, even though it is brand new
to the source table today. "New to the source" and "newer than the
target's watermark" are different conditions, and this filter only
ever checks the second one. Nothing errors: the query runs, returns
zero new rows for that batch, and moves on — which is exactly why this
class of bug typically surfaces days later, via a downstream row-count
mismatch, not via any alert.
Here's the trap within the trap: adding a lookback window today (say,
- interval '3 days') fixes the pipeline — future backfills up to 3
days late will now be caught. It does not retroactively recover
these 40,000 rows, because incremental runs only ever look forward
from the current watermark; a lookback window changes how far back
new runs check, it doesn't replay history. The rows already missed
need a one-time dbt run --full-refresh (or a narrowly scoped backfill
targeting that historical window) — the lookback window alone leaves
them missing forever.
Share this question