Querying from Jinja

Concepts covered: Jinja in models

run_query(sql) sends a query over the open connection and hands back the result as a table object. result.columns[0].values() gives the first column as a list and result.rows iterates rows; result.print_table() is for debugging, since it writes the whole thing to the log. During the parse it returns None, which is the reason every call lives behind execute. The query fires when the calling model compiles, and under dbt run a node compiles right before it runs, after every model it ref()s has been built. A model can therefore read a table that an upstream model finished 10 seconds earlier in the same invocation.

run_query wraps an older pair: a {% call statement('methods', fetch_result=True) %} block that holds the SQL, then load_result('methods') to fetch what it returned. Older projects and packages use the pair; both forms are the same round trip, and load_result returns nothing useful offline either.

One column per value the data holds

A payments feed carries a method column that holds card and bank today, and later whatever the payments team launches next. The finance mart wants one amount column per method. Typing card_amount and bank_amount by hand means a code change each time a method appears. Instead, the macro below asks the warehouse which methods exist, and the model writes its column list from the answer. The animation runs that model twice, and a wallet payment lands between the runs.

{% macro pay_methods() %}
  {% set q %}
    select distinct method
    from {{ source('raw', 'payments') }}
    order by 1
  {% endset %}
  {% if execute %}
    {{ return(run_query(q).columns[0].values()) }}
  {% endif %}
  {{ return([]) }}
{% endmacro %}

select
  {% for m in pay_methods() %}
  sum(if(method = '{{ m }}',
    amount, 0)) as {{ m }}_amount,
  {% endfor %}
  count(*) as payment_count
from {{ source('raw', 'payments') }}

Read the second compiled SQL against the first. Nothing in models/ changed, and the mart has a new column. The {% if execute %} in pay_methods() is what lets dbt parse finish: offline, the macro returns an empty list, the for loop emits nothing, and the model still parses as a valid SELECT. The {% set q %} block sits above the branch, so its source() renders in both phases and the edge to raw.payments is drawn; only run_query waits for the connection. Under dbt compile in a schema where raw.payments does not exist yet, that call fails with a missing-table error, since compile renders with execute on.

A shape that moves without a commit

A column list that follows the data also breaks whatever depends on it. A downstream model that selects wallet_amount by name works only once a wallet payment exists, and breaks the day the method is retired and its rows deleted. A not_null test declared on card_amount in YAML fails with an unknown-column error if card payments stop. A model contract (Models: Advanced) freezes the column list and rejects the third column. So a data-driven shape belongs at the edge of the graph, where nothing downstream names its columns, or it should take its list from something you control.

List from the raw data

• Any new value appears as a column on the next run
• A retired value drops its column, and whatever named it breaks
• Column order follows the query's order by and nothing else

List from a seed

• A new value is a row added to the seed: a commit and a review
• A column exists even in a week with 0 rows of that value, filled with zeros
• The seed is a ref(), so lineage shows where the list came from

Columns, not values

adapter.get_columns_in_relation(relation) asks the warehouse for a relation's columns and returns objects with .name and .data_type. It is how a model selects every column of its upstream except the load metadata, without listing 40 names. The parse branch here falls back to *, which keeps the offline render a valid SELECT. dbt_utils.star() does the same with the guards included, and dbt_utils.get_column_values() is the packaged form of the pivot above.

{% set skip = ['_loaded_at', '_source_file'] %}
{% set cols = [] %}
{% if execute %}
  {% for c in adapter.get_columns_in_relation(ref('stg_events')) %}
    {% if c.name not in skip %}
      {% do cols.append(c.name) %}
    {% endif %}
  {% endfor %}
{% endif %}

select
    {{ cols | join(', ') if cols else '*' }}
from {{ ref('stg_events') }}

Each of these queries is a round trip issued while the node compiles, one at a time. A project with 40 such models adds 40 queries to every dbt run and every dbt compile, and each result sits in memory, so run_query over a fact table is a mistake even when it works. Keep the questions to metadata-sized answers: distinct values of a low-cardinality column, a column list, a single max date.

About this lesson

This section is part of the Jinja and Macros: Advanced lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.