如何实现动态dbt多列单位转换宏？现有宏编译异常排查

核心问题分析 #

你的代码存在两个关键错误，导致无法正常生成转换结果：

1. 宏名称拼写不匹配 #

SQL调用中使用的宏名是convert_mass_unit，但你定义的宏实际名称是convert_mass_units（末尾多了一个s），这会导致dbt编译时找不到对应的宏，直接输出无效内容或报错。

2. 宏逻辑设计错误：静态Jinja无法处理动态列值 #

原宏试图在Jinja编译阶段（代码生成时）判断from_unit_column的具体值，但from_unit_column是数据库表的列，每行的单位值不同，Jinja编译阶段无法获取运行时的每行数据，因此原有的静态判断逻辑完全无法适配动态数据场景，必须改用SQL的**动态条件判断（CASE WHEN）**来实现单位转换。

修正后的实现方案 #

步骤1：重写convert_mass_units宏 #

将宏逻辑改为生成SQL的CASE WHEN语句，让数据库在运行时动态处理每行的单位转换：

{% macro convert_mass_units(mass_column, from_unit_column, to_unit) %}
    CASE
        -- 处理from_unit为NULL的情况
        WHEN {{ from_unit_column }} IS NULL THEN NULL
        -- 单位相同，直接返回原值
        WHEN LOWER({{ from_unit_column }}) = LOWER('{{ to_unit }}') THEN {{ mass_column }}
        -- 遍历所有转换因子生成判断逻辑
        {% for key, factor in conversion_factors.items() %}
            {% set from_u, to_u = key.split('_to_') %}
            {% if to_u == to_unit.lower() %}
                WHEN LOWER({{ from_unit_column }}) = '{{ from_u }}' THEN {{ mass_column }} * {{ factor }}
            {% endif %}
        {% endfor %}
        -- 不匹配的单位返回NULL
        ELSE NULL
    END
{% endmacro %}

-- 单独定义转换因子，方便维护与复用
{% set conversion_factors = {
    'kg_to_mg': 1000000,
    'kg_to_g': 1000,
    'kg_to_lb': 2.20462,
    'kg_to_oz': 35.274,
    'kg_to_ton': 0.00110231,
    'g_to_mg': 1000,
    'g_to_kg': 0.001,
    'g_to_lb': 0.00220462,
    'g_to_oz': 0.035274,
    'g_to_ton': 0.00000110229,
    'lb_to_kg': 0.453592,
    'lb_to_g': 453.592,
    'lb_to_mg': 453592,
    'lb_to_oz': 16,
    'lb_to_ton': 0.0005,
    'oz_to_kg': 0.0283495,
    'oz_to_g': 28.3495,
    'oz_to_mg': 28349.5,
    'oz_to_lb': 0.0625,
    'ton_to_mg': 907185000,
    'ton_to_g': 907185,
    'ton_to_kg': 907.185,
    'ton_to_lb': 2000,
    'ton_to_oz': 32000
} %}

步骤2：修正SQL调用语句 #

修正宏名拼写，确保和定义一致：

SELECT *, {{ convert_mass_units(value, from_unit, "g") }} AS wt_in_g FROM T1;

验证编译结果与输出 #

执行dbt compile后，生成的SQL会自动转换为包含动态判断的语句，基于你的T1表数据，最终执行后会得到正确的转换结果：

内容的提问来源于stack exchange，提问作者abhi1610