How to Use dbt Operations: Automating Management Tasks

dbt operations (dbt run-operation) is the mechanism for invoking Jinja macros directly, independent of model execution. It automates management tasks such as granting privileges, creating and dropping schemas, and updating metadata.

This article covers the fundamentals of the stable run-operation feature, best practices, common implementation examples, and scheduling approaches. It also highlights the points you need for the Analytics Engineer exam.

Operations Fundamentals and Design Principles

Operations execute macros outside the dbt DAG. At runtime they can use contexts such as target (profile/connection info), env_var, flags, adapter, and run_query. Because model dependency resolution does not run, they are well suited to bulk management tasks and maintenance.

The key design goals are idempotency and safe argument design. Provide a dry-run mode, target only explicit schemas and roles, and return execution logs and results in a machine-readable format (JSON) to make orchestration and auditing easier.

• Example command: dbt run-operation <macro_name> --args '{"key": "value"}'
• Representative contexts available: target, env_var, flags, adapter, run_query, log, exceptions
• Use cases: granting privileges, creating/dropping schemas, per-tag/per-environment initialization, notifying external systems

Operations execution flow (conceptual)

Minimal template (returning a JSON result)

{% macro op_template(required_arg, optional_arg='') %}
  {% if required_arg is not string %}
    {% do exceptions.raise_compiler_error('required_arg must be string') %}
  {% endif %}
  {% do log('target: ' ~ target.name ~ ' (' ~ target.type ~ ')', info=True) %}
  {% do return({'ok': True, 'arg': required_arg, 'opt': optional_arg}) %}
{% endmacro %}

Example 1: Macros that automate granting privileges

Granting USAGE on schemas and SELECT on tables/views per environment is a classic fit for operations. The example below is a simplified macro for Snowflake and Databricks (including Unity Catalog). A dry_run option lets you preview the SQL that would be issued before executing it.

BigQuery primarily uses IAM role grants, so equivalent work is typically handled through project/dataset-level permission management rather than GRANT statements. The macro below is therefore limited to Snowflake and Databricks/Spark.

• Arguments: target_schema, role, dry_run (default: false)
• Targets: Snowflake and Databricks/Spark. Other adapters raise an error.
• Output: whether execution ran and the number (or list) of statements

Example grant_usage_and_select macro

{% macro grant_usage_and_select(target_schema, role, dry_run=False) %}
  {%- set wh = target.type -%}
  {%- set statements = [] -%}
  {%- if wh == 'snowflake' -%}
    {%- do statements.append('grant usage on schema ' ~ target.database ~ '.' ~ target_schema ~ ' to role ' ~ role) -%}
    {%- do statements.append('grant select on all tables in schema ' ~ target.database ~ '.' ~ target_schema ~ ' to role ' ~ role) -%}
    {%- do statements.append('grant select on future tables in schema ' ~ target.database ~ '.' ~ target_schema ~ ' to role ' ~ role) -%}
    {%- do statements.append('grant select on all views in schema ' ~ target.database ~ '.' ~ target_schema ~ ' to role ' ~ role) -%}
    {%- do statements.append('grant select on future views in schema ' ~ target.database ~ '.' ~ target_schema ~ ' to role ' ~ role) -%}
  {%- elif wh in ['databricks','spark'] -%}
    {%- set cat = target.get('catalog') if target.get('catalog') else target.database -%}
    {%- set full_schema = cat ~ '.' ~ target_schema -%}
    {%- do statements.append('grant usage on schema ' ~ full_schema ~ ' to ' ~ role) -%}
    {%- do statements.append('grant select on all tables in schema ' ~ full_schema ~ ' to ' ~ role) -%}
    {%- do statements.append('grant select on all views in schema ' ~ full_schema ~ ' to ' ~ role) -%}
  {%- else -%}
    {%- do exceptions.raise_compiler_error('Unsupported adapter for this macro: ' ~ wh) -%}
  {%- endif -%}

  {%- if dry_run -%}
    {%- for s in statements %}{% do log(s, info=True) %}{% endfor -%}
    {% do return({'executed': False, 'statements': statements}) %}
  {%- else -%}
    {%- for s in statements %}
      {% do run_query(s) %}
      {% do log('OK: ' ~ s, info=True) %}
    {%- endfor -%}
    {% do return({'executed': True, 'count': statements|length}) %}
  {%- endif -%}
{% endmacro %}

-- Example invocation (CLI)
-- Configure the prod target in profiles.yml beforehand
-- Dry-run for review
-- dbt run-operation grant_usage_and_select --target prod --args '{"target_schema": "analytics", "role": "ANALYST_ROLE", "dry_run": true}'
-- Real execution
-- dbt run-operation grant_usage_and_select --target prod --args '{"target_schema": "analytics", "role": "ANALYST_ROLE"}'

Example 2: Automating schema lifecycle (safe create/drop)

Creating temporary schemas in verification environments and cleaning them up after acceptance testing are also good fits for operations. To minimize blast radius, act only on an explicit list and require a dry-run policy.

Below is a macro that bundles schema create/drop. Most warehouses support create schema if not exists / drop schema if exists.

• Arguments: schemas (string or array), action (create/drop), dry_run
• Target only explicitly named schemas. Avoid wildcards or guessing.
• When dropping, tune the use of CASCADE to your DWH policy.

Example ensure_schemas macro

