Azure Data Factory复制数据Upsert操作产生重复条目问题

背景 #

我有一个数据管道，负责将Google Analytics 4近7天的数据加载至落地表lnd_ga4.dateScreenPageViews_Page，再复制到数据仓库表stg0_ga4.dateScreenPageViews_Page，要求避免重复条目。

源表结构 #

CREATE TABLE [lnd_ga4].[dateScreenPageViews_Page](
    [sk_id] [int] IDENTITY(1,1) NOT NULL,
    [taxonomie_id] [bigint] NULL,
    [dim_date] [varchar](512) NULL,
    [dim_fullPageUrl] [varchar](512) NULL,
    [dim_pagePath] [varchar](512) NULL,
    [dim_articleId] [varchar](512) NULL,
    [dim_articleType] [varchar](512) NULL,
    [dim_pageReferrer] [varchar](512) NULL,
    [dim_pageTitle] [varchar](512) NULL,
    [dim_sessionSource] [varchar](512) NULL,
    [dim_type] [char](2) NULL,
    [engagedSessions] [bigint] NULL,
    [screenPageViews] [bigint] NULL,
    [last_updated] [datetime] NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [lnd_ga4].[dateScreenPageViews_Page] ADD  DEFAULT (getdate()) FOR [last_updated]
GO

目标表结构 #

CREATE TABLE [stg0_ga4].[dateScreenPageViews_Page](
    [sk_id] [int] IDENTITY(1,1) NOT NULL,
    [taxonomie_id] [bigint] NULL,
    [dim_date] [varchar](512) NULL,
    [dim_fullPageUrl] [varchar](512) NULL,
    [dim_pagePath] [varchar](512) NULL,
    [dim_articleId] [varchar](512) NULL,
    [dim_articleType] [varchar](512) NULL,
    [dim_pageReferrer] [varchar](512) NULL,
    [dim_pageTitle] [varchar](512) NULL,
    [dim_sessionSource] [varchar](512) NULL,
    [dim_type] [char](2) NULL,
    [engagedSessions] [bigint] NULL,
    [screenPageViews] [bigint] NULL,
    [last_updated] [datetime] NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [stg0_ga4].[dateScreenPageViews_Page] ADD  DEFAULT (getdate()) FOR [last_updated]
GO

复制数据活动SQL查询 #

SELECT 
  CAST(
    dbo.GA4_getTaxonomieID(
      CONCAT('www.xyz.com', dim_pagePath)
    ) AS bigint
  ) AS taxonomie_id, 
  TRIM(dim_date) as dim_date, 
  TRIM(
    CONCAT('www.xyz.com', dim_pagePath)
  ) AS dim_fullPageUrl, 
  TRIM(dim_pagePath) as dim_pagePath, 
  TRIM(
    dbo.GA4_getArticleID(
      CONCAT('www.xyz.com', dim_pagePath)
    )
  ) AS dim_articleId, 
  TRIM(
    dbo.GA4_getArticleType(
      CONCAT('www.xyz.com', dim_pagePath)
    )
  ) AS dim_articleType, 
  TRIM(dim_pageReferrer) AS dim_pageReferrer, 
  TRIM(dim_pageTitle) AS dim_pageTitle, 
  TRIM(dim_sessionSource) AS dim_sessionSource, 
  TRIM(
    dbo.GA4_getType(
      CONCAT('www.xyz.com', dim_pagePath)
    )
  ) AS dim_type, 
  engagedSessions, 
  screenPageViews
FROM 
  lnd_ga4.dateScreenPageViews_Page 
ORDER BY 
  sk_id DESC

ADF配置细节 #

• 源端、目标端配置已完成
• 键列表达式：@json(replace(string(pipeline().parameters.keys),'\',''))
• 键列参数数组：["taxonomie_id","dim_date","dim_fullPageUrl","dim_pagePath","dim_articleId","dim_articleType","dim_pageReferrer","dim_pageTitle","dim_sessionSource","dim_type"]

当前问题 #

首次运行后目标表写入10条数据，重复运行管道（无任何修改）时，目标表条目数量翻倍。简单数据集上测试键列配置有效，手动配置映射后问题依旧。

排查与解决方案 #

1. 检查键列的NULL值问题 #

ADF的重复匹配逻辑中，NULL会被视为不相等的值，导致重复插入。运行以下查询检查目标表键列的NULL情况：

SELECT 
  COUNT(*) AS null_count,
  CASE 
    WHEN taxonomie_id IS NULL THEN 'taxonomie_id'
    WHEN dim_date IS NULL THEN 'dim_date'
    WHEN dim_fullPageUrl IS NULL THEN 'dim_fullPageUrl'
    WHEN dim_pagePath IS NULL THEN 'dim_pagePath'
    WHEN dim_articleId IS NULL THEN 'dim_articleId'
    WHEN dim_articleType IS NULL THEN 'dim_articleType'
    WHEN dim_pageReferrer IS NULL THEN 'dim_pageReferrer'
    WHEN dim_pageTitle IS NULL THEN 'dim_pageTitle'
    WHEN dim_sessionSource IS NULL THEN 'dim_sessionSource'
    WHEN dim_type IS NULL THEN 'dim_type'
  END AS null_column
FROM stg0_ga4.dateScreenPageViews_Page
GROUP BY 
  CASE 
    WHEN taxonomie_id IS NULL THEN 'taxonomie_id'
    WHEN dim_date IS NULL THEN 'dim_date'
    WHEN dim_fullPageUrl IS NULL THEN 'dim_fullPageUrl'
    WHEN dim_pagePath IS NULL THEN 'dim_pagePath'
    WHEN dim_articleId IS NULL THEN 'dim_articleId'
    WHEN dim_articleType IS NULL THEN 'dim_articleType'
    WHEN dim_pageReferrer IS NULL THEN 'dim_pageReferrer'
    WHEN dim_pageTitle IS NULL THEN 'dim_pageTitle'
    WHEN dim_sessionSource IS NULL THEN 'dim_sessionSource'
    WHEN dim_type IS NULL THEN 'dim_type'
  END
HAVING COUNT(*) > 0

• 若存在NULL值，需在源查询中用COALESCE替换为默认值，或调整键列选择（排除可能为NULL的列）。

2. 验证键列表达式的实际输出 #

键列表达式可能存在转换异常，需确认实际传递给ADF的键列数组是否正确：

• 开启ADF调试模式，查看pipeline().parameters.keys的实际值，确认转义符替换后是否生成了正确的JSON数组；
• 临时将键列数组硬编码到目标配置中（直接填写["taxonomie_id","dim_date",...]），排除参数传递问题。

3. 确认复制活动的写入行为 #

ADF默认写入行为是Append，会直接追加数据，必须设置为Upsert或Merge才能实现去重：

• 进入复制活动的目标配置，找到"写入行为"选项：
  • 选择"Upsert"，确保已正确指定键列；
  • 若选择"Merge"，需为目标表的键列创建唯一约束（否则ADF无法识别重复项）。

4. 检查源表的重复数据 #

如果源表本身存在重复的键列组合，复制活动会将这些重复项写入目标表：

SELECT 
  taxonomie_id, dim_date, dim_fullPageUrl, dim_pagePath, dim_articleId,
  dim_articleType, dim_pageReferrer, dim_pageTitle, dim_sessionSource, dim_type,
  COUNT(*) AS duplicate_count
FROM lnd_ga4.dateScreenPageViews_Page
GROUP BY 
  taxonomie_id, dim_date, dim_fullPageUrl, dim_pagePath, dim_articleId,
  dim_articleType, dim_pageReferrer, dim_pageTitle, dim_sessionSource, dim_type
HAVING COUNT(*) > 1

• 若源表存在重复，需在复制前去重，比如在源查询中添加DISTINCT，或用窗口函数筛选唯一行：

SELECT * FROM (
  SELECT 
    -- 源查询所有字段
    ROW_NUMBER() OVER (PARTITION BY taxonomie_id, dim_date, dim_fullPageUrl, dim_pagePath, dim_articleId,
                      dim_articleType, dim_pageReferrer, dim_pageTitle, dim_sessionSource, dim_type 
                      ORDER BY sk_id DESC) AS rn
  FROM lnd_ga4.dateScreenPageViews_Page
) t WHERE rn = 1

5. 为目标表创建唯一约束（Merge模式必备） #

如果使用Merge写入行为，目标表必须为键列创建唯一约束，否则ADF无法匹配重复项：

ALTER TABLE stg0_ga4.dateScreenPageViews_Page
ADD CONSTRAINT UC_dateScreenPageViews_Page_Keys
UNIQUE (taxonomie_id, dim_date, dim_fullPageUrl, dim_pagePath, dim_articleId,
        dim_articleType, dim_pageReferrer, dim_pageTitle, dim_sessionSource, dim_type)

注意：SQL Server的唯一约束允许多个NULL行，因此需先处理键列的NULL问题。

6. 验证TRIM操作导致的匹配差异 #

源查询中对多个字段使用了TRIM，如果目标表中已存在未被TRIM的数据，会导致键列不匹配：

-- 检查源查询输出与目标表数据的空格差异
SELECT src.dim_date, tgt.dim_date, src.dim_fullPageUrl, tgt.dim_fullPageUrl
FROM (
  SELECT 
    TRIM(dim_date) as dim_date, 
    TRIM(CONCAT('www.xyz.com', dim_pagePath)) AS dim_fullPageUrl
  FROM lnd_ga4.dateScreenPageViews_Page
) src
JOIN stg0_ga4.dateScreenPageViews_Page tgt
ON src.dim_date = tgt.dim_date AND src.dim_fullPageUrl = tgt.dim_fullPageUrl
WHERE LEN(src.dim_date) <> LEN(tgt.dim_date) OR LEN(src.dim_fullPageUrl) <> LEN(tgt.dim_fullPageUrl)

• 若存在差异，需确保目标表数据统一经过TRIM处理，或在写入时同步处理。

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