pre-hook & post-hook

dbt_project.yml


models:
  <resource-path>:
    +pre-hook: <sql-statement> | [<sql-statement>]
    +post-hook: <sql-statement> | [<sql-statement>]
models/<model_name>.sql


{{ config(
    pre_hook="<sql-statement>" | ["<sql-statement>"],
    post_hook="<sql-statement>" | ["<sql-statement>"],
) }}


select ...

Definition

A SQL statement (or list of SQL statements) to be run before or after a model, seed or snapshot is built.

Pre- and post-hooks can also call macros that return SQL statements.

▶Changelog

Examples

Grant privileges on a model

dbt_project.yml


models:
  +post-hook: "grant select on {{ this }} to group reporter"

Grant multiple privileges on a model

dbt_project.yml


models:
  +post-hook:
    - "grant select on {{ this }} to group reporter"
    - "grant select on {{ this }} to group transformer"

Call a macro to grant privileges on a model

dbt_project.yml


model:
  +post-hook: "{{ grant_select(this) }}"

Grant privileges on a directory of models

dbt_project.yml


model:
  jaffle_shop: # this is the project name
    marts:
      marketing:
        # this will be applied to all models in marts/marketing/
        +post-hook: "{{ grant_select(this) }}"

Additional examples

We've compiled some more in-depth examples here.

Usage notes

Hooks are cumulative

If you define hooks in both your dbt_project.yml and in the config block of a model, both sets of hooks will be applied to your model.

Execution ordering

If multiple instances of any hooks are defined, dbt will run each hook using the following ordering:

  1. Hooks from dependent packages will be run before hooks in the active package.
  2. Hooks defined within the model itself will be run before hooks defined in dbt_project.yml.
  3. Hooks within a given context will be run in the order in which they are defined.

Transaction behavior

If you're using an adapter that makes use of transactions (namely Postgres or Redshift), it's worth noting that by default hooks are executed inside of the same transaction as your model being created.

There may be occasions where you need to run these hooks outside of a transaction, for example:

  • You want to run a VACUUM in a post-hook, however this cannot be executed within a transaction (Redshift docs)
  • You want to insert a record into an audit table at the start of a run, and do not want that statement rolled back if the model creation fails.

To achieve this, you can use one of the following syntaxes:

Config block: use the before_begin and after_commit helper macros

models/<modelname>.sql
{{
  config(
    pre_hook=before_begin("<sql-statement>"),
    post_hook=after_commit("<sql-statement>")
  )
}}


select ...

Config block: use a dictionary

models/<modelname>.sql
{{
  config(
    pre_hook={
      "sql": "<sql-statement>",
      "transaction": False
    },
    post_hook={
      "sql": "<sql-statement>",
      "transaction": False
    }
  )
}}


select ...

dbt_project.yml: Use a dictionary

dbt_project.yml


models:
  +pre-hook:
    sql: "<sql-statement>"
    transaction: false
  +post-hook:
    sql: "<sql-statement>"
    transaction: false
Edit this page