Skip to main content

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 name
  • pre-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 created
  • post-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., VACUUM in 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=False to 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-end hooks in dbt_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, or dbt 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