Hooks vs operations in dbt #dbt

README.md

Hooks vs operations in dbt

All of this information is from the official dbt documentation.

We have a SQL statement we want to execute, perhaps after all tables have been built by a dbt run command:

grant select on all tables in schema foo to alice

Using hooks

# dbt_project.yml
...
models:
  +post-hook: "grant select on all tables in schema foo to alice"
...
on-run-end: "grant select on all tables in schema foo to alice"

The post-hook will be executed after each model is built and the on-run-end hook will be executed after the last model is built.

Let's wrap it up in a macro to keep things dry:

-- macros/grant.sql
{% macro grant() %}
  grant select on all tables in schema foo to alice
{% endmacro %}

# dbt_project.yml
...
models:
  +post-hook: "{{ grant() }}"
...
on-run-end: "{{ grant() }}"

This is functionally equivalent - notice that the macro grant() simply evaluates to a string... nothing fancy going on. Hooks CAN EXECUTE sql statement strings.

Using operations

Now, instead of hooks, you want to use your macro above in a run-operation instead:

dbt run-operation grant

And then you notice that the grant isn't being executed. This is because your grant() macro simply returns a string and unlike hooks, operations CANNOT EXECUTE sql statement strings - they need to be wrapped in a special run_query macro.

Add a macro with run_query:

-- macros/grant.sql
{% macro grant() %}
  grant select on all tables in schema foo to alice
{% endmacro %}

{% macro run_grant() %}
  {% do run_query(grant()) %}
{% endmacro %}

And then run the new macro:

dbt run-operation run_grant

Here grant() evaluates to a string "grant select on all tables in schema foo to alice" and then the string is wrapped in run_query("grant select on all tables in schema foo to alice") which is then excecuted.

Happy days.