# How to set multiple variables by running \`run\_query()\` iteratively over a list of strings

**URL:** <https://discourse.getdbt.com/t/how-to-set-multiple-variables-by-running-run-query-iteratively-over-a-list-of-strings/17903>\
**Category:** Help\
**Tags:** jinja\
**Created:** [January 26, 2025, 1:38pm UTC](https://discourse.getdbt.com/t/how-to-set-multiple-variables-by-running-run-query-iteratively-over-a-list-of-strings/17903 "2025-01-26T13:38:56Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![emman](https://avatars.discourse-cdn.com/v4/letter/e/22d042/32.png) [@emman](https://discourse.getdbt.com/u/emman)\
**Post date:** [January 26, 2025, 1:38pm UTC](https://discourse.getdbt.com/t/how-to-set-multiple-variables-by-running-run-query-iteratively-over-a-list-of-strings/17903/1 "2025-01-26T13:38:57Z")

</div>

Cross posting with [StackOverflow](https://stackoverflow.com/q/79388538/6105259).

* * *

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](https://docs.getdbt.com/reference/dbt-jinja-functions/run_query)) 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:

```sql
-- 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:

```sql
-- 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:

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

```

---

<div class="post-metadata">

**Author:** ![a\_slack\_user](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/a_slack_user/32/1629_2.png) [@a\_slack\_user](https://discourse.getdbt.com/u/a_slack_user)\
**Post date:** [January 26, 2025, 9:05pm UTC](https://discourse.getdbt.com/t/how-to-set-multiple-variables-by-running-run-query-iteratively-over-a-list-of-strings/17903/2 "2025-01-26T21:05:27Z")

</div>

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](https://getdbt.slack.com/archives/CBSQTAPLG/p1737925520019359?thread_ts=1737898762.366519&cid=CBSQTAPLG). It might not have transferred perfectly.

---

<div class="post-metadata">

**Author:** ![a\_slack\_user](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/a_slack_user/32/1629_2.png) [@a\_slack\_user](https://discourse.getdbt.com/u/a_slack_user)\
**Post date:** [January 27, 2025, 8:06am UTC](https://discourse.getdbt.com/t/how-to-set-multiple-variables-by-running-run-query-iteratively-over-a-list-of-strings/17903/3 "2025-01-27T08:06:56Z")

</div>

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](https://getdbt.slack.com/archives/CBSQTAPLG/p1737965204148339?thread_ts=1737898762.366519&cid=CBSQTAPLG). It might not have transferred perfectly.

---

<div class="post-metadata">

**Author:** ![a\_slack\_user](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/a_slack_user/32/1629_2.png) [@a\_slack\_user](https://discourse.getdbt.com/u/a_slack_user)\
**Post date:** [January 27, 2025, 8:07am UTC](https://discourse.getdbt.com/t/how-to-set-multiple-variables-by-running-run-query-iteratively-over-a-list-of-strings/17903/4 "2025-01-27T08:07:24Z")

</div>

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](https://getdbt.slack.com/archives/CBSQTAPLG/p1737965232810309?thread_ts=1737898762.366519&cid=CBSQTAPLG). It might not have transferred perfectly.

---

<div class="post-metadata">

**Author:** ![a\_slack\_user](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/a_slack_user/32/1629_2.png) [@a\_slack\_user](https://discourse.getdbt.com/u/a_slack_user)\
**Post date:** [January 27, 2025, 8:32am UTC](https://discourse.getdbt.com/t/how-to-set-multiple-variables-by-running-run-query-iteratively-over-a-list-of-strings/17903/5 "2025-01-27T08:32:42Z")

</div>

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:

````select
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>
````