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!!

CodePudding user response：

One solution is to put those codes and column names into a seed into a seed e.g. 74122913.csv

Then select the data from that to build your case statement:

{%- set case_data -%}
select code, name
from {{ ref('74122913') }}
{%- endset -%}

{%- set results = run_query(case_data) -%}

{%- if execute -%}
select *,
    {%- for r in results %}
      case when what_id='{{ r["code"] }}' then what_id else null end as {{ r["name"] }}
    {%- if not loop.last -%}
      ,
    {%- endif -%}
    {% endfor %}
from dim_activities
{% endif %}

When I compiled the above model after running dbt seed this was the output:

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

One of the plus sides to this approach is non-technical users can keep the seed up to date