Run SQL statements in Bolt schedules

Bolt schedules run dbt™ commands, but sometimes you need to execute plain SQL against your data warehouse as part of a job: creating a table, granting permissions, or running any other DDL or DML statement. The dbt™ run-operation command lets you do this by wrapping your SQL in a macro and running it like any other schedule command. This works with any warehouse connected to Paradime, such as Snowflake, BigQuery, Databricks, or Redshift. The statement runs through your dbt™ connection, so anything the warehouse role or service account behind your connection can execute, the operation can execute.

Prerequisites

A dbt™ project connected to your Paradime workspace
A data warehouse connection configured for your scheduler environment
Permissions on the warehouse role or service account used by your connection to run the statements you need

Create and run a SQL operation

Create a macro that wraps your SQL

In your dbt™ project, add a macro that defines the SQL and executes it with run_query():

macros/create_customer_snapshot.sql

{% macro create_customer_snapshot() %}
    {% set sql %}
        create or replace table analytics.reporting.customer_snapshot as
        select customer_id, customer_name, updated_at
        from analytics.core.customers
    {% endset %}

    {% do run_query(sql) %}
    {% do log("customer_snapshot created", info=True) %}
{% endmacro %}

The SQL can be any statement your warehouse allows: CREATE OR REPLACE, GRANT, ALTER, DELETE, or warehouse-specific commands like Snowflake’s ALTER PIPE ... REFRESH. The log() call prints a confirmation in the run logs.

Test the macro from the Code IDE

Before scheduling, run the operation from a terminal in the Code IDE to confirm it works:

dbt run-operation create_customer_snapshot

The statement executes against the warehouse connection of your current environment.

Add the command to a Bolt schedule

Create a schedule with the Standard schedule type. Deferred and Turbo CI only apply to state-aware dbt™ builds, so a plain SQL operation always uses Standard.In the Commands section, add:

dbt run-operation create_customer_snapshot

You can run the operation on its own schedule or add it before or after other dbt™ commands in an existing schedule. Commands run in order, so an operation placed after dbt build only executes when the build succeeds.

Pass arguments to your operation

Macros accept arguments, so one macro can serve multiple schedules. Pass values with the --args flag as a YAML string:

macros/grant_select_on_schema.sql

{% macro grant_select_on_schema(schema_name, role) %}
    {% set sql %}
        grant select on all tables in schema {{ schema_name }} to role {{ role }};
    {% endset %}

    {% do run_query(sql) %}
{% endmacro %}

dbt run-operation grant_select_on_schema --args '{schema_name: analytics.reporting, role: reporter}'

run_query() executes SQL whenever the macro is invoked, including during dbt compile and dbt docs generate if the macro is called from a model or hook. Macros invoked only via dbt run-operation are not affected. If you reference the macro elsewhere, guard it so the SQL runs only when intended:

{% if execute and flags.WHICH in ['run', 'build', 'run-operation'] %}
    {% do run_query(sql) %}
{% endif %}

If your statement is a CREATE OR REPLACE TABLE ... AS SELECT, consider making it a dbt™ model with a table materialization instead. You get lineage, testing, and documentation for free. Reserve run-operation for statements that do not fit the model workflow, such as grants, permissions, and warehouse-specific objects like pipes, stages, and tasks.

Hooks and operational tasks

More run-operation examples, including refreshing Snowflake pipes and warehouse cleanup.

Bolt command settings

All command types available in Bolt schedules.

​Prerequisites

​Create and run a SQL operation

​Pass arguments to your operation

​Related resources

Hooks and operational tasks

Bolt command settings

Prerequisites

Create and run a SQL operation

Pass arguments to your operation

Related resources