DBT Jinja Case When

Question

I am very new in DBT and Jinja, and I want to optimise my Case When in SQL working with Jinja.

So, this is my situation:

select *,
      case when what_id='006' then what_id else null end as opportunity_id
      ,case when what_id='a1b' then what_id else null end as billing_acc_id
      ,case when what_id='a04' then what_id else null end as Internal_Ticket
      ,case when what_id='001' then what_id else null end as account_id
      ,case when what_id='500' then what_id else null end as case_id
      ,case when what_id='a1D' then what_id else null end as Onboarding_process_id
      ,case when what_id='a02' then what_id else null end as training_id
      ,case when what_id='00Q' then what_id else null end as lead_id
      ,case when what_id='003' then what_id else null end as contact_id
from dim_activities

And I would like to do something like the example we have in jinja/dbt documentation:

{% set payment_methods = ["bank_transfer", "credit_card", "gift_card"] %}

select
    order_id,
    {% for payment_method in payment_methods %}
    sum(case when payment_method = '{{payment_method}}' then amount end) as {{payment_method}}_amount,
    {% endfor %}
    sum(amount) as total_amount
from app_data.payments
group by 1

So instead of using many case when, to have this list of each whatid, I want to do like a mapping. But I still need to classify each whatid and the description (example, 006 means opportunity_id).

Any ideas/suggestion to achieve this?

Thanks a lot!!

Answer 1

Simeon's answer is a good one. If you don't want to use a seed, you could store this data in your model code as a dict in jinja:

{% set id_mapping = {
   "006": "opportunity_id",
   "a1b": "billing_acc_id",
   ...
} %}

and then to loop over that, you can use items():

select *,
    {%- for code, name in id_mapping.items() %}
      case when what_id='{{ code }}' then what_id else null end as {{ name }}
    {%- if not loop.last -%}
      ,
    {%- endif -%}
    {% endfor %}
from dim_activities

BUT! What you're really doing is a pivot operation. There is a macro in dbt_utils called pivot that can simplify this somewhat (it would be better if the IDs were already mapped -- out of the box you'll get column names like 006).