在dbt（Snowflake）中捕获宏返回的单行4字段结果报错求助

问题背景 #

在Snowflake数据库环境下使用dbt时，编写了一个返回单行4字段的宏，但在dbt模型中捕获宏的结果时出现报错。

宏代码 #

{% macro mac_em_edw_delta_attributes(em_schema_in,em_table_in,edw_schema_in,edw_table_in,exclude_columns_in) %}
--start macro
{% set var_sql_stmt %}
...<some complex sql cte statements>...
SELECT
    listagg('em_table.'||COLUMN_NAME,',') WITHIN GROUP (ORDER BY COLUMN_NAME) AS COLUMNS_LIST_EM,
    listagg('edw_table.'||COLUMN_NAME,',') WITHIN GROUP (ORDER BY COLUMN_NAME) AS COLUMNS_LIST_EDW,
    listagg('em_table.'||COLUMN_NAME||' As '||COLUMN_NAME||'_em, '||'edw_table.'||COLUMN_NAME||' As '||COLUMN_NAME||'_edw',',') WITHIN GROUP (ORDER BY COLUMN_NAME) AS COLUMNS_SELECT,
    listagg('em_table.'||COLUMN_NAME||'<>'||'edw_table.'||COLUMN_NAME,' OR ') WITHIN GROUP (ORDER BY COLUMN_NAME) AS COLUMNS_EXPR
FROM columns_to_compare 
{% endset %}

{% set var_sql_resut = run_query(var_sql_stmt) %}

{% if execute %}
      {{ return(var_sql_resut) }}
  {% else %}
      {{ exceptions.raise_compiler_error("Error executing SQL query: " ~ var_sql_stmt) }}
{% endif %}
    
--end macro
{% endmacro %}

模型代码 #

{{
    config(
    materialized='view'
    )
}}

--dynamic variables/ Edit only first 5 variables
{% set var_em_schema = 'ENTERPRISE_MODEL_HRC' %}
{% set var_em_table = 'MATERIALDIM' %}
{% set var_edw_schema = 'ENTERPRISE_MODEL_HRC' %}
{% set var_edw_table = '_EDW_UAT_TRDW_MATERIALDIM' %}
{% set var_natural_keys = 'MATERIAL' %}
{% set var_exclude_columns = 'MATERIAL_KEY' %}
--static variables
{% set var_always_exclude_columns = 'UPDATE_TIMESTAMP' %}
{% set var_sql_resuts_out = mac_em_edw_delta_attributes(var_em_schema,var_em_table,var_edw_schema,var_edw_table,var_exclude_columns) %}
{% set var_columns_list_em_out = var_sql_resuts_out.columns[0].values()[0] %}
{% set var_columns_list_edw_out = var_sql_resuts_out.columns[0].values()[1] %}
{% set var_columns_select_out = var_sql_resuts_out.columns[0].values()[2] %}
{% set var_columns_expr_out = var_sql_resuts_out.columns[0].values()[3] %}

--Final Select
--Goes for VIEW
SELECT
    {{var_columns_select_out}}
FROM {{var_em_schema}}.{{var_em_table}} em_table
JOIN {{var_edw_schema}}.{{var_edw_table}} edw_table
ON em_table.MATERIAL = edw_table.MATERIAL
WHERE {{mac_hkey_generator(var_columns_list_em_out)}} <> {{mac_hkey_generator(var_columns_list_edw_out)}}

报错信息 #

• 报错1：Error executing SQL query: <entire sql in macro>
• 报错2：Compilation Error in model _materialdim_em_edw_vw (models\final\_materialdim_em_edw_vw.sql) 'None' has no attribute 'table'. This can happen when calling a macro that does not exist.

报错原因及修复方案 #

针对报错1：SQL执行失败 #

该报错说明宏内的SQL语句本身存在问题，需从以下方面排查：

