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
resultsObject
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:
- Run the
on-run-startcommands. - Build the 15 finance models.
- Run the
on-run-endcommands.
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
- 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 %}
- 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-startexecutes once before the dbt invocation begins.on-run-endexecutes 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
resultsobject is available in theon-run-endcontext and can be used to build custom audit logging macros.