如何获取dbt测试中未通过用例的失败数据行？

方法一：利用dbt_expectations内置的失败记录存储功能 #

• 对于expect_column_values_to_be_between、expect_column_values_to_not_be_null这类常见测试，可在测试配置中添加store_failures: true参数，让失败记录自动写入Snowflake的指定表中。
  示例配置：

  models:
    - name: your_target_model
      tests:
        - dbt_expectations.expect_column_values_to_not_be_null:
            column: user_id
            store_failures: true
            schema: dbt_test_failures
  

• 配置完成后，失败记录会存入dbt_test_failures.expect_column_values_to_not_be_null_your_target_model_user_id这类命名的表中，直接查询该表就能获取具体失败数据。

方法二：基于Elementary测试结果反向关联排查 #

Elementary已记录测试的node_id、test_name等核心信息，可通过这些字段关联原模型表，编写自定义SQL定位问题：

1. 先从Elementary的测试结果表（如elementary.test_results）筛选出失败记录，提取关键信息：

   SELECT
     node_id,
     test_name,
     database_name,
     schema_name
   FROM elementary.test_results
   WHERE status = 'fail'
   

2. 解析node_id得到模型表名（格式通常为model.<项目名>.<schema>.<表名>），结合测试类型编写排查SQL：
   • 若测试为expect_column_values_to_not_be_null：

     SELECT *
     FROM your_database.your_schema.target_table
     WHERE target_column IS NULL
     

   • 若测试为expect_column_values_to_be_in_set：

     SELECT *
     FROM your_database.your_schema.target_table
     WHERE target_column NOT IN ('valid_val1', 'valid_val2')
     

方法三：自定义dbt宏自动生成排查SQL #

编写自定义宏，接收Elementary的失败测试记录，自动生成对应排查语句，减少重复工作：
示例宏代码：

{% macro generate_failure_query(test_record) %}
  {% set db = test_record.database_name %}
  {% set schema = test_record.schema_name %}
  {% set table = test_record.node_id.split('.')[-1] %}
  {% set test_type = test_record.test_name %}

  {% if test_type == 'expect_column_values_to_not_be_null' %}
    {% set col = test_record.test_params.column %}
    SELECT * FROM {{ db }}.{{ schema }}.{{ table }} WHERE {{ col }} IS NULL;
  {% elif test_type == 'expect_column_values_to_be_between' %}
    {% set col = test_record.test_params.column %}
    {% set min_val = test_record.test_params.min_value %}
    {% set max_val = test_record.test_params.max_value %}
    SELECT * FROM {{ db }}.{{ schema }}.{{ table }} WHERE {{ col }} < {{ min_val }} OR {{ col }} > {{ max_val }};
  {% endif %}
{% endmacro %}

在dbt中调用该宏，传入单条失败测试记录，即可直接得到定位失败数据的SQL语句。

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