Hooks & Operations

Related documentation

Assumed knowledge

Getting started

Effective database administration sometimes requires additional SQL statements to be run, for example:

  • Granting privileges on an table / view
  • Creating UDFs
  • Vacuuming tables on Redshift
  • Creating partitions in Redshift Spectrum external tables
  • Resuming/pausing/resizing warehouses in Snowflake
  • Refreshing a pipe in Snowflake
  • Create a share on Snowflake
  • Cloning a database on Snowflake

dbt provides two different interfaces for you to version control and execute these statements as part of your dbt project — hooks and operations.

Hooks

Hooks are snippets of SQL that are executed at different times:

  • pre-hook: executed before a model, seed or snapshot is built.
  • post-hook: executed after a model, seed or snapshot is built.
  • on-run-start: executed at the start of dbt run, dbt seed or dbt snapshot
  • on-run-end: executed at the end of dbt run, dbt seed or dbt snapshot

Hooks are defined in your dbt_project.yml file. Pre- and post-hooks can also be defined in a config block.

Here's a minimal example of using hooks to grant privileges. You can find more information in the reference sections for on-run-start and on-run-end hooks and pre-hooks and post-hooks.

dbt_project.yml
on-run-end:
  - "grant usage on {{ target.schema }} to role reporter"


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

You can also apply the post-hook to individual models using a config block:

models/<model_name>.sql
{{ config(
    post_hook=[
      "grant select on {{ this }} to role reporter"
    ]
) }}


select ...
Calling a macro in a hook

You can also use a macro to bundle up hook logic. Check out some of the examples in the reference sections for on-run-start and on-run-end hooks and pre- and post-hooks,

Operations

Operations are macros that you can run using the run-operation command command. As such, operations aren't actually a separate resource in your dbt project — they are just a convenient way to invoke a macro without needing to run a model.

Explicitly execute the SQL in an operation

Unlike hooks, you need to explicitly execute a query within a macro, by using either a statement block or a helper macro like the run_query macro macro. Otherwise, dbt will return the query as a string without executing it.

This macro performs a similar action as the above hooks:

macros/grant_select.sql
{% macro grant_select(role) %}
{% set sql %}
    grant usage on schema {{ target.schema }} to role {{ role }};
    grant select on all tables in schema {{ target.schema }} to role {{ role }};
    grant select on all views in schema {{ target.schema }} to role {{ role }};
{% endset %}


{% do run_query(sql) %}
{% do log("Privileges granted", info=True) %}
{% endmacro %}

To invoke this macro as an operation, execute dbt run-operation grant_select --args '{role: reporter}'.

$ dbt run-operation grant_select --args '{role: reporter}'
Running with dbt=0.16.1
Privileges granted

Full usage docs can for the run-operation command can be found here.

Additional examples

These examples from the community highlight some of the use-cases for hooks and operations!

Edit this page