dbt Hooks

Hooks are SQL statements or dbt operations that run automatically before or after a model executes. Use them to run warehouse-specific commands that dbt does not handle natively — like granting permissions, creating indexes, running VACUUM, or logging run metadata to a table.

The Four Hook Types

Hook               When It Runs
-----------        ------------
pre-hook           Before each model runs
post-hook          After each model runs
on-run-start       Once at the start of the entire dbt run
on-run-end         Once at the end of the entire dbt run

pre-hook and post-hook

These hooks attach to individual models. Define them in the model's config block or in dbt_project.yml.

Granting SELECT After a Model Runs

-- models/marts/fct_orders.sql

{{ config(
    materialized='table',
    post_hook=[
        "GRANT SELECT ON {{ this }} TO ROLE REPORTER",
        "GRANT SELECT ON {{ this }} TO ROLE BI_TOOL"
    ]
) }}

select ...

{{ this }} refers to the table just created. dbt runs the GRANT statements immediately after the CREATE TABLE finishes.

Creating an Index After a Table is Built (PostgreSQL)

{{ config(
    materialized='table',
    post_hook="CREATE INDEX IF NOT EXISTS idx_fct_orders_customer
               ON {{ this }} (customer_id)"
) }}

select ...

Logging Model Metadata

{{ config(
    post_hook="""
        INSERT INTO audit.model_run_log
        (model_name, run_at, row_count)
        SELECT
            '{{ this.name }}',
            current_timestamp,
            count(*)
        FROM {{ this }}
    """
) }}

select ...

on-run-start and on-run-end

These hooks run once per dbt run invocation — not per model. Define them in dbt_project.yml.

Creating a Schema Before Models Run

# dbt_project.yml
on-run-start:
  - "CREATE SCHEMA IF NOT EXISTS {{ target.schema }}"
  - "CREATE SCHEMA IF NOT EXISTS {{ target.schema }}_snapshots"

Logging the Run Start Time

on-run-start:
  - "INSERT INTO audit.pipeline_log (event, started_at)
     VALUES ('dbt_run_start', current_timestamp)"

on-run-end:
  - "INSERT INTO audit.pipeline_log (event, finished_at)
     VALUES ('dbt_run_end', current_timestamp)"

Granting Access to the Entire Schema After All Models Run

on-run-end:
  - "GRANT USAGE ON SCHEMA {{ target.schema }} TO ROLE REPORTER"
  - "GRANT SELECT ON ALL TABLES IN SCHEMA {{ target.schema }} TO ROLE REPORTER"

This is more efficient than granting per-model in post-hooks when you have many models in the same schema.

Applying Hooks to Multiple Models via dbt_project.yml

# dbt_project.yml
models:
  my_project:
    marts:
      +post-hook:
        - "GRANT SELECT ON {{ this }} TO ROLE REPORTER"

This post-hook runs after every model in the marts/ folder. No need to add it to each model's config block individually.

Calling Macros from Hooks

Hooks can call dbt macros for reusable logic:

-- macros/grant_access.sql
{% macro grant_access(role='REPORTER') %}
    GRANT SELECT ON {{ this }} TO ROLE {{ role }}
{% endmacro %}

-- In a model config
{{ config(
    post_hook="{{ grant_access(role='REPORTER') }}"
) }}

Hook Execution Diagram

dbt run starts
      |
      v
[on-run-start hooks]  ← run once for the whole pipeline
      |
      v
For each model (in DAG order):
      |
      ├─ [pre-hook(s)] run
      ├─ [model SQL] runs (CREATE TABLE / VIEW)
      └─ [post-hook(s)] run
      |
      v
[on-run-end hooks]    ← run once after all models complete
      |
      v
dbt run finishes

Common Hook Use Cases

Use Case                       Hook Type    Example SQL
-----------------------        ----------   -----------
Grant permissions              post-hook    GRANT SELECT ON {{ this }} TO ROLE X
Create search index            post-hook    CREATE INDEX ON {{ this }} (col)
Create schema if missing       on-run-start CREATE SCHEMA IF NOT EXISTS ...
VACUUM/ANALYZE (Postgres)      post-hook    VACUUM ANALYZE {{ this }}
Log run metadata               on-run-end   INSERT INTO audit.log ...
Cluster table (Snowflake)      post-hook    ALTER TABLE {{ this }} CLUSTER BY (date)

Debugging Hooks

Hook SQL appears in the dbt run output and in logs/dbt.log. If a hook fails, dbt reports the error with the hook SQL that caused it. Copy the SQL and run it manually in the warehouse to debug the exact error.