Replicate a case when statement in Jinja

Question

I want to replicate a simple case-when statement with a jinja block in dbt. How can I achieve this?

Here is my statement:

CASE status
    WHEN 0 THEN 'pending'
    WHEN 1 THEN 'ordered'
    WHEN 2 THEN 'shipped'
    WHEN 3 THEN 'received'
    WHEN 4 THEN 'delivered'
    ELSE NULL
END as status_mapping

Answer 1

You have a couple options. First, you can define the mappings in an array or dict (if the ids are not a sequence) and loop through it to produce the full case statement:

{% set statuses = ['pending', 'ordered', 'shipped', 'received', 'delivered'] %}
CASE STATUS
{% for status in statuses %}
WHEN {{ loop.index - 1 }} THEN '{{ status }}'
{% endfor %}
ELSE NULL END STATUS_MAPPING

The other option is to put the mappings into a CSV, load it as a seed data file in DBT (https://docs.getdbt.com/docs/build/seeds), then join with the seed data as a ref.

Create a file called status_mappings.csv:

status_code,status
0,pending
1,ordered
2,shipped
3,received
4,delivered

Run dbt seed, then add

WITH STATUS_MAPPINGS AS (
  SELECT * FROM {{ ref('status_mappings') }}
}

SELECT S.STATUS
FROM MY_TABLE T1
JOIN STATUS_MAPPINGS SM ON T1.STATUS_CODE = SM.STATUS_CODE