[Python] How to set multiple variables in DBT/SQL by running `run_query()` iteratively over a...

In dbt, 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 %}

Continue reading...