dbt post-hook failed and rolled back the whole model
Fixes dbt post-hook failures that roll back the model build by making the hook SQL idempotent and testing it standalone before rerunning. Use when a model build fails because its post-hook failed. Not for pre-hook failures, which abort before the model builds.
TL;DR
Post-hooks run inside the same transaction as the model build, so a failing hook rolls back the model too. Read the hook SQL, run it by hand against the warehouse to find the defect, make it defensive (idempotent, tolerant of reruns), and rebuild the model.
Error
dbt post-hook failed and rolled back the whole modelSteps
- Find the post-hook definition: the model's config block or the project YAML
+post-hook. Expected: you have the exact hook SQL. - Run the hook SQL manually in the warehouse against the built table. Expected: it fails the same way, proving the hook (not the model) is broken.
- Make the hook idempotent: guard drops, use create-or-replace patterns, and handle the already-exists case. Expected: the hook succeeds on repeat runs.
- Consider whether the hook belongs in
on-run-endinstead: work that is not per-model (like grants on many tables) survives individual model failures better there. Expected: a deliberate placement decision. - Rerun
dbt run --select [MODEL NAME]. Expected: model and hook both succeed.
When to use
dbt runfails on a model and the error is in its post-hook.- The model SQL itself runs fine standalone.
When not to use
- The failure is in a pre-hook (the model never built; different phase).
- The hook is fine but the model SQL is broken (fix the model).
Tool compatibility
- dbt Core 1.0 and later, all adapters. Transaction behavior around hooks is adapter-specific.
Variant phrasings
Post-hook grant failed, model rolled back
The classic case: a grant on a table that the hook assumes exists with a certain name.
on-run-end hook failed after models succeeded
Same fix, but the blast radius is the whole run rather than one model.
Why it happens
dbt wraps the model build and its hooks in one transaction on adapters that support it. That atomicity is usually what you want, but it means hook bugs take down good models.
Edge cases
- Hooks referencing
{{ this }}resolve to the model being built; a typo there fails every run. - A hook that works on tables may fail on views or incremental merges; test against each materialization you use.
- Logging hooks that insert into an audit table need that table to exist before the first run.
Provenance
Resolved from the public thread: https://vectle.com/posts/pstfwjY3cbzmdfh62iJArUUw