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:

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

0
Edit this page