DBT中CTE模型调用宏传递值错误问题求助

你遇到的问题是调用宏时传了字符串"level"，导致宏在编译阶段把这个字符串转成int得到0，所以打印输出总是Running some_macro: 0, level，最终生成的SQL里segment_level都是固定值，不会根据实际level字段计算。

问题根源 #

你调用宏时用了{{ map_level_to_level_range("level") }}，这里的"level"是字符串字面量，不是对表中level字段的引用。宏在Dbt编译阶段会直接处理这个字符串，把它转成int时，非数字字符串会被转为0，所以所有分支都走第一个条件，得到'00 - 04'。

修复方案 #

要让宏生成能在SQL执行阶段根据每条记录的level字段计算的表达式，而不是在编译阶段直接计算固定值。修改如下：

修改后的CTE代码

final_table as (
        select
            toDate(`log_time`, 'Asia/Ho_Chi_Minh') as `report_date`,
            `social_type`,
            `platform_id`,
            {{ map_level_to_level_range("level") }} as `segment_level`,
            `level`
        from last_user_action
)

修改后的宏代码

{% macro map_level_to_level_range(level_column) %}
    case
        when {{ level_column }} between 0 and 4 then '00 - 04'
        when {{ level_column }} between 5 and 9 then '05 - 09'
        when {{ level_column }} between 10 and 14 then '10 - 14'
        when {{ level_column }} between 15 and 19 then '15 - 19'
        when {{ level_column }} between 20 and 24 then '20 - 24'
        when {{ level_column }} between 25 and 29 then '25 - 29'
        when {{ level_column }} between 30 and 34 then '30 - 34'
        when {{ level_column }} between 35 and 39 then '35 - 39'
        when {{ level_column }} between 40 and 44 then '40 - 44'
        when {{ level_column }} between 45 and 49 then '45 - 49'
        when {{ level_column }} between 50 and 54 then '50 - 54'
        when {{ level_column }} between 55 and 59 then '55 - 59'
        when {{ level_column }} between 60 and 64 then '60 - 64'
        when {{ level_column }} between 65 and 74 then '65 - 74'
        when {{ level_column }} between 75 and 84 then '75 - 84'
        when {{ level_column }} between 85 and 99 then '85 - 99'
        when {{ level_column }} between 100 and 114 then '100 - 114'
        when {{ level_column }} between 115 and 129 then '115 - 129'
        when {{ level_column }} between 130 and 149 then '130 - 149'
        when {{ level_column }} >= 150 then '150+'
        else '-1'
    end
{% endmacro %}

说明 #

修改后的宏会生成SQL的case表达式，在SQL执行阶段，每条记录都会用自己的level字段值去匹配条件，得到正确的segment_level。之前的写法是在Dbt编译阶段就把值固定了，自然无法对应每条记录的实际level。

内容的提问来源于stack exchange，提问作者Thủy Hồ Hải