How to Use run_query Macro in dbt: Syntax and Examples

In dbt, use the run_query macro to execute raw SQL queries inside your models or macros and capture their results as a relation or table. This allows you to run dynamic SQL and use the query output in your dbt logic. Call run_query('YOUR SQL QUERY') to get the results during compilation or runtime.

Syntax

The run_query macro takes a single argument: a SQL query string. It executes this query and returns a Relation object representing the result.

• run_query(sql_query): Runs the SQL query passed as a string.
• sql_query: A string containing the SQL statement to execute.

You can then use the returned relation to fetch rows or columns as needed.

jinja

result = run_query('SELECT 1 AS number')

# To fetch columns from the result
columns = result.columns if result else []

Example

This example shows how to use run_query inside a dbt macro to get the count of rows from a table and use it in a conditional statement.

jinja

{% macro check_row_count() %}
  {% set query %}SELECT COUNT(*) AS total FROM {{ ref('my_table') }}{% endset %}
  {% set result = run_query(query) %}

  {% if result and result.columns[0].values()[0] | int > 100 %}
    {{ log('Table has more than 100 rows', info=True) }}
  {% else %}
    {{ log('Table has 100 or fewer rows', info=True) }}
  {% endif %}
{% endmacro %}

Output

Table has more than 100 rows

Common Pitfalls

Common mistakes when using run_query include:

• Not checking if the result is None before accessing columns, which causes errors.
• Using run_query in contexts where it cannot run (like inside certain model SQL files).
• Expecting run_query to return raw data directly; it returns a relation object that needs further processing.

Always validate the result and handle empty or null cases.

jinja

{% set result = run_query('SELECT * FROM non_existent_table') %}
{% if result is none %}
  {{ log('Query failed or returned no results', warn=True) }}
{% else %}
  {{ log('Query succeeded', info=True) }}
{% endif %}

Output

Query failed or returned no results

Quick Reference

Usage	Description
run_query('SQL')	Executes the SQL query and returns a relation object
result.columns	Access columns of the query result
result.columns[0].values()[0]	Get first value of first column
Check if result is None	Avoid errors when query fails or returns no data

Key Takeaways

Use run_query('SQL') to execute raw SQL inside dbt macros or models.

Always check if the run_query result is None before accessing data.

run_query returns a relation object, not raw data directly.

Use run_query for dynamic SQL logic or fetching metadata during runs.