Feed Jinja list with results of SQL query

Question

I'm new to DBT and Jinja and wondering if it is possible to dynamically define a list using a SQL query. Instead of manually declaring the items of the list like:

{% set myOrders = [123,234, 345, 456, 567] %}

Define the list with a SQL query, something like this:

{% set myOrders = SELECT DISTINCT OrderNum FROM OrdersTable ORDER BY OrderNum  %}

Is this possible?

Thanks!

Answer 1

Yes! Not quite as you've written it, but this is supported.

First, a note that this is inherently difficult because DBT typically runs in two phases:

1. templates are compiled to make actual SQL queries (i.e. all the Jinja gets executed)
2. the compiled SQL queries are executed

But there is a construction, {% if execute %}, that allows you to defer compilation to the execution stage.

Straightforwardly adapting the example in the docs for your use case:

{% set my_orders_query %}
  SELECT DISTINCT OrderNum
    FROM {{ ref('OrdersTable') }}
ORDER BY OrderNum
{% endset %}

{% set rows = run_query(my_orders_query) %}

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