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

Question

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 %}

samhita · Accepted Answer · 2025-01-26 17:22:46Z

You are on the right track,I have just adjusted the steps and syntax slightly.

I have included it in a model.sql for testing but you can add the steps in a macro as well.

Define variables

{% set ANIMALS = ["zebra", "lion", "elephant"] %}
{% set weights = {} %}

and then you can run a loop on ANIMALS and fetch the weights. I have created a separate model named zoo which is used in run_query

{% for animal in ANIMALS %}
        {% set query_result = run_query('SELECT weight FROM zoo WHERE animal = \'' ~ animal ~ '\'' ) %}
        {% set weight = query_result.columns[0].values()[0] %}
        {% set weights = weights.update({animal: weight}) %}

weights generates as dictionary with animal as key and weight as value

{'zebra': 400, 'lion': 200, 'elephant': 4000}

Then you can set the values of weight like below

{% set zebra_weight = weights['zebra'] %}

Print output

Combining all the above steps in a model test1

{% set ANIMALS = ["zebra", "lion", "elephant"] %}

{% set weights = {} %}

{% if execute %}
    {% for animal in ANIMALS %}
        {% set query_result = run_query('SELECT weight FROM zoo WHERE animal = \'' ~ animal ~ '\'' ) %}
        {% set weight = query_result.columns[0].values()[0] %}
        {% set weights = weights.update({animal: weight}) %}
        
    {% endfor %}

    {% set zebra_weight = weights['zebra'] %}
    {% set lion_weight = weights['lion'] %}
    {% set elephant_weight = weights['elephant'] %}
    {{ log('zebra_weight :  ' ~ zebra_weight, info=True) }}
    {{ log('lion_weight :  ' ~ lion_weight, info=True) }}
    {{ log('elephant_weight :  ' ~ elephant_weight, info=True) }}
{% endif %}

{% set weights_list = weights.items() %}

SELECT
   {{  zebra_weight}} + {{lion_weight}} + {{ elephant_weight}} as sum_weights

Output of test1

Collectives™ on Stack Overflow

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

Example

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

Example

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related