01. Pre Hooks Post Hooks
What are Pre Hooks and Post Hooks in dbt?
Hooks are SQL statements or macros that run before or after a dbt model, snapshot, or seed executes.
They allow you to perform additional operations automatically, like logging, auditing, or temporary setup tasks.
- Pre-hooks → Run before a model or snapshot runs
- Post-hooks → Run after a model or snapshot runs
Why Use Hooks?
Automate repetitive SQL tasks
Maintain data quality (e.g., validate tables)
Add auditing or logging for model runs
Manage permissions, grants, or temporary tables
Pre-hook Example
Scenario: Before loading a table, you want to log the start timestamp.
-- dbt_project.yml or model config
models:
my_project:
pre-hook:
- "insert into audit_log (model_name, run_type, run_time) values ('{{ this.name }}', 'pre', current_timestamp);"
Explanation:
{{ this.name }}→ Refers to the current model namepre-hook→ Executes before the model runs
Post-hook Example
Scenario: After loading a table, you want to grant select access to a role.
-- dbt_project.yml or model config
models:
my_project:
post-hook:
- "grant select on {{ this }} to role analytics_team;"
Explanation:
{{ this }}→ Refers to the fully qualified table that was createdpost-hook→ Executes after the model runs
Using Hooks in Model Config
In addition to project-level configuration in dbt_project.yml, you can define hooks directly within a regular model file inside the {{ config(...) }} block.
Depending on your needs, you can define hooks as a single SQL statement (using a string) or multiple SQL statements (using a list of strings).
1. Single Hook Syntax (String)
Best when you only need to run a single command (e.g., granting select permissions):
{{ config(
materialized='table',
post_hook="grant select on {{ this }} to role reporting_user;"
) }}
select *
from {{ source('raw', 'customers') }}
2. Multiple Hooks Syntax (List of Strings)
Best when you need to execute multiple statements in sequence (e.g., executing a staging setup or performing logging steps):
{{ config(
materialized='incremental',
pre_hook=[
"insert into audit_log (model_name, event, event_time) values ('{{ this.name }}', 'run_started', current_timestamp);",
"analyze table {{ this }}"
],
post_hook=[
"grant select on {{ this }} to role report_reader;",
"insert into audit_log (model_name, event, event_time) values ('{{ this.name }}', 'run_completed', current_timestamp);"
]
) }}
select *
from {{ source('raw', 'customers') }}
️ Limitations of dbt Hooks
While pre-hooks and post-hooks are powerful, they have several limitations and gotchas that you must keep in mind:
1. Transaction Wrapping & Commands That Block Transactions
By default, dbt wraps the execution of a model and its associated hooks inside a single database transaction.
- The Issue: Some SQL commands cannot be executed inside an open transaction block (e.g.,
VACUUMin Postgres/Redshift, or certain DDL commands in other databases). - The Result: If you attempt to execute one of these commands inside a hook, the database will throw a transaction error.
- The Solution: In your hook configuration, you can specify
transaction=Falseto force the hook to run outside of the wrapped model transaction:{{ config(
post_hook={
"sql": "vacuum {{ this }}",
"transaction": false
}
) }}
2. Sequential Execution & Performance Overhead
Hooks execute sequentially on the warehouse, meaning each SQL statement in a pre-hook or post-hook must finish executing before the next step begins.
- The Issue: Heavy queries or slow operations (e.g., logging to a busy audit table, running heavy analytical scans) inside hooks will block the compilation/execution pipeline and extend model runtimes.
- Best Practice: Keep hooks lightweight. Do not execute heavy transformations or long-running computations in hooks.
3. Failure Behavior
Post-hooks will not execute if the main model query fails or if a pre-hook fails.
- The Issue: If you rely on a post-hook to clean up a temporary table or log the end of a execution run, that cleanup/log will be skipped if the model run errors.
- Best Practice: Use hooks for optimization and auditing rather than critical lifecycle steps that must run regardless of failure. For runs where execution cleanup is mandatory, consider dbt's global
on-run-endhooks indbt_project.yml.
4. Limited Execution Scope
Model hooks only execute when the model is actively run (e.g., via dbt run or dbt build).
- The Issue: Hooks do not run during
dbt compile,dbt test,dbt seed, ordbt docs generate. If you have schema permission hooks, they will not be re-applied when running tests.
5. Precedence & Ordering Rules
If you define hooks in both dbt_project.yml (project-level) and inside the model SQL file (model-level), they execute in a strict hierarchical order that you cannot customize:
- Pre-hooks execution order: Project-level pre-hooks run first $\rightarrow$ Model-level pre-hooks run second.
- Post-hooks execution order: Model-level post-hooks run first $\rightarrow$ Project-level post-hooks run second.
Key Best Practices
Keep hooks simple and fast – don’t add heavy transformations
Use hooks for auditing, grants, or temporary table management
Use {{ this }} and {{ this.name }} for dynamic references
Monitor hook execution in dbt logs