Skip to main content

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 run
  • post_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