dbt On-Run-Start and On-Run-End Hooks

Hook Architecture

Hook Execution Pipeline

Formal Definitions

Detailed Explanation

dbt hooks provide lifecycle management for your data pipeline. They execute at predictable points in the run lifecycle, enabling automation that integrates with your existing infrastructure.

Hook Use Cases

Code Examples

Basic Hook Configuration

# dbt_project.yml
name: 'my_project'
version: '1.0.0'

on-run-start:
  - "grant usage on schema {{ target.schema }} to role {{ target.role }}"
  - "{{ log('dbt run started at ' ~ run_started_at, info=True) }}"

on-run-end:
  - "{{ log('dbt run completed with status: ' ~ run.status, info=True) }}"
  - "grant usage on schema {{ target.schema }} to role analyst_role"

Schema Creation Hook

-- macros/create_schema_if_not_exists.sql
{% macro create_schema_if_not_exists(schema_name) %}
    {%- set sql -%}
        create schema if not exists {{ schema_name }}
    {%- endset -%}
    
    {% do run_query(sql) %}
    {{ log("Schema created/verified: " ~ schema_name, info=True) }}
{% endmacro %}

# dbt_project.yml
on-run-start:
  - "{{ create_schema_if_not_exists(target.schema) }}"
  - "{{ create_schema_if_not_exists('staging') }}"
  - "{{ create_schema_if_not_exists('analytics') }}"

Notification Hook

-- macros/notify_run_complete.sql
{% macro notify_run_complete(results) %}
    {%- set succeeded = results | selectattr('status', 'equalto', 'success') | list | length -%}
    {%- set failed = results | selectattr('status', 'equalto', 'error') | list | length -%}
    {%- set total = results | length -%}
    
    {%- set message = "dbt run complete: " ~ succeeded ~ "/" ~ total ~ " succeeded" -%}
    
    {%- if failed > 0 -%}
        {%- set message = message ~ " (" ~ failed ~ " failed)" -%}
    {%- endif -%}
    
    {{ log(message, info=True) }}
    
    {#- Send to Slack/Email/Teams -#}
    {%- if target.type == 'postgres' -%}
        select pg_notify('dbt_notifications', '{{ message }}')
    {%- endif -%}
{% endmacro %}

# dbt_project.yml
on-run-end:
  - "{{ notify_run_complete(results) }}"

Audit Log Hook

-- macros/log_run_audit.sql
{% macro log_run_audit() %}
    {%- set audit_table = target.schema ~ '.dbt_run_audit' -%}
    
    {%- set create_sql -%}
        create table if not exists {{ audit_table }} (
            run_id varchar(100),
            started_at timestamp,
            completed_at timestamp,
            status varchar(20),
            models_run integer,
            models_failed integer,
            duration_seconds integer
        )
    {%- endset -%}
    
    {% do run_query(create_sql) %}
    
    {%- set succeeded = results | selectattr('status', 'equalto', 'success') | list | length -%}
    {%- set failed = results | selectattr('status', 'equalto', 'error') | list | length -%}
    {%- set duration = (run.completed_at - run_started_at).total_seconds() | int -%}
    
    {%- set insert_sql -%}
        insert into {{ audit_table }} values (
            '{{ invocation_id }}',
            '{{ run_started_at }}',
            '{{ run.completed_at }}',
            '{{ run.status }}',
            {{ succeeded }},
            {{ failed }},
            {{ duration }}
        )
    {%- endset -%}
    
    {% do run_query(insert_sql) %}
    {{ log("Audit record logged for run " ~ invocation_id, info=True) }}
{% endmacro %}

Cleanup Hook

-- macros/cleanup_temp_tables.sql
{% macro cleanup_temp_tables() %}
    {%- set temp_schema = target.schema ~ '_temp' -%}
    
    {%- set drop_sql -%}
        begin
            for rec in (
                select table_name
                from information_schema.tables
                where table_schema = '{{ temp_schema }}'
                  and table_name like 'temp_%'
            ) loop
                execute immediate 'drop table if exists {{ temp_schema }}.' || rec.table_name;
            end loop;
        end;
    {%- endset -%}
    
    {#- Only run on supported databases -#}
    {%- if target.type in ['snowflake', 'bigquery'] -%}
        {% do run_query(drop_sql) %}
        {{ log("Temp tables cleaned up", info=True) }}
    {%- endif -%}
{% endmacro %}

Grant Permissions Hook

-- macros/grant_permissions.sql
{% macro grant_permissions(schemas, roles) %}
    {%- for schema in schemas -%}
        {%- for role in roles -%}
            {%- set grant_sql -%}
                grant usage on schema {{ schema }} to role {{ role }}
            {%- endset -%}
            {% do run_query(grant_sql) %}
            
            {%- set grant_tables_sql -%}
                grant select on all tables in schema {{ schema }} to role {{ role }}
            {%- endset -%}
            {% do run_query(grant_tables_sql) %}
            
            {%- set grant_future_sql -%}
                grant select on future tables in schema {{ schema }} to role {{ role }}
            {%- endset -%}
            {% do run_query(grant_future_sql) %}
        {%- endfor -%}
    {%- endfor -%}
    
    {{ log("Permissions granted for " ~ schemas | length ~ " schemas", info=True) }}
{% endmacro %}

Monitor Integration Hook

-- macros/update_monitor.sql
{% macro update_monitor(status) %}
    {%- set monitor_url = var('monitor_url', none) -%}
    
    {%- if monitor_url -%}
        {%- set payload = {
            "run_id": invocation_id,
            "status": status,
            "started_at": run_started_at | string,
            "project": project_name,
            "target": target.name
        } -%}
        
        {#- Send webhook notification -#}
        {{ log("Monitor update: " ~ payload, info=True) }}
    {%- endif -%}
{% endmacro %}

# dbt_project.yml
on-run-start:
  - "{{ update_monitor('started') }}"

on-run-end:
  - "{{ update_monitor(run.status) }}"

Hook Configuration Reference

Best Practices

1. Keep hooks simple - Complex logic belongs in macros
2. Idempotent hooks - Hooks should be safe to run multiple times
3. Error handling - Use try/catch in macros for resilience
4. Logging - Always log hook execution for debugging
5. Test hooks - Use dbt run-operation to test hook macros
6. Document purpose - Describe what each hook does and why
7. Order matters - Hooks execute in list order
8. Performance - Avoid slow operations in on-run-start (blocks model execution)

See Also

• dbt Projects — Project configuration and structure
• Custom Macros — Building reusable macros
• dbt Cloud — CI/CD and job scheduling
• dbt Best Practices — Operational patterns