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

Hook Architecture

Hook Execution Pipeline

Formal Definitions

Detailed Explanation

What are On-Run-Start and On-Run-End Hooks?

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

Category	on-run-start	on-run-end
Schema	Create/grant schemas	Revoke temporary access
Security	Set session policies	Audit log entries
Notifications	Start run notifications	Success/failure alerts
Cleanup	Drop temp tables	Archive run artifacts
Monitoring	Update run status	Send metrics to monitoring
Data	Backfill validation	Post-run transformations

Key Takeaway: on-run-start hooks execute before any models — if they fail, the run aborts. on-run-end hooks execute even when the run fails, making them ideal for cleanup and notifications.

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

Setting	Description	Example
`on-run-start`	List of SQL/macros before run	Schema creation
`on-run-end`	List of SQL/macros after run	Notifications
`--fail-fast`	Stop on first error	`dbt run --fail-fast`
`--no-version-check`	Skip version check	`dbt run --no-version-check`

Best Practices

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

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

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

Hook Architecture

Hook Execution Pipeline

Formal Definitions

Detailed Explanation

What are On-Run-Start and On-Run-End Hooks?

Hook Use Cases

Code Examples

Basic Hook Configuration

Schema Creation Hook

Notification Hook

Audit Log Hook

Cleanup Hook

Grant Permissions Hook

Monitor Integration Hook

Hook Configuration Reference

Best Practices

See Also

Need Expert dbt Help?