dbt jinja returning the results of a query

Question

I am trying to model the following situation:

• given some query, return multi-column result-set (e.g. run_query or db_utils.get_query_results_as_dict

• iterate over in a case/statment

for exmaple:

{% set conditions = dbt_utils.get_query_results_as_dict("select comment, criteria from " 
~ ref('the_model') %}

...
select case
{% for condition in conditions %}
when {{ condition["criteria"] }}
then {{ condition["comment"] }}
{% endfor %}

Have not been able to get this to work, any guidance appreciated.

Some ideas I tried:

• get_column_values x2 and zipping them into a new list of tuples. zip not recognised
• get the count(*) from the_model then trying to iterate over the range - ran into issues with types
• various for conditions {% for k, v in conditions.items() %}

Answer 1

Was able to self resolve with the following:

{% set conditions = dbt_utils.get_query_results_as_dict("select criteria, comment from " ~ ref('reference_data') ~ " order by sequence desc") %}

with main as (
    select * from {{ ref('my_other_model') }}
),

-- [NEEDS_REVIEW] there's probably a cleaner way to do this iteration - however it's interpolated result. Could do with the zip function.
comments as (
    select
        *,
        case
            {# {{- log(conditions, info=True) -}} #}
            {%- for comment in conditions.COMMENT -%}
            when {{ conditions.CRITERIA[loop.index0] }}
            then '{{ comment }}'
            {% endfor %}
        end as comment

        from main
)

select * from comments

The gotchas:

• this was on snowflake, so the keys returned by the function will be up-cased as that is how I loaded the data.
• Using the loop.index0 to get the current iteration of the loop and index into the other collection of tuples (in this case CRITERIA).
• i added a SEQUENCE key to my reference data just to ensure consistent rendering by using that to order. The criteria do overlap a-little bit so this was important.