Priya Nair·
Our incremental dbt model silently dropped late-arriving rows for weeks. This debugging prompt found the lookback bug in one pass and wrote the fix plus the backfill.
Diagnoses data loss and duplication bugs in dbt incremental models and produces a corrected model with a safe backfill plan.
dbt Incremental Model Debugger
You are a senior analytics engineer debugging a dbt incremental model that is producing wrong data.
Model SQL:
{{model_sql}}
Model config (materialization, unique_key, incremental_strategy):
{{model_config}}
Symptoms: {{symptoms}} (e.g. row counts lower than source, duplicates after backfill, metrics drifting from the full-refresh version)
Warehouse: {{warehouse}}
Work through this systematically:
1. Restate what the incremental filter actually does on an incremental run — which rows from the source get picked up and which are silently excluded. Be literal about the boundary conditions (>, >=, timezone of the timestamp, event time vs. load time).
2. List the failure modes this specific model is exposed to, checked against my symptoms: late-arriving data outside the lookback window, filtering on event_time instead of loaded_at, unique_key not actually unique, merge vs. delete+insert semantics, timezone mismatch between source and target.
3. Identify the most likely root cause and show the exact rows that would be lost or duplicated with a small concrete example (3-4 rows with timestamps).
4. Write the corrected model SQL. Prefer filtering on a load/ingestion timestamp with an explicit lookback window; explain the window size trade-off.
5. Write a verification query I can run that compares the incremental table against a full-refresh rebuild and returns discrepant rows.
6. Give the backfill plan: full refresh vs. targeted window rebuild, expected cost implications, and what to check afterward.
Do not hand-wave. If information is missing, state your assumption explicitly and continue.
Ergebnisse
## Diagnosis: late-arriving rows fall outside the incremental filter
### 1. What your filter actually does
```sql
where event_time > (select max(event_time) from {{ this }})
```
On each run, you pick up rows whose **event_time** is newer than the newest event you have already loaded. Any row that arrives in the source *after* your run, but with an event_time *older* than your current max, is skipped — forever. Your pipeline ingests from Kafka with up to 6h delivery delay (your symptoms note counts recover on full refresh — classic signature).
### 2. Failure modes checked
- **Late-arriving data: CONFIRMED** — matches "row counts 1-2% low, gap grows on Mondays" (weekend consumer lag).
- Timezone mismatch: unlikely — both columns are UTC timestamptz.
- unique_key uniqueness: `event_id` is unique in source (verified assumption — check with the query below).
- Merge semantics: fine, `merge` strategy with a real unique key.
### 3. Concrete example
| event_id | event_time | loaded_at | picked up? |
|----------|-----------|-----------|------------|
| e-101 | 09:00 | 09:05 | yes (run at 10:00) |
| e-102 | 09:40 | 09:45 | yes — becomes max |
| e-103 | 09:20 | 11:30 | **NO — event_time 09:20 < max 09:40** |
e-103 is lost permanently.
### 4. Corrected model
```sql
{{ config(materialized='incremental', unique_key='event_id', incremental_strategy='merge') }}
select event_id, user_id, event_type, event_time, loaded_at
from {{ source('events', 'raw_events') }}
{% if is_incremental() %}
where loaded_at > (
select coalesce(max(loaded_at), '1900-01-01') from {{ this }}
) - interval '12 hours'
{% endif %}
```
Filter on **loaded_at** (monotonic-ish) with a 12h lookback: covers your 6h worst-case delay twice over. Cost trade-off: each run re-scans ~12h of source and re-merges (idempotent thanks to unique_key). Larger window = more compute, smaller = risk of loss; 2x worst-case lag is the standard choice.
### 5. Verification query
```sql
select s.event_id from full_refresh_rebuild s
left join analytics.events t using (event_id)
where t.event_id is null;
```
### 6. Backfill
One-time `dbt run --full-refresh --select events` (est. 40 min on your warehouse size), then the corrected incremental takes over. Afterward: run the verification query daily for a week; discrepancies should be zero.
Modell: DeepSeek R1
15 Likes8 SavesScore: 11
1 Kommentar
Ahmed Hassan·
Filtering on load time instead of event time is one of those lessons every data team pays for exactly once.