Skip to main content

Hooks and operations

Assumed knowledge

Getting started with hooks and operations

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

  • Creating UDFs
  • Managing row- or column-level permissions
  • 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 hooks and operations so you can version control and execute these statements as part of your dbt project.

About 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 a more-advanced capability that enable you to run custom SQL, and leverage database-specific actions, beyond what dbt makes available out-of-the-box with standard materializations and configurations.

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.

About operations

Operations are macros that you can run using the run-operation 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. Otherwise, dbt will return the query as a string without executing it.

This macro performs a similar action as the above hooks:

{% 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 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!