1. 检查CTE逻辑：确认columns_to_compare这个CTE是否正确获取了两张表需要对比的列，比如是否正确从INFORMATION_SCHEMA.COLUMNS筛选列，且排除了指定的exclude_columns_in和var_always_exclude_columns。
2. 验证Snowflake语法：检查LISTAGG函数的使用是否符合要求，若存在空值导致聚合失败，可添加IGNORE NULLS参数：LISTAGG(..., ',') WITHIN GROUP (...) IGNORE NULLS。
3. 权限验证：确认dbt使用的Snowflake角色拥有访问INFORMATION_SCHEMA及目标表的权限。
4. 直接测试SQL：将宏内的var_sql_stmt对应的SQL复制到Snowflake客户端执行，查看具体报错信息，快速定位语法或数据问题。

针对报错2：'None' has no attribute 'table' #

该报错源于两个问题：一是dbt编译阶段（execute=False）run_query不执行SQL，宏返回None；二是模型中访问查询结果的方式错误。修复方案如下：

1. 优化宏的返回逻辑

在非执行阶段返回模拟结果，避免返回None：

{% macro mac_em_edw_delta_attributes(em_schema_in,em_table_in,edw_schema_in,edw_table_in,exclude_columns_in) %}
--start macro
{% set var_sql_stmt %}
...<some complex sql cte statements>...
SELECT
    listagg('em_table.'||COLUMN_NAME,',') WITHIN GROUP (ORDER BY COLUMN_NAME) AS COLUMNS_LIST_EM,
    listagg('edw_table.'||COLUMN_NAME,',') WITHIN GROUP (ORDER BY COLUMN_NAME) AS COLUMNS_LIST_EDW,
    listagg('em_table.'||COLUMN_NAME||' As '||COLUMN_NAME||'_em, '||'edw_table.'||COLUMN_NAME||' As '||COLUMN_NAME||'_edw',',') WITHIN GROUP (ORDER BY COLUMN_NAME) AS COLUMNS_SELECT,
    listagg('em_table.'||COLUMN_NAME||'<>'||'edw_table.'||COLUMN_NAME,' OR ') WITHIN GROUP (ORDER BY COLUMN_NAME) AS COLUMNS_EXPR
FROM columns_to_compare 
{% endset %}

{% if execute %}
    {% set var_sql_result = run_query(var_sql_stmt) %}
    {% if var_sql_result is none or var_sql_result.rows|length == 0 %}
        {{ exceptions.raise_compiler_error("宏执行SQL未返回任何结果，SQL语句：" ~ var_sql_stmt) }}
    {% endif %}
    {{ return(var_sql_result) }}
{% else %}
    -- 编译阶段返回模拟结果，避免报错
    {% set mock_result = namespace(rows=[['mock_em_cols','mock_edw_cols','mock_select','mock_expr']]) %}
    {{ return(mock_result) }}
{% endif %}
    
--end macro
{% endmacro %}

2. 模型中正确访问结果

dbt的run_query返回的Result对象，行数据存储在rows属性中，需通过rows[0]获取单行数据，同时增加execute判断：

--static variables
{% set var_always_exclude_columns = 'UPDATE_TIMESTAMP' %}
{% set var_sql_resuts_out = mac_em_edw_delta_attributes(var_em_schema,var_em_table,var_edw_schema,var_edw_table,var_exclude_columns) %}

{% if execute %}
    {% set result_row = var_sql_resuts_out.rows[0] %}
    {% set var_columns_list_em_out = result_row[0] %}
    {% set var_columns_list_edw_out = result_row[1] %}
    {% set var_columns_select_out = result_row[2] %}
    {% set var_columns_expr_out = result_row[3] %}
{% else %}
    -- 编译阶段使用占位值，不影响编译
    {% set var_columns_list_em_out = 'placeholder_em' %}
    {% set var_columns_list_edw_out = 'placeholder_edw' %}
    {% set var_columns_select_out = 'placeholder_select' %}
    {% set var_columns_expr_out = 'placeholder_expr' %}
{% endif %}

3. 检查依赖宏

报错提示“调用不存在的宏”，需确认mac_hkey_generator宏已正确定义，且模型可访问（比如在同一个dbt包中，或已通过{% import %}导入）。

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