{% macro ensure_schemas(schemas=[], action='create', dry_run=False) %}
  {%- if schemas is string -%}
    {%- set schemas = [schemas] -%}
  {%- endif -%}
  {%- set stmts = [] -%}
  {%- for s in schemas -%}
    {%- if action == 'create' -%}
      {%- set stmt = 'create schema if not exists ' ~ target.database ~ '.' ~ s -%}
    {%- elif action == 'drop' -%}
      {%- set stmt = 'drop schema if exists ' ~ target.database ~ '.' ~ s ~ ' cascade' -%}
    {%- else -%}
      {%- do exceptions.raise_compiler_error("action must be 'create' or 'drop'") -%}
    {%- endif -%}
    {%- do stmts.append(stmt) -%}
  {%- endfor -%}

  {%- if dry_run -%}
    {%- for s in stmts %}{% do log(s, info=True) %}{% endfor -%}
    {% do return({'executed': False, 'statements': stmts}) %}
  {%- else -%}
    {%- for s in stmts %}{% do run_query(s) %}{% endfor -%}
    {% do return({'executed': True, 'count': stmts|length}) %}
  {%- endif -%}
{% endmacro %}

-- Example invocation
-- dbt run-operation ensure_schemas --target dev --args '{"schemas": ["tmp_feature_a", "tmp_feature_b"], "action": "create", "dry_run": true}'

Scheduling: dbt Cloud and External Orchestration

In a dbt Cloud Job you can add run-operation as a Step. A common pattern is Step 1 grants privileges, Step 2 runs the production pipeline (dbt build), and Step 3 cleans up. Schedules and triggers keep it running reliably, and logs/results are visible in the Cloud UI.

CI/CD (such as GitHub Actions) and external orchestrators (Airflow, Dagster, etc.) reproduce the same behavior by calling the same CLI. Collect the JSON in stdout and persist it as an audit log for later verification.

• dbt Cloud: add dbt run-operation ... as a Step in the Job
• GitHub Actions: install dbt and the adapter on the runner, then run the CLI
• Airflow: invoke the CLI via BashOperator or KubernetesPodOperator and store results in XCom

GitHub Actions example (Snowflake adapter)

name: run-operations
on:
  workflow_dispatch:
jobs:
  op:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - uses: actions/setup-python@v5
        with:
          python-version: '3.11'
      - name: Install dbt core and adapter
        run: |
          pip install --upgrade pip
          pip install dbt-core dbt-snowflake
      - name: Run dbt operation (dry-run)
        env:
          DBT_USER: ${{ secrets.DBT_USER }}
          DBT_PASSWORD: ${{ secrets.DBT_PASSWORD }}
        run: |
          dbt deps
          dbt debug --target prod --profiles-dir .
          dbt run-operation grant_usage_and_select \
            --target prod \
            --args '{"target_schema": "analytics", "role": "ANALYST_ROLE", "dry_run": true}'

Safety and Best Practices

Ops macros tend to have a large blast radius when they fail. Build them around dry-run, explicit targets, pre-execution logging, and early termination on failure (compiler_error). Wrap them in a transaction where appropriate, or conversely run DDLs in small chunks to make the failure point easy to identify.

Validate input strictly and minimize branching on environment variables or target. For grants, follow the principle of least privilege and use future grants where appropriate.

• Idempotency: repeating the same operation must not change the consistent state.
• dry-run: log every SQL statement so they can be reviewed.
• Input validation: validate schema/role names and reject empty values.
• Minimize blast radius: forbid wildcard deletes; act only on explicit lists.
• Fail fast: use exceptions.raise_compiler_error to fail safely.
• Audit: persist the JSON from return() to keep work traceable.

Lightweight transaction wrapper (only on supporting DWHs)

{% macro run_in_transaction(sqls=[]) %}
  {# Note: Some DWHs do not support DDL transactions #}
  {% do run_query('begin') %}
  {% for s in sqls %}
    {% do run_query(s) %}
  {% endfor %}
  {% do run_query('commit') %}
  {% do return({'executed': sqls|length}) %}
{% endmacro %}

-- Example usage
-- {% do run_in_transaction(['grant usage on schema DB.SCHEMA to role R', 'grant select on all tables in schema DB.SCHEMA to role R']) %}

Logging and Handling Return Values

Macros invoked via dbt run-operation print the object you pass to return() on stdout. Keeping it as machine-readable JSON makes it easy to collect and validate from outside. Pair it with log() for human-readable messages.

Below is an example of extracting just the JSON portion from the CLI output for saving or alerting. Depending on the environment, the --quiet option produces less noise and is easier to consume.

• Calling return() exactly once is easiest to work with in practice.
• Log important SQL at info level and split details to debug level.
• Use jq or similar to extract the JSON externally and persist it for auditing.

Example of extracting JSON from stdout (bash)

# Capture the dry-run result
out=$(dbt --quiet run-operation grant_usage_and_select --args '{"target_schema": "analytics", "role": "ANALYST_ROLE", "dry_run": true}')
# dbt prints in the form "The result of this operation was: <JSON>", so extract the JSON portion
json=$(printf "%s\n" "$out" | sed -n 's/.*The result of this operation was:\s*//p')
# Optional: format and save
printf "%s\n" "$json" | jq .
echo "$json" > operation-result.json

Check Your Understanding

Frequently Asked Questions