如何在dbt的Jinja表达式宏调用中引用CTE？

问题核心 #

本质矛盾是Jinja编译时机与SQL运行时机不匹配：

• Jinja宏（包括get_earliest_date、run_query）在dbt编译SQL阶段就会执行
• 你定义的stg_example_table CTE是SQL运行阶段才会生成的临时表

所以Jinja处理宏时，这个CTE还不存在，直接引用自然无效；而用ref('stg_db__example_table')调用宏时，用的是原始表而非过滤后的CTE，导致结果错误。

可行解决方案 #

方案1：将CTE过滤逻辑直接传入宏 #

把CTE的过滤逻辑写成子查询，作为参数传给get_earliest_date宏，让宏直接查询过滤后的数据集：

修改后的SQL代码

SELECT * FROM {{ dbt_utils.date_spine(
    datepart="day",
    start_date="'" ~ get_earliest_date("(SELECT * FROM " ~ ref('stg_db__example_table') ~ " WHERE some_filter = true)") ~ "'",
    end_date="current_date"
) }}

说明

宏get_earliest_date会把传入的子查询作为查询对象，直接获取过滤后数据集的最早日期，无需依赖SQL里的CTE。

方案2：封装CTE逻辑为复用宏（避免重复代码） #

如果CTE过滤逻辑复杂，可将其封装成单独的Jinja宏，在SQL的CTE块和宏调用中复用：

步骤1：定义复用宏

在macros/目录下新建宏文件（比如stg_example_macros.sql）：

{% macro get_filtered_stg_example() %}
    SELECT * FROM {{ ref('stg_db__example_table') }}
    WHERE some_filter = true
{% endmacro %}

步骤2：在模型中复用

WITH stg_example_table AS (
    {{ get_filtered_stg_example() }}
)

SELECT * FROM {{ dbt_utils.date_spine(
    datepart="day",
    start_date="'" ~ get_earliest_date("(" ~ get_filtered_stg_example() ~ ")") ~ "'",
    end_date="current_date"
) }}

说明

SQL里的CTE和宏调用共用同一套过滤逻辑，既保证数据一致性，又避免重复代码。

方案3：用run_query直接执行包含CTE的查询 #

如果一定要基于SQL里的CTE逻辑获取最早日期，可把整个CTE+取数逻辑写到run_query的查询语句中，让Jinja直接执行完整查询：

模型代码

{% set earliest_date_query %}
    WITH stg_example_table AS (
        SELECT * FROM {{ ref('stg_db__example_table') }}
        WHERE some_filter = true
    )
    SELECT MIN(date_column) FROM stg_example_table
{% endset %}

{% set earliest_date = run_query(earliest_date_query).columns[0][0] if execute else '2020-01-01' %}

SELECT * FROM {{ dbt_utils.date_spine(
    datepart="day",
    start_date="'" ~ earliest_date ~ "'",
    end_date="current_date"
) }}

说明

• run_query会在Jinja编译阶段执行这段完整SQL，直接拿到过滤后的最早日期
• if execute是为了避免dbt语法解析阶段报错（解析时execute为False，返回默认日期）

为什么之前的run_query尝试失败？ #

你之前用run_query时，可能只在查询里引用了stg_example_table这个CTE名字，但这个CTE仅存在于当前模型的SQL运行阶段，run_query执行的是独立查询，无法识别未在其内部定义的CTE，因此必须把CTE的完整逻辑写到run_query的查询语句中。

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