在Informatica Source Qualifier中处理软删除的多源表匹配需求

需求说明 #

需将目标表数据与多源系统的输入表对比实现软删除，核心约束：

• 不同源系统对应输入表数量不同：部分源系统数据分布在多张输入表，部分仅在单张输入表
• 输入表每日执行truncate and reload，无历史数据；目标表保留全量历史数据

核心规则 #

针对不同源系统的输入表分布，执行以下软删除判断：

• 场景1：同一源系统数据存在于全部三张输入表时，目标数据需在三张输入表中均存在，只要未出现在至少一张输入表，则标记软删除
• 场景2：同一源系统数据存在于两张输入表时，目标数据需在这两张输入表中均存在，只要未出现在至少一张输入表，则标记软删除
• 场景3：同一源系统数据仅存在于单张输入表时，目标数据需出现在该输入表中，否则标记软删除

示例数据 #

初始目标表（TARGET） #

ID    SOURCE_SYSTEM    DELETE_FLG
12345 AB               N            
98745 AB               N
08765 AB               N
69523 BA               N
34123 BA               N
78912 DF               N
11234 JK               N
2345  DK               Y （Y表示已软删除）

输入表集合 #

INPUT_TABLE1：
ID    SOURCE_SYSTEM    DELETE_FLG
12345 AB               N
34123 BA               N

INPUT_TABLE2：
ID    SOURCE_SYSTEM    DELETE_FLG
12345 AB               N
23451 DK               N

INPUT_TABLE3：
ID    SOURCE_SYSTEM    DELETE_FLG
78912 DF               N
11234 JK               N

期望目标表（更新后） #

ID    SOURCE_SYSTEM    DELETE_FLG
12345 AB               N
98745 AB               Y （需软删除）
08765 AB               Y （需软删除）
69523 BA               Y （需软删除）
34123 BA               N
78912 DF               N
11234 JK               N
23451 DK               N
2345  DK               Y （已软删除，保持不变）

Informatica源限定器SQL实现 #

以下SQL适用于多数关系型数据库（可根据实际数据库语法微调），直接在Source Qualifier中配置：

WITH source_system_tables AS (
    -- 收集每个源系统对应的输入表
    SELECT DISTINCT SOURCE_SYSTEM, 'INPUT1' AS TABLE_NAME FROM INPUT_TABLE1
    UNION ALL
    SELECT DISTINCT SOURCE_SYSTEM, 'INPUT2' AS TABLE_NAME FROM INPUT_TABLE2
    UNION ALL
    SELECT DISTINCT SOURCE_SYSTEM, 'INPUT3' AS TABLE_NAME FROM INPUT_TABLE3
),
source_table_count AS (
    -- 统计每个源系统需要匹配的输入表总数
    SELECT SOURCE_SYSTEM, COUNT(DISTINCT TABLE_NAME) AS REQUIRED_TABLES
    FROM source_system_tables
    GROUP BY SOURCE_SYSTEM
),
input_ids AS (
    -- 统计每个ID-源系统对实际出现在多少张输入表中
    SELECT ID, SOURCE_SYSTEM, COUNT(DISTINCT TABLE_NAME) AS PRESENT_TABLES
    FROM (
        SELECT ID, SOURCE_SYSTEM, 'INPUT1' AS TABLE_NAME FROM INPUT_TABLE1
        UNION ALL
        SELECT ID, SOURCE_SYSTEM, 'INPUT2' AS TABLE_NAME FROM INPUT_TABLE2
        UNION ALL
        SELECT ID, SOURCE_SYSTEM, 'INPUT3' AS TABLE_NAME FROM INPUT_TABLE3
    )
    GROUP BY ID, SOURCE_SYSTEM
)
-- 处理目标表现有数据的软删除标记
SELECT 
    t.ID,
    t.SOURCE_SYSTEM,
    CASE 
        WHEN t.DELETE_FLG = 'Y' THEN 'Y'
        WHEN i.PRESENT_TABLES = st.REQUIRED_TABLES THEN 'N'
        ELSE 'Y'
    END AS DELETE_FLG
FROM TARGET t
LEFT JOIN source_table_count st ON t.SOURCE_SYSTEM = st.SOURCE_SYSTEM
LEFT JOIN input_ids i ON t.ID = i.ID AND t.SOURCE_SYSTEM = i.SOURCE_SYSTEM
-- 补充输入表中新增的目标表无记录的数据
UNION ALL
SELECT 
    i.ID,
    i.SOURCE_SYSTEM,
    'N' AS DELETE_FLG
FROM input_ids i
LEFT JOIN TARGET t ON i.ID = t.ID AND i.SOURCE_SYSTEM = t.SOURCE_SYSTEM
WHERE t.ID IS NULL

逻辑说明 #

1. source_system_tables：识别每个源系统分布在哪些输入表中
2. source_table_count：计算每个源系统需要匹配的输入表数量，作为判断基准
3. input_ids：统计每个有效ID在对应源系统的输入表中出现的次数
4. 主查询：对目标表现有数据，根据是否满足“在所有对应输入表中存在”的条件更新软删除标记，已标记为Y的记录保持不变
5. UNION ALL部分：将输入表中新增的、目标表未存在的ID加入结果集，标记为未删除（N）

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