Skip to main content

Hooks vs Operations

We have learned that Hooks are SQL statements that execute automatically during a dbt run. They are intrinsically tied to your models or your project invocation.

But what if you need to execute a SQL maintenance script that has absolutely nothing to do with building a model? For example, what if you need a script to forcefully drop all stale development schemas on a Friday evening?

You do not want this script running every time you type dbt run. You want to trigger it manually or on a separate schedule. For this, you use Operations.

Topics Covered​

  • The Difference between Hooks and Operations
  • The dbt run-operation Command
  • Hands-On: Building a Cleanup Operation

1. The Difference​

FeatureHooks (pre-hook, post-hook)Operations
How they are defineddbt_project.yml or config() blockInside a {% macro %} block
How they are triggeredAutomatically during dbt run or dbt buildManually triggered via dbt run-operation
Use CaseGranting permissions to a specific table just after it buildsDatabase maintenance, garbage collection, schema creation
Tied to the DAG?YesNo

2. Hands-On: Building a Cleanup Operation​

Let's assume our Data Engineering team in Pune generates dozens of temporary PR_TESTING_XYZ schemas in Snowflake every week during Pull Request testing.

We need a script to find and drop any PR testing schema older than 7 days.

Step 1: Define the Operation as a Macro​

Operations are just Jinja macros that execute SQL. Create a new file: macros/cleanup_stale_schemas.sql.

{% macro drop_stale_pr_schemas() %}

-- Print a message to the console
{{ log("Starting the cleanup operation...", info=True) }}

-- 1. Find all stale PR schemas in Snowflake
{% set find_schemas_sql %}
SELECT SCHEMA_NAME
FROM BEEMS_PROD.INFORMATION_SCHEMA.SCHEMATA
WHERE SCHEMA_NAME LIKE 'PR_TESTING_%'
AND CREATED < DATEADD('day', -7, CURRENT_TIMESTAMP())
{% endset %}

-- 2. Execute the query and fetch the results into a Jinja variable
{% set results = run_query(find_schemas_sql) %}

-- 3. Loop through the results and drop them
{% if execute %}
{% for row in results.rows %}
{% set schema_to_drop = row[0] %}

{{ log("Dropping schema: " ~ schema_to_drop, info=True) }}

{% set drop_sql = "DROP SCHEMA IF EXISTS BEEMS_PROD." ~ schema_to_drop %}
{% do run_query(drop_sql) %}

{% endfor %}
{% endif %}

{{ log("Cleanup complete!", info=True) }}

{% endmacro %}

Step 2: Executing the Operation​

Because this is an operation, running dbt run will completely ignore it.

To execute it, you use a special dbt CLI command: dbt run-operation, followed by the name of the macro.

dbt run-operation drop_stale_pr_schemas

3. Passing Arguments to Operations​

Operations become even more powerful when you pass arguments to them.

Suppose you want to make your cleanup macro dynamic, so you can specify exactly how many days old the schemas must be before dropping them.

Modify the macro definition:

-- Add a parameter with a default value of 7
{% macro drop_stale_pr_schemas(days_old=7) %}

When you trigger the operation from the CLI (or from an orchestrator like Airflow), you pass the arguments using a JSON string:

# Force cleanup of schemas older than 2 days
dbt run-operation drop_stale_pr_schemas --args '{"days_old": 2}'

Summary​

  • Hooks execute automatically as part of the DAG (dbt run).
  • Operations are standalone macros that execute independently.
  • You trigger operations using the dbt run-operation command.
  • Use operations for database maintenance, vacuuming, garbage collection, or custom API calls that are not tied to building specific data models.
  • You can pass dynamic arguments to operations using the --args flag.