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-operationCommand - Hands-On: Building a Cleanup Operation
1. The Difference
| Feature | Hooks (pre-hook, post-hook) | Operations |
|---|---|---|
| How they are defined | dbt_project.yml or config() block | Inside a {% macro %} block |
| How they are triggered | Automatically during dbt run or dbt build | Manually triggered via dbt run-operation |
| Use Case | Granting permissions to a specific table just after it builds | Database maintenance, garbage collection, schema creation |
| Tied to the DAG? | Yes | No |
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-operationcommand. - 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
--argsflag.