Generates a SQL CASE statement from a column name and a Jinja dictionary.

Signature

{{ nexus.case_map(column, mapping) }}

Parameter	Type	Description
column	string	The SQL column to match against
mapping	dict	Jinja dictionary of value -> SQL expression pairs

The dictionary may include a 'default' key for the ELSE clause. If omitted, the default is null.

How It Works

The macro iterates over the dictionary entries (skipping 'default') and emits a WHEN column = 'key' THEN value for each. The 'default' entry becomes the ELSE clause.

Macro Source

{%- macro case_map(column, mapping) -%}
case
    {%- for key, value in mapping.items() if key != 'default' %}
    when {{ column }} = '{{ key }}' then {{ value }}
    {%- endfor %}
    else {{ mapping.get('default', 'null') }}
end
{%- endmacro -%}

Usage

Basic Example

{% set status_labels = {
    'active': "'Active'",
    'cancelled': "'Cancelled'",
    'default': "'Unknown'",
} %}

SELECT
    {{ nexus.case_map('status', status_labels) }} as status_label
FROM orders

Compiles to:

SELECT
    case
        when status = 'active' then 'Active'
        when status = 'cancelled' then 'Cancelled'
        else 'Unknown'
    end as status_label
FROM orders

With event_maps Dictionary

The primary use case is in marketing_platform_conversions, where multiple mappings are defined in a single event_maps dictionary at the top of the file:

{% set event_maps = {
    'facebook_event_type': {
        'enrollment': "'Purchase'",
        'contract renewed': "'Purchase'",
        'default': "'Other'",
    },
    'google_ads_conversion_id': {
        'enrollment': "'12345'",
        'contract renewed': "'67890'",
    },
} -%}

SELECT
    {{ nexus.case_map('event_name', event_maps.facebook_event_type) }}
        as facebook_event_type,
    {{ nexus.case_map('event_name', event_maps.google_ads_conversion_id) }}
        as google_ads_conversion_id
FROM events

This pattern keeps all event-specific mapping logic in one visible place at the top of the model. Adding a new event or destination is a dictionary edit.

Without a Default

If 'default' is omitted, the ELSE clause evaluates to null:

{% set tier = {
    'gold': "1",
    'silver': "2",
} %}

{{ nexus.case_map('membership', tier) }}

Compiles to:

case
    when membership = 'gold' then 1
    when membership = 'silver' then 2
    else null
end

Notes

• Dictionary values are inserted as raw SQL expressions. Wrap string literals in inner quotes: "'Purchase'" (not "Purchase").
• The macro works on any column, not just event_name.
• For the full marketing conversions pattern, see the Marketing Pixels and CAPIs guide.