dbt快照宏问题：含IS_DELETED字段时WHERE子句未生效

你当前的宏逻辑错误在于仅判断is_deleted_field参数是否存在，而没有实际检测源表是否包含该字段。因为默认参数是'IS_DELETED'，所以{% if is_deleted_field %}总是为真，但如果表没有这个字段，执行SQL会报错；而你需要的是：表存在该字段时才添加AND NOT IS_DELETED，不存在则跳过。

以下是修复后的宏代码：

{% macro snapshot(source_model, is_deleted_field='IS_DELETED', current_only=True) %}

{#
  source_model     | snapshot model ref
  is_deleted_field | name of the field that marks whether a record was deleted
  current_only     | Boolean to include current records (True), or include all history (False)
#}

{% set source_relation = ref(source_model) if source_model is string else source_model %}
{% set columns = adapter.get_columns_in_relation(source_relation) %}
{% set has_is_deleted = is_deleted_field in [col.name.upper() for col in columns] %}

  SELECT
    {{ dbt_utils.star(from=source_model, except=['DBT_VALID_TO']) }},
    COALESCE(dbt_valid_to, {{ var('constants')['OPEN_END_DATE'] }}) AS dbt_valid_to,
    COALESCE(dbt_valid_to, {{ var('constants')['OPEN_END_DATE'] }}) = {{ var('constants')['OPEN_END_DATE'] }} AS is_current
  FROM
    {{ source_model }}
  WHERE 1=1

    {% if has_is_deleted %}
    AND NOT {{ is_deleted_field }}
    {% endif %}

    {% if current_only %}
    AND is_current
    {%- endif %}

{% endmacro %}

关键修改说明 #

1. 获取表字段列表：用adapter.get_columns_in_relation接口读取源表的实际字段信息，这是dbt内置的跨适配器通用方法
2. 字段存在性判断：生成has_is_deleted布尔变量，通过对比字段名（统一转大写避免大小写差异）确定表是否包含目标字段
3. 简化条件分支：移除无用的elif分支，直接根据字段实际存在情况决定是否添加NOT IS_DELETED过滤条件

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