03. Automation Use Cases
Automation Use Cases with Hooks & Operations
dbt allows you to automate repetitive tasks using hooks and operations.
This helps maintain data quality, audit trails, and efficient workflows in your data warehouse.
1. Pre/Post Hooks Automation
Use Case: Automatically log start and end times of model runs.
Example:
{{ config(
pre_hook="insert into audit_log (model_name, run_type, run_time) values ('{{ this.name }}', 'start', current_timestamp);",
post_hook="insert into audit_log (model_name, run_type, run_time) values ('{{ this.name }}', 'end', current_timestamp);"
) }}
select * from {{ source('app', 'orders') }}
pre_hook→ logs start of the model runpost_hook→ logs completion of the model run- Ensures automatic auditing without manual intervention
2. Granting Permissions Automatically
Use Case: Grant access to tables or views after models are built.
Example using Post-hook:
{{ config(
post_hook="grant select on {{ this }} to role analytics_team;"
) }}
select * from {{ source('app', 'customers') }}
- Automatically applies permissions after table creation
- Avoids manual GRANT statements and ensures security compliance
3. Running Custom Operations
Use Case: Execute administrative or housekeeping tasks.
Macro Example:
{% macro refresh_materialized_views() %}
refresh materialized view analytics.customer_summary;
refresh materialized view analytics.sales_summary;
{% endmacro %}
Run via CLI:
dbt run-operation refresh_materialized_views
- Refreshes materialized views automatically
- Can be scheduled or triggered before/after data loads
4. Data Quality Checks
Use Case: Validate table data automatically after loading.
Example Macro:
{% macro check_nulls(table_name, column_name) %}
select count(*) as null_count
from {{ table_name }}
where {{ column_name }} is null;
{% endmacro %}
Run as operation or post-hook:
dbt run-operation check_nulls --args '{"table_name": "app.orders", "column_name": "order_id"}'
- Ensures data integrity
- Automates validation instead of manual queries
Best Practices for Automation
Keep hooks and operations lightweight and fast
Reuse macros for common tasks
Use logging/auditing for tracking automated runs
Combine hooks and operations for end-to-end automation