dbt Hooks #

Hooks are snippets of SQL that get can be executed at different points of builds ofr models, seeds, or snapshots.

pre-hook - runs before a build
post-hook - runs after a build
on-run-start - runs at the start of dbt run, dbt seed, or dbt snapshot
on-run-end - runs at the end

Implementation #

Hooks are defined in dbt_project.yml

Options:

Write the hook as a SQL within dbt_project.yml
Include the hook as a separate macro called within dbt_project.yml

SQL #

The following would run for every run:


on-run-start: <sql statement>
on-run-end:  <sql statement>

If we want to restrict it to only models:

models:
  on-run-start: <sql statement>
  on-run-end:  <sql statement>

Multiple lines condensed onto one line can be split with ;.

Macros #

Start by defining a macro in macros, as <macro name>.sql. Within this file, define the macro logic. It can be dynamic with some Jinja.

For example:

{% macro <macro name>('<argument>') -%}

<sql logic>;

<can include <argument> defined above here>;

{%- endmacro %}

^ The above macro example can be invoked from within dbt_project.yml (Note: ignore the backslashes below, just had to comment things out)

on-run-start: "\{\{ <macro_name>('<variable for start>') }}"
on-run-end:  "\{\{ <macro_name>('<variable for end>') }}"

models:
  on-run-start: "\{\{ <macro_name>('<variable for start>') }}"
  on-run-end:  "\{\{<macro_name>('<variable for end>') }}"

Inspection #

We can see the output of the runs with the hooks at target/dbt_project.yml/hooks, which should include *.sql files generated.