How to join two tables into a dictionary in dbt jinja

user_17385487 0
Viewed 1166

I want to create a for loop in dbt that loops through two columns of a table in my database and created case when statements using the values in each row of the two columns. Just like this:

{% set locations = {'%United-States%':'United States', '%USA%':'United States'} %}

select
    case
        {% for feeder, correct in locations.items() %}
            when lower(locationname) like {{feeder}} then {{correct}}
        {% endfor %}
    end as city
from table

I was able to create lists for both feeder and correct, but I am not able to merge them as key-value pairs of a dictionary to loop through it. Any ideas on how should I do this?

dbt jinja2 list dictionary loops
1 Answers

Sounds like you have two problems:

  1. fetch data from another table, and
  2. use that data to populate a case when statement.

Big thing to remember is that the main thing that dbt-jinja does is create a string of SQL. That said, there is clever functionality that lets you query a database before jinja starts to put the string together.

The idea being:

  1. fetch the values from the database that you want to have included in your SELECT query
  2. populate the previously fetched values into the query while rendering the statement

there's two macros that may be of use in the first step:

  • run_query() (docs), and
  • dbt-utils' get_query_results_as_dict() (docs)

Something like this might work (provided you already have dbt-utils installed:


{% set locations_query %}
select feeder, correct from my_other_table
{% endset %}
{% set locations = run_query(locations_query) %}

select
    case
        -- not sure how this part will work yet....
        {% for feeder, correct in locations.items() %}
            when lower(locationname) like {{feeder}} then {{correct}}
        {% endfor %}
    end as city
from table
user_38426100
Related