Hooks and Operations

Concepts covered: Macros and packages

A model is a SELECT with a materialization around it. Some SQL fits neither: a grant on the table once it exists, a row written to an audit table when the run ends, a schema created before anything else, a one-off repair. dbt runs that SQL from 5 places: 4 hooks tied to moments in a build, and an operation that runs apart from one. None of them is a model's SELECT, and each sees a different set of things.

Model hooks run on the model's connection and as part of the model's node: a pre_hook that errors fails the node, and its children skip exactly as they would behind a failed test (Testing: Beginner). The choice among the 5 is about timing. Use a model hook for work tied to one table and its build, a project hook for work tied to the invocation, and an operation, covered below, for work a person decides to run on demand.

post_hook: SQL that needs the table to exist

{{ config(
    materialized='table',
    post_hook="grant select on {{ this }} to role reporting"
) }}

select
    order_id,
    customer_id,
    total_cents
from {{ ref('stg_orders') }}

The hook string is Jinja too, rendered when the hook runs, so {{ this }} resolves to the table the model produced. Several hooks go in a list and run in that order. A hook can also be a macro call that returns SQL, which is how one grant reaches every mart from dbt_project.yml, where a +post-hook under a folder applies to every model beneath it. If the model's own statements fail, its post hook never runs.

models:
  shop:
    marts:
      +post-hook: "{{ grant_reporting(this) }}"

on-run-end: a record of the whole run

on-run-end:
  - "{{ record_run_results(results) }}"

{% macro record_run_results(results) %}
  {% if execute and results %}
    insert into audit.model_runs
    values
    {% for r in results %}
        (
            '{{ invocation_id }}',
            '{{ r.node.name }}',
            '{{ r.status }}',
            {{ r.execution_time }}
        ){{ ',' if not loop.last }}
    {% endfor %}
  {% else %}
    select 1
  {% endif %}
{% endmacro %}

results holds one record per node dbt attempted: r.node.name, r.status (success, error or skipped for a model; pass, fail or warn for a test), r.execution_time in seconds, and r.message. invocation_id is the same value across every row of one run, which is what joins the audit rows back to a single invocation. The macro renders one insert with a row per node, and the hook sends that text as its statement. Offline, or when no node ran, it renders select 1 instead, because an insert with no rows is invalid SQL.

run-operation: a macro as the whole job

dbt run-operation create_audit_table calls the macro with execute set to True, after parsing the project and opening a connection. The invocation builds no node, so nothing is materialized and nothing enters the lineage graph. The macro has to send its own SQL with run_query or inside a statement block, because a string it returns goes nowhere. Arguments arrive through --args '{days: 7}' as a YAML mapping. It is the home for maintenance: creating a schema, dropping the tables a renamed model left behind (Models: Advanced), a one-time grant, or a repair statement someone would otherwise paste into a console and never record.

{% macro create_audit_table() %}
  {% set ddl %}
    create table if not exists audit.model_runs (
        invocation_id varchar,
        model_name varchar,
        status varchar,
        seconds double
    )
  {% endset %}
  {% do run_query(ddl) %}
  {{ log("audit.model_runs is ready", info=True) }}
{% endmacro %}

About this lesson

This section is part of the Jinja and Macros: Advanced lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.