Skip to main content

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