Skip to main content

on-run-start and on-run-end

In previous modules, we learned about pre-hook and post-hook. Those hooks are tied to specific models. They run immediately before or after a single table is built.

But what if you want to execute a SQL command before the entire project starts building? Or what if you want to insert a log entry into an audit table only after all 500 models have finished?

You use Project-Level Run Hooks: on-run-start and on-run-end.

Topics Covered​

  • Configuring Run Hooks
  • Common Use Cases
  • Advanced: The results Object

1. Configuring Run Hooks​

Run hooks are defined globally in your dbt_project.yml file. They are executed at the absolute beginning and absolute end of an invocation (like dbt run or dbt build).

# dbt_project.yml

name: 'beems_retail'
version: '1.0.0'

on-run-start:
- "CREATE STAGE IF NOT EXISTS BEEMS_PROD.RAW.TEMP_STAGE"
- "ALTER SESSION SET TIMEZONE = 'Asia/Kolkata'"

on-run-end:
- "GRANT USAGE ON SCHEMA {{ target.schema }} TO ROLE TABLEAU_READ_ONLY"

If you execute dbt run --select tag:finance, dbt will:

  1. Run the on-run-start commands.
  2. Build the 15 finance models.
  3. Run the on-run-end commands.

2. Common Use Cases​

Creating Missing Infrastructure​

If your Indian logistics company frequently spins up temporary databases for testing, you can use an on-run-start hook to ensure the necessary schemas, file formats, and stages exist before the models attempt to use them.

Suspending Virtual Warehouses​

If you are using Snowflake, you can use an on-run-end hook to forcefully suspend the warehouse. This ensures that the warehouse does not stay awake burning credits if the Auto-Suspend setting is misconfigured.

on-run-end:
- "ALTER WAREHOUSE {{ target.warehouse }} SUSPEND"

3. Advanced Use Case: The results Object (Audit Logging)​

One of the most powerful features of dbt is the results object.

During an on-run-end hook, dbt exposes a special variable called results. This object contains the execution status (success, error, skipped) and timing data for every single model that was just executed.

You can write a Jinja macro that loops through this results object and inserts a row into a Snowflake Audit Table for every model that ran!

Hands-On: Building an Audit Logger​

  1. Create the Macro (macros/audit_logger.sql):
{% macro log_run_results(results) %}

-- We only want this to run in production
{% if execute and target.name == 'prod' %}

{% for res in results %}
{% set status = res.status %}
{% set model_name = res.node.name %}
{% set execution_time = res.execution_time %}

{% set log_query %}
INSERT INTO BEEMS_PROD.AUDIT.DBT_RUN_LOGS (
RUN_TIMESTAMP, MODEL_NAME, STATUS, EXECUTION_TIME_SECONDS
) VALUES (
CURRENT_TIMESTAMP(),
'{{ model_name }}',
'{{ status }}',
{{ execution_time }}
);
{% endset %}

{% do run_query(log_query) %}
{% endfor %}

{% endif %}
{% endmacro %}
  1. Call the Macro in dbt_project.yml:
# dbt_project.yml

on-run-end:
# We pass the special `results` variable into our custom macro
- "{{ log_run_results(results) }}"

Now, every time your nightly production job finishes, your DBT_RUN_LOGS table in Snowflake will be populated with a beautiful history of exactly which models succeeded, which ones failed, and how long they took!

Summary​

  • on-run-start executes once before the dbt invocation begins.
  • on-run-end executes once after all models have finished.
  • Define them in dbt_project.yml.
  • They are excellent for managing session variables, warehouse states, or creating infrastructure.
  • The results object is available in the on-run-end context and can be used to build custom audit logging macros.