The dbt run-operation Command
Up until now, every macro we have written has been injected inside a SELECT statement within a .sql model file. The macro is executed when you type dbt run, and its only purpose is to help build that specific table.
But what if you want to write a macro that performs an administrative task on your Snowflake database, completely unrelated to building a table?
For example, what if you want to write a macro that automatically grants SELECT permissions to the Tableau user for every schema in your database?
You can't put that inside a SELECT statement. You need a way to execute a macro standalone. You do this using the dbt run-operation command.
Topics Covered
- Writing an Operational Macro
- The
run_queryfunction - Executing via the CLI
- Passing Arguments
1. Writing an Operational Macro
Let's assume our Data Engineer, Priya, is managing the Snowflake environment for an Indian startup.
Every Friday, she needs to execute a GRANT USAGE ON SCHEMA ... TO ROLE BI_ANALYST command for several new schemas created during the week. Instead of doing this manually in the Snowflake UI, she writes an operational macro.
-- macros/grant_bi_access.sql
{% macro grant_bi_access() %}
{# Define the Snowflake DDL command #}
{% set grant_sql %}
GRANT USAGE ON SCHEMA BEEMS_PROD.MARTS TO ROLE BI_ANALYST;
GRANT SELECT ON ALL TABLES IN SCHEMA BEEMS_PROD.MARTS TO ROLE BI_ANALYST;
{% endset %}
{# Use the special run_query function to send the DDL directly to Snowflake! #}
{% do run_query(grant_sql) %}
{# Print a success message to the terminal #}
{{ log("Successfully granted permissions to BI_ANALYST!", info=True) }}
{% endmacro %}
2. Executing via the CLI
Because this macro is not attached to a model, Priya cannot run it using dbt run.
Instead, she opens her terminal and uses the run-operation command, passing the exact name of the macro she wants to execute:
dbt run-operation grant_bi_access
dbt will connect to Snowflake, execute the run_query block, print the success message to the terminal, and disconnect. No models are built, and no data is transformed.
3. Passing Arguments
The macro above is hardcoded to the MARTS schema. What if Priya wants to make it dynamic so she can pass the schema name as an argument?
She updates the macro definition:
{% macro grant_bi_access(schema_name) %}
{% set grant_sql %}
GRANT USAGE ON SCHEMA BEEMS_PROD.{{ schema_name }} TO ROLE BI_ANALYST;
GRANT SELECT ON ALL TABLES IN SCHEMA BEEMS_PROD.{{ schema_name }} TO ROLE BI_ANALYST;
{% endset %}
...
Now, when she runs the macro from the terminal, she uses the --args flag to pass a dictionary (JSON) of arguments!
dbt run-operation grant_bi_access --args '{"schema_name": "FINANCE_REPORTS"}'
4. Common Use Cases
The run-operation command is incredibly versatile. It is commonly used for:
- Security: Granting permissions or defining row-level security masking policies.
- Maintenance: Running
VACUUMorOPTIMIZEcommands on Delta Lake or Redshift tables. - Data Quality: Truncating specific audit tables or deleting expired records.
- Code Generation: As we saw with the
dbt-codegenpackage, printing generated SQL to the terminal screen.
Summary
- Use
dbt run-operation <macro_name>to execute Jinja macros independently of the DAG. - Operational macros use the Jinja
{% do run_query(sql) %}function to execute DDL/DML statements directly against the database. - Use the
--argsflag in the terminal to pass variables (formatted as a JSON dictionary) directly into the macro.