CODEWITHSUNDEEP
jinja2dbt
Stephen
Stephen

Reputation: 103

dbt jinja returning the results of a query

I am trying to model the following situation:

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:

Upvotes: 1

Views: 9012

Answers (1)

Stephen
Stephen

Reputation: 103

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.

Upvotes: 3

Related Questions