如何在dbt+PostgreSQL中实现月度递归循环计算？

需求背景 #

需按月份递归/循环计算数据，将中间结果保存至数据库，后续周期基于这些结果计算，细节如下：

• 基于已有数据完成当月计算；
• 基于当月结果计算下月，直至补全所有缺失月份并保存；
• 每月计算涉及4-5个复杂视图。

其他技术栈的常规解决方案 #

• 基于初始数据计算当月；
• 将结果存入技术表；
• 基于技术表计算下月；
• 重复上述步骤直至完成。

dbt中的理论实现思路 #

• 编写自定义物化方案；
• 适配实验性的insert-by-period功能；
• 通过Jinja模板一次性生成全量计算（但实现繁琐且资源消耗大）。

已尝试但受限的方案 #

曾考虑通过Airflow多次运行初始及第n月计算模型，但客户限制Airflow权限，调试不便。

当前问题 #

1. 如何在dbt中正确实现上述递归按月计算的需求？
2. 是否有规范使用insert-by-period的方法，或其他替代方案？

遇到的报错 #

使用insert-by-period时出现如下错误：

column "__period_filter__" does not exist
LINE 15: where __PERIOD_FILTER__

一、规范使用insert-by-period解决报错 #

你遇到的__PERIOD_FILTER__不存在错误，是因为未正确配置周期过滤参数，以下是规范使用步骤：

1. 配置模型的增量物化与周期参数
   在dbt_project.yml或模型配置块中，明确设置增量物化并开启insert_by_period，指定周期类型和对应字段：

   models:
     your_project:
       your_target_model:
         materialized: incremental
         insert_by_period:
           period: month
           column: calc_month  # 替换为模型中存储计算周期年月的字段
   

2. 在SQL中调用周期过滤宏
   模型的增量逻辑部分，使用dbt内置的insert_by_period_filter()宏替代硬写的__PERIOD_FILTER__，它会自动生成对应周期的过滤条件：

   {% if is_incremental() %}
     where {{ insert_by_period_filter() }}
   {% endif %}
   

二、递归按月计算的替代方案（无需Airflow） #

如果insert-by-period实验性功能不稳定，可采用以下两种更稳妥的方案：

方案1：基于dbt变量循环执行

• 逻辑：通过命令行变量指定当前计算的月份，循环调用dbt run，每次计算一个月并增量写入。
• 步骤：
  1. 在模型中添加基于变量的过滤逻辑：

     {% set target_month = var('target_month', '2024-01-01') %}
     -- 你的核心计算逻辑，仅针对target_month对应的月份
     select * from your_complex_view 
     where date_trunc('month', business_date) = cast('{{ target_month }}' as date)
     

  2. 编写简单脚本遍历缺失月份执行：

     # 示例shell脚本，遍历2024年1-6月
     for month in 2024-01-01 2024-02-01 2024-03-01 2024-04-01 2024-05-01 2024-06-01
     do
       dbt run --models your_target_model --vars '{"target_month": "'$month'"}'
     done
     

  优势：无需依赖外部调度工具，调试灵活，可精准控制每个月的计算顺序。

方案2：自定义增量物化宏

• 逻辑：编写自定义物化宏，自动检测已计算的最大月份，递归计算后续缺失月份并写入数据库。
• 核心代码示例：

  {% macro materialize_recursive_monthly() %}
    {% set max_calc_month = run_query("select max(calc_month) from your_tech_table") %}
    {% set current_month = max_calc_month[0][0] + interval '1 month' %}
    {% set end_month = date_trunc('month', current_date) %}
    
    {% while current_month <= end_month %}
      {% set insert_sql %}
        insert into your_tech_table
        select * from your_complex_view where calc_month = '{{ current_month }}'
      {% endset %}
      {% do run_query(insert_sql) %}
      {% set current_month = current_month + interval '1 month' %}
    {% endwhile %}
  {% endmacro %}
  

  然后在模型中指定自定义物化方式：

  models:
    your_project:
      your_target_model:
        materialized: recursive_monthly
  

  优势：完全在dbt内部实现，无需外部脚本，自动化程度高。

三、复杂视图的优化建议 #

针对每月涉及的4-5个复杂视图：

• 将每个视图拆解为增量模型，确保每个模型仅处理当前月份的数据，避免全量计算；
• 使用dbt的ref()函数维护依赖关系，保证每月计算时视图能基于上月的结果生成数据。

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