Skip to main content

About run_query macro

The run_query macro provides a convenient way to run queries and fetch their results. It is a wrapper around the statement block, which is more flexible, but also more complicated to use. If you are new to run_query, refer to the using Jinja guide for an example of working with the results of the run_query macro (step 8).

warning

run_query runs SQL against the warehouse whenever dbt compiles your project with a live connection, including during dbt compile and dbt docs generate (by default). This is expected because compilation resolves your Jinja and macros. If you put DML or other side-effecting statements inside run_query, they run in those workflows too unless you scope them, for example using flags.WHICH. Refer to How run_query runs during compilation and dbt docs generate.

Args​

  • sql: The SQL query to execute

Returns a Table object with the result of the query. If the specified query does not return results (for example, a DDL, DML, or maintenance query), then the return value will be none.

Note: The run_query macro will not begin a transaction automatically - if you wish to run your query inside of a transaction, please use begin and commit statements as appropriate.

Examples​

models/my_model.sql
{% if execute %}
{% set results = run_query('select 1 as id') %}
{% else %}
{% set results = none %}
{% endif %}

{% if results is not none %}
  {{ log(results.print_table(), info=True) }}
{% endif %}

{# do something with `results` here... #}
macros/run_grants.sql
{% macro run_vacuum(table) %}

  {% set query %}
    vacuum table {{ table }}
  {% endset %}

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

Here's an example of using this (though if you're using run_query to return the values of a column, check out the get_column_values macro in the dbt-utils package).

models/my_model.sql

{% set payment_methods_query %}
select distinct payment_method from app_data.payments
order by 1
{% endset %}

{% set results = run_query(payment_methods_query) %}

{% if execute %}
{# Return the first column #}
{% set results_list = results.columns[0].values() %}
{% else %}
{% set results_list = [] %}
{% endif %}

select
order_id,
{% for payment_method in results_list %}
sum(case when payment_method = '{{ payment_method }}' then amount end) as {{ payment_method }}_amount,
{% endfor %}
sum(amount) as total_amount
from {{ ref('raw_payments') }}
group by 1

You can also use run_query to perform SQL queries that aren't select statements.

macros/run_vacuum.sql
{% macro run_vacuum(table) %}

  {% set query %}
    vacuum table {{ table }}
  {% endset %}

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

Use the length filter to verify whether run_query returned any rows or not. Wrap the logic in an if execute block so run_query does not run during parsing, when execute is False.

{% if execute %}
{% set results = run_query(payment_methods_query) %}
{% if results|length > 0 %}
    -- do something with `results` here...
{% else %}
    -- do fallback here...
{% endif %}
{% endif %}

How run_query runs during compilation and dbt docs generate​

When dbt compiles models and other resources with a live warehouse connection, it evaluates your Jinja and runs any run_query() calls that your project reaches. That is normal: introspective macros need the warehouse the same way other compilation steps do. Compilation happens during dbt run and dbt build, and also during dbt compile, dbt docs generate (when compilation runs), and other commands that compile the project—not only during steps where you might picture dbt "building" tables.

dbt docs generate compiles your project by default (unless you pass --no-compile). So run_query() runs during documentation generation under the same rules as other compile workflows, including for resources that are not part of a particular dbt run selection.

execute is True during compilation​

The execute context variable is True whenever dbt compiles with a connection, not only while materializing models during dbt run. A guard like {% if execute %} still allows run_query() to run during dbt docs generate and dbt compile, because those commands compile your project. Patterns such as {% if execute and is_incremental() %} change when incremental model SQL runs, but they do not turn off compilation itself—so they do not, on their own, skip run_query() during docs or compile unless your Jinja never calls it in those paths.

If you want DML or other side-effecting SQL to run only for certain dbt commands, add another condition, for example flags.WHICH.

Scope side-effecting SQL with flags.WHICH​

Combine execute with flags.WHICH so DML runs only when the active command is one you want (run, build, and so on), and not when dbt is compiling for docs, compile, or other commands where side effects would be surprising. Refer to the flags.WHICH table for the full list of command values.

{% if execute and flags.WHICH in ['run', 'build'] %}
  {% do run_query('delete from my_scratch_table where session_id = ...') %}
{% endif %}

Adjust the list of commands to match where your macro should run.

Was this page helpful?

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

0
Loading