How to set multiple variables by running `run_query()` iteratively over a list of strings

Cross posting with StackOverflow.


I want to utilize jinja syntax to reduce code repetition when assigning variables (with set). I have one preemptive query that I wish to run repeatedly, changing the value matched in its WHERE clause each time. For each iteration, I want to save the result into a separate variable.

In other words, to iterate run_query macro (doc) over a list of strings, such that the query itself remains the same, but the value matched in the WHERE clause changes according the the string in the list being iterated over.


Example

On the one hand, I have a table that specifies animals and their weights:

-- zoo.sql
WITH zoo (animal, weight) AS (
  VALUES
    ('zebra',    400),
    ('lion',     200),
    ('elephant', 4000),
    ('bear',     160)
)

On the other hand, I have a model that relies on animals’ weights. I wish to assign the weight of each into a dedicated variable in my model’s script.

So I thought of doing:

-- first step: set the names in a list, ensuring they match the values
-- in `animal` column in `zoo.sql`:
{% set ANIMALS = ["zebra", "lion", "elephant"] %} -- let's say I only want those animals

-- second step: `run_query`:
{% run_query('SELECT weight FROM zoo WHERE animal = ANIMALS') %}

-- final step: assigning into variables"
{% set ZEBRA_WEIGHT, LION_WEIGHT, ELEPHANT_WEIGHT = ..., ..., ... %} -- took from here: https://stackoverflow.com/a/40177302

Clearly there should be an iteration here, most likely using {% for animal in ANIMALS %} or something like that. But I’m totally new to this and can’t wrap my head how to do the iteration of run_query() & var assignment succinctly.

I expect the result of the iterative var assignment to be equal as if I would’ve set the variables manually:

{% set ZEBRA_WEIGHT = 400 %}
{% set LION_WEIGHT = 200 %}
{% set ELEPHANT_WEIGHT = 4000 %}

Not sure of your use case here, if you have a table called zoo then its a simple query off the table.
If you want on a list this should not be hardcoded into your script - create a seed for your lookup.

if you want to make it complicated you cale use the following
With my_soo_lookup as (
select ARRAY_CONSTRUCT(
{% for animal in zoo %}
‘{{ animal }}’
{%- if not loop.last %},{% endif -%}
{% endfor %}
) as MY_ANIMALS )
–Then flatten and use data

Note: @Nick Pinfold originally posted this reply in Slack. It might not have transferred perfectly.

Hi <@U06KL2TALG6>, thanks for your response. I’m the author of this question, and I’d love to follow up with you here.

Note: @emmanuel.shpigel originally posted this reply in Slack. It might not have transferred perfectly.

Hi <@U06KL2TALG6>, thanks for your response. I’m the author of this question, and I’d love to follow up with you here.
The use case is my attempt to avoid magic numbers

Note: @emmanuel.shpigel originally posted this reply in Slack. It might not have transferred perfectly.

Hi <@U06KL2TALG6>, thanks for your response. I’m the author of this question, and I’d love to follow up with you here.
The use case is my attempt to avoid “magic numbers” in my main model’s script. That is, let’s say that I have the following query:

from tbl```
in this :arrow_up: SQL, `100` is a magic number that we attempt to avoid. We could have done:
```{% set my_var = 100 %}

select foo * {{ my_var }} as res
from tbl```
while this is slightly better, I have a better solution. It turns out that this `100` value comes from a certain (separate) model. So the cleanest solution would be to __derive__ the value from the other model, assign it to a var in the current model, then use it.

Better yet, since there are several values to be assigned to variables, and since those values are derived from the same model in the same manner (query), I'd like to utilize iteration.

<sub>Note: `@emmanuel.shpigel` originally [posted this reply in Slack](https://getdbt.slack.com/archives/CBSQTAPLG/p1737966752535189?thread_ts=1737898762.366519&cid=CBSQTAPLG). It might not have transferred perfectly.</sub>