如何基于两个现有dbt测试模型创建新的整合模型

假设你需要的目标schema包含以下核心字段：

• article_number: 商品编号
• article_family: 商品品类
• overall_test_status: 整体测试状态（PASS/FAIL）
• failed_rules: 所有触发失败的业务规则（多规则用逗号分隔）

以下是对应的dbt模型SQL代码：

WITH combined_tests AS (
    -- 关联两个测试模型，确保不遗漏任何商品编号
    SELECT
        COALESCE(f.article_number, n.article_number) AS article_number,
        f.article_family,
        -- 合并同一个商品的所有失败规则
        STRING_AGG(DISTINCT COALESCE(f.business_rule, n.business_rule), ', ') AS failed_rules
    FROM {{ ref('tbstg_test_article_family') }} AS f
    FULL OUTER JOIN {{ ref('tbstg_test_article_number') }} AS n
        ON f.article_number = n.article_number
    GROUP BY article_number, article_family
)

SELECT
    article_number,
    article_family,
    -- 根据是否存在失败规则判断整体状态
    CASE WHEN failed_rules IS NOT NULL THEN 'FAIL' ELSE 'PASS' END AS overall_test_status,
    failed_rules
FROM combined_tests

代码逻辑说明 #

• 使用FULL OUTER JOIN确保不会遗漏仅在单个测试模型中出现的商品编号
• STRING_AGG用于聚合同一个商品的所有失败规则，便于统一查看问题
• COALESCE处理其中一个模型缺失字段的情况
• 整体测试状态由是否存在失败规则直接判定

如果你的目标schema有不同需求，可以按以下方式调整：

• 若需要保留两个测试的单独状态：在查询中添加f.test_status AS family_test_status、n.test_status AS number_test_status字段
• 若需要每条规则对应一条记录：去掉GROUP BY和STRING_AGG，改用UNION ALL整合两个模型的结果，示例代码如下：

SELECT
    article_number,
    article_family,
    test_status,
    business_rule,
    'article_family_check' AS test_type
FROM {{ ref('tbstg_test_article_family') }}

UNION ALL

SELECT
    article_number,
    NULL AS article_family,
    test_status,
    business_rule,
    'article_number_check' AS test_type
FROM {{ ref('tbstg_test_article_number') }}

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