如何在模型中查询宏内CTE的结果并进行字段处理？

问题根源 #

你遇到的语法错误核心原因是：宏返回的是包含with子句的完整SQL片段，但你在模型里把它当作子查询嵌套在了from子句内部。SQL语法中，with子句只能作为整个语句的起始结构，不能直接放在from (...)这样的子查询里，这会直接触发解析错误。

另外你的模型代码里还有一个小语法问题：trim(country_code) as country_code后面缺少逗号，导致和* except部分连在一起，也会引发语法错误。

简便解决方案（无需中间模型） #

方案1：调整宏为返回可嵌入的子查询 #

修改宏，将内部的CTE包装成一个完整的子查询，这样可以直接放在from子句中使用：

{% macro my_macro(target_table, source_name) %}
    (
        with changes as (
            -- 原宏内部的CTE逻辑
            ...
        ),
        union_of_changes as (
            -- 原宏中生成union_of_changes的逻辑
            ...
        )
        select * from union_of_changes
    ) as macro_subquery
{% endmacro %}

然后模型里直接调用宏，不需要额外套results CTE：

with final as (
    select
        object_id,
        buy_date::date as buy_date,
        trim(country_code) as country_code,
        * except(
            object_id,
            buy_date,
            country_code
        )
    from {{ my_macro(current_table, full_source) }}
)
select * from final

方案2：让宏只生成CTE片段，在模型中整合 #

如果宏的CTE逻辑需要和模型自身的CTE结合，可以把宏改成只输出CTE的定义部分，而非完整的查询：

{% macro my_macro_cte(target_table, source_name) %}
    changes as (
        -- 原宏内部的CTE逻辑
        ...
    ),
    union_of_changes as (
        -- 原宏中生成union_of_changes的逻辑
        ...
    )
{% endmacro %}

然后在模型里将宏的CTE和自己的逻辑整合：

with
    {{ my_macro_cte(current_table, full_source) }},
    final as (
        select
            object_id,
            buy_date::date as buy_date,
            trim(country_code) as country_code,
            * except(
                object_id,
                buy_date,
                country_code
            )
        from union_of_changes
    )
select * from final

中间模型方案的适用场景 #

如果你的宏逻辑会被多个模型重复调用，或者宏生成的数据集需要被多次复用，那么创建中间模型是更合理的选择：

1. 新建一个模型，直接调用宏生成基础数据集：

   {{ my_macro(target_table, source_name) }}
   

2. 其他需要做字段清洗的模型，直接基于这个中间模型查询：

   select
       object_id,
       buy_date::date as buy_date,
       trim(country_code) as country_code,
       * except(object_id, buy_date, country_code)
   from {{ ref('中间模型名称') }}
   

这种方式的优势是避免重复执行宏的逻辑，提升性能，同时让代码结构更清晰。

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