基于Jinja的BigQuery dbt宏：多类型列比对语法问题求助

核心错误原因 #

你当前的宏代码中，type(s1_col)判断的是dbt模板变量的类型（比如传入的列名是字符串，所以type(s1_col)永远返回string），而非数据库中列的实际数据类型。这会导致所有列都走字符串分支，当处理数值/日期列时，用空字符串做coalesce替换会触发类型不匹配错误，这是你遇到类型错误的根本原因。

修正方案 #

下面提供两种可行的实现方式，分别解决类型判断和日期/timestamp处理问题：

方案1：显式传入列类型（简单直接）

调用宏时手动指定列的数据库类型，避免依赖元数据查询：

{% macro find_mismatch_s1_s2(s1_col, s2_col, field, col_type) -%}
  {% set col_type = col_type.lower() %}
  {% if col_type in ['string', 'varchar', 'text'] %}
    coalesce({{ s1_col }}, '') != coalesce({{ s2_col }}, '') as is_{{ field }}_mismatch
  {% elif col_type in ['integer', 'int', 'bigint', 'smallint', 'float', 'numeric', 'decimal'] %}
    coalesce({{ s1_col }}, 0) != coalesce({{ s2_col }}, 0) as is_{{ field }}_mismatch
  {% elif col_type in ['date', 'timestamp', 'datetime', 'timestamptz'] %}
    {# 使用业务中不可能出现的极小日期作为哨兵值，避免误判 #}
    {% set sentinel = "'0001-01-01'" if col_type == 'date' else "'0001-01-01 00:00:00'" %}
    coalesce({{ s1_col }}, {{ sentinel }}) != coalesce({{ s2_col }}, {{ sentinel }}) as is_{{ field }}_mismatch
  {% else %}
    {{ exceptions.raise_compiler_error("不支持的列类型: " ~ col_type) }}
  {% endif %}
{% endmacro %}

调用示例：

select
  {{ find_mismatch_s1_s2('t1.username', 't2.username', 'username', 'string') }},
  {{ find_mismatch_s1_s2('t1.age', 't2.age', 'age', 'integer') }},
  {{ find_mismatch_s1_s2('t1.create_time', 't2.create_time', 'create_time', 'timestamp') }}
from {{ ref('table1') }} t1
join {{ ref('table2') }} t2 on t1.id = t2.id

方案2：自动获取列类型（无需手动传入）

通过dbt的adapter.get_columns_in_relation自动查询表的元数据，获取列的实际类型：

{# 辅助宏：根据表和列名获取实际数据类型 #}
{% macro get_column_type(relation, col_name) %}
  {% set columns = adapter.get_columns_in_relation(relation) %}
  {% for col in columns %}
    {% if col.name.lower() == col_name.lower() %}
      {{ return(col.data_type.lower()) }}
    {% endif %}
  {% endfor %}
  {{ exceptions.raise_compiler_error("列 " ~ col_name ~ " 不在表 " ~ relation ~ " 中") }}
{% endmacro %}

{# 主比对宏 #}
{% macro find_mismatch_s1_s2_auto(s1_rel, s1_col, s2_rel, s2_col, field) -%}
  {% set s1_type = get_column_type(s1_rel, s1_col) %}
  {% set s2_type = get_column_type(s2_rel, s2_col) %}
  
  {# 校验两列类型一致，避免跨类型比对错误 #}
  {% if s1_type != s2_type %}
    {{ exceptions.raise_compiler_error("列 " ~ s1_col ~ " 和 " ~ s2_col ~ " 类型不匹配: " ~ s1_type ~ " vs " ~ s2_type) }}
  {% endif %}

  {% if s1_type in ['string', 'varchar', 'text'] %}
    coalesce({{ s1_rel }}.{{ s1_col }}, '') != coalesce({{ s2_rel }}.{{ s2_col }}, '') as is_{{ field }}_mismatch
  {% elif s1_type in ['integer', 'int', 'bigint', 'smallint', 'float', 'numeric', 'decimal'] %}
    coalesce({{ s1_rel }}.{{ s1_col }}, 0) != coalesce({{ s2_rel }}.{{ s2_col }}, 0) as is_{{ field }}_mismatch
  {% elif s1_type in ['date', 'timestamp', 'datetime', 'timestamptz'] %}
    {% set sentinel = "'0001-01-01'" if s1_type == 'date' else "'0001-01-01 00:00:00'" %}
    coalesce({{ s1_rel }}.{{ s1_col }}, {{ sentinel }}) != coalesce({{ s2_rel }}.{{ s2_col }}, {{ sentinel }}) as is_{{ field }}_mismatch
  {% else %}
    {{ exceptions.raise_compiler_error("不支持的列类型: " ~ s1_type) }}
  {% endif %}
{% endmacro %}

调用示例：

{% set t1 = ref('table1') %}
{% set t2 = ref('table2') %}

select
  {{ find_mismatch_s1_s2_auto(t1, 'username', t2, 'username', 'username') }},
  {{ find_mismatch_s1_s2_auto(t1, 'age', t2, 'age', 'age') }},
  {{ find_mismatch_s1_s2_auto(t1, 'create_time', t2, 'create_time', 'create_time') }}
from {{ t1 }}
join {{ t2 }} on {{ t1 }}.id = {{ t2 }}.id

日期/timestamp类型最优处理要点 #

1. 哨兵值选择：使用业务中绝对不会出现的极小日期（如0001-01-01）作为null的替代值，确保两个null被视为匹配，一个null一个非null被视为不匹配。
2. 类型区分：DATE类型用纯日期字符串，TIMESTAMP类型用带时间的字符串，避免类型转换错误。
3. 多数据库适配（可选）：如果需要支持多种数据库，可以扩展哨兵值生成逻辑，比如：

{% macro get_date_sentinel(col_type) %}
  {% set col_type = col_type.lower() %}
  {% if col_type == 'date' %}
    {% if target.type == 'bigquery' %}DATE('0001-01-01'){% else %}'0001-01-01'::DATE{% endif %}
  {% elif col_type in ['timestamp', 'timestamptz'] %}
    {% if target.type == 'bigquery' %}TIMESTAMP('0001-01-01 00:00:00'){% else %}'0001-01-01 00:00:00'::TIMESTAMP{% endif %}
  {% endif %}
{% endmacro %}

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