Priya Nair·
Ran this against our 40-model dbt project and it flagged three incremental models silently dropping late-arriving rows. Saved us a very ugly backfill.
Audits a dbt incremental model for silent data-loss patterns: late-arriving data, lookback windows, merge key mistakes, and full-refresh divergence.
dbt Incremental Model Auditor
You are a senior analytics engineer reviewing a dbt incremental model for correctness. I will paste the model SQL and its config. Your job is to find silent data-loss and divergence bugs, not style issues.
Model SQL:
{{model_sql}}
Unique key(s): {{unique_key}}
How data arrives upstream (batch cadence, typical lateness, whether rows can be updated after insert): {{loading_pattern}}
Audit the model against this checklist and report ONLY real findings:
1. Late-arriving data: does the incremental filter (`is_incremental()` block) use a hard cutoff like `> (select max(updated_at) from {{ this }})` with no lookback window? If upstream rows can arrive late or be updated, estimate what gets dropped and propose a lookback window sized to the stated lateness.
2. Merge semantics: does the unique_key actually guarantee uniqueness upstream? Flag composite-key columns that can be NULL (NULL never equals NULL in a merge).
3. Full-refresh divergence: would `--full-refresh` produce different rows than the incremental path? Point to the exact predicate that differs.
4. Timezone and boundary bugs: date truncation, `>=` vs `>` off-by-one on the watermark, comparing timestamps of different zones.
5. Deletes: if upstream hard-deletes rows, does this model ever remove them?
For every finding give: severity (data-loss / divergence / perf), the exact line, a one-sentence explanation of the failure scenario, and the corrected SQL snippet. Finish with a 3-line summary I can paste into the PR. If the model is clean, say so explicitly — do not invent findings.
Ergebnisse
## Audit: `fct_order_events` (incremental)
**Verdict: 2 data-loss findings, 1 divergence finding. Not safe to merge as-is.**
---
### 1. DATA-LOSS — hard watermark with zero lookback (line 14)
```sql
where event_ts > (select max(event_ts) from {{ this }})
```
You stated upstream events arrive up to 6 hours late from the mobile clients. Any event whose `event_ts` is older than the current max at run time is dropped forever. With hourly runs and 6h lateness, that is every late mobile event — I would estimate 1–3% of daily volume based on the lateness you described.
**Fix:**
```sql
where event_ts > (
select dateadd('hour', -8, max(event_ts)) from {{ this }}
)
```
8h lookback = stated 6h lateness + 2h safety margin. Requires `unique_key` merge to stay idempotent — which brings us to finding 2.
---
### 2. DATA-LOSS — nullable column in composite unique_key (config, line 3)
`unique_key = ['event_id', 'variant_id']` — `variant_id` is nullable in `stg_events` (confirmed in its schema.yml). In a merge, `NULL = NULL` is never true, so late re-sent events with NULL `variant_id` insert duplicates instead of updating, and the lookback window from fix 1 would multiply them every run.
**Fix:** `coalesce(variant_id, '-1') as variant_id_key` in the model and use that in `unique_key`, or collapse to `event_id` alone if it is truly unique upstream.
---
### 3. DIVERGENCE — incremental-only status filter (line 22)
`and status != 'draft'` lives inside the `is_incremental()` block only. A `--full-refresh` keeps drafts; the nightly incremental drops them. The table's contents depend on which path last ran.
**Fix:** move the predicate outside the conditional block so both paths agree.
---
**PR summary:** Adds 8h lookback for late mobile events, de-NULLs the merge key to stop duplicate inserts, and moves the draft filter out of the incremental block so full-refresh and incremental produce identical results.
Modell: Claude Sonnet 4.6
31 Likes19 SavesScore: 24
2 Kommentare
Luca Brunner·
The nullable-column-in-merge-key catch is so good. Exact same bug cost us a weekend in May — merge just quietly inserts duplicates and nothing errors.
Marco Rossi·
Stealing the 'do not invent findings' line for my infra review prompts. Half the battle is stopping the model from padding the report.