pre-hook & post-hook
- Models
- Seeds
- Snapshots
models:
<resource-path>:
+pre-hook: <sql-statement> | [<sql-statement>]
+post-hook: <sql-statement> | [<sql-statement>]
{{ config(
pre_hook="<sql-statement>" | ["<sql-statement>"],
post_hook="<sql-statement>" | ["<sql-statement>"],
) }}
select ...
seeds:
<resource-path>:
+pre-hook: <sql-statement> | [<sql-statement>]
+post-hook: <sql-statement> | [<sql-statement>]
snapshots:
<resource-path>:
+pre-hook: <sql-statement> | [<sql-statement>]
+post-hook: <sql-statement> | [<sql-statement>]
{% snapshot snapshot_name %}
{{ config(
pre_hook="<sql-statement>" | ["<sql-statement>"],
post_hook="<sql-statement>" | ["<sql-statement>"],
) }}
select ...
{% end_snapshot %}
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.
Examplesβ
Grant privileges on a modelβ
models:
+post-hook: "grant select on {{ this }} to group reporter"
Grant multiple privileges on a modelβ
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β
model:
+post-hook: "{{ grant_select(this) }}"
Grant privileges on a directory of modelsβ
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:
- Hooks from dependent packages will be run before hooks in the active package.
- Hooks defined within the model itself will be run before hooks defined in
dbt_project.yml
. - 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 apost-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β
{{
config(
pre_hook=before_begin("<sql-statement>"),
post_hook=after_commit("<sql-statement>")
)
}}
select ...
Config block: use a dictionaryβ
{{
config(
pre_hook={
"sql": "<sql-statement>",
"transaction": False
},
post_hook={
"sql": "<sql-statement>",
"transaction": False
}
)
}}
select ...
dbt_project.yml
: Use a dictionaryβ
models:
+pre-hook:
sql: "<sql-statement>"
transaction: false
+post-hook:
sql: "<sql-statement>"
transaction: false