两个系统的用户ID对不上——数据一致性问题怎么解？

-- 同源对账：ODS表 vs 源数据库
-- 方法1：总行数对比
-- MySQL端
SELECT COUNT(*) FROM production.orders WHERE DATE(created_at) = '2024-01-15';
-- 结果：50000
 
-- ODS端
SELECT COUNT(*) FROM ods_orders WHERE ds = '2024-01-15';
-- 结果：49800（少了200条）
 
-- 方法2：关键指标对比
-- MySQL端
SELECT SUM(amount) FROM production.orders WHERE DATE(created_at) = '2024-01-15';
-- 结果：2500000.00
 
-- ODS端
SELECT SUM(amount) FROM ods_orders WHERE ds = '2024-01-15';
-- 结果：2497500.00（差了2500，正好是200条的平均值）

-- 广告归因数据对账SQL
-- 将Facebook数据和自有埋点数据按统一口径对比
SELECT
  a.ds,
  a.campaign_id,
  a.fb_installs,        -- Facebook报告的安装数
  b.our_installs,       -- 我们统计的安装数
  a.fb_installs - b.our_installs AS diff,
  ROUND((a.fb_installs - b.our_installs) * 100.0 / a.fb_installs, 2) AS diff_pct
FROM (
  SELECT ds, campaign_id, SUM(installs) AS fb_installs
  FROM ods_facebook_ads_report
  WHERE ds = '${bizdate}'
  GROUP BY ds, campaign_id
) a
LEFT JOIN (
  SELECT ds, campaign_id, COUNT(DISTINCT device_id) AS our_installs
  FROM dwd_app_install
  WHERE ds BETWEEN DATE_SUB('${bizdate}', 7) AND '${bizdate}'  -- 7天归因窗口
  GROUP BY ds, campaign_id
) b ON a.ds = b.ds AND a.campaign_id = b.campaign_id;
-- 关注diff_pct > 20%的campaign，需要重点排查

跨系统对账框架

处理跨系统一致性问题，我总结了一套标准化的对账框架：

Step 1：统一口径。两个系统对比之前，先确保口径一致——时间范围、去重逻辑、过滤条件全部对齐。80%的差异来自口径不同。

Step 2：选定基准。确定哪个系统的数据作为「准数」。一般原则是：离用户最近的系统最准。比如收入以支付系统为准，用户行为以自有埋点为准。

Step 3：分维度拆解。不要只看总数的差异，要按维度（日期、渠道、国家等）拆开看，找出差异集中在哪个维度。

Step 4：设置容忍度。跨系统的数据不可能100%一致。要和业务方约定一个可接受的差异范围（比如±5%），在范围内的不算问题。

# 自动化对账脚本框架
def run_reconciliation(ds):
    # 1. 获取两个系统的数据
    source_a = query_source_a(ds)  # 如Facebook数据
    source_b = query_source_b(ds)  # 如自有埋点数据
 
    # 2. 按维度JOIN对比
    merged = source_a.merge(source_b, on=['ds', 'campaign_id'], how='outer')
    merged['diff_pct'] = abs(merged['a_value'] - merged['b_value']) / merged['a_value'] * 100
 
    # 3. 标记异常（超过容忍度5%的）
    anomalies = merged[merged['diff_pct'] > 5]
 
    # 4. 生成对账报告
    if len(anomalies) > 0:
        send_alert(anomalies, level='WARNING',
            msg=f'{ds} 有{len(anomalies)}个维度差异超过5%')
 
    # 5. 记录历史（用于趋势分析）
    save_to_meta(merged, table='meta_reconciliation_log')

ID打通：跨系统一致性的基础

很多跨系统一致性问题的根源是ID不统一。同一个用户，在广告系统里是device_id，在App里是user_id，在支付系统里是pay_account_id。要做跨系统对账，首先要做ID打通。

-- 用户ID映射表：打通多个系统的ID
CREATE TABLE IF NOT EXISTS dim_user_id_mapping (
  master_user_id STRING COMMENT '主用户ID（以App注册ID为准）',
  device_id STRING COMMENT '设备ID（广告归因用）',
  pay_account_id STRING COMMENT '支付账户ID',
  email STRING COMMENT '邮箱（部分系统用邮箱识别）',
  first_seen_date DATE COMMENT '首次出现日期',
  last_update_time TIMESTAMP
);
 
-- ID打通的核心逻辑：
-- 1. App内注册时，同时记录device_id和user_id的关联
-- 2. 支付成功时，记录user_id和pay_account_id的关联
-- 3. 通过user_id作为桥接，打通所有ID
-- 4. 一个master_user_id可能对应多个device_id（换设备的用户）

面试高频追问

追问一：跨系统数据差5%以内算正常吗？

取决于业务场景。广告投放数据差5%以内通常可以接受——因为广告平台和自有系统的采集链路完全不同，有天然的误差。但财务数据差5%就不行了——收入数据必须和支付系统对齐到元级别。我们的标准是：广告数据容忍度±10%，收入数据容忍度±0.1%，用户行为数据容忍度±5%。

追问二：对账发现差异后怎么处理？

分情况。如果是口径差异（比如时区不一致），修改口径对齐即可。如果是数据丢失（比如埋点没上报），需要做数据回补。如果是技术差异（比如去重逻辑不同），要和业务方确认用哪种逻辑。关键是：每次发现差异都要记录根因，形成一个「差异原因库」，后续再遇到类似问题可以快速定位。

追问三：实时数据和离线数据不一致怎么办？

这是一个很常见的问题。实时数据和离线数据因为计算时机不同，天然会有差异。我的做法是：实时数据标注「实时预估」，离线数据标注「最终值」。每天离线数据产出后，自动覆盖实时数据。业务方理解这个差异后，就不会每天来问「为什么今天的数字和昨天看到的不一样」了。

实战踩坑

我在某短剧出海公司做广告归因对账时，踩过一个时区相关的坑。我们用UTC时间做ds分区，Facebook用的是广告账户设置的时区（美东时间EST，UTC-5）。这导致Facebook显示的「1月15日」数据实际上跨了UTC的1月15日和1月16日两天。

我最初直接用ds='2024-01-15'去对比两边的数据，差异一直在15-20%左右，怎么都对不上。后来意识到时区问题后，把Facebook的数据按UTC重新分区，差异一下降到了3%以内。

-- 时区对齐：把Facebook数据从EST转换为UTC
SELECT
  -- Facebook原始时间是EST，需要+5小时转成UTC
  DATE(CONVERT_TIMEZONE('America/New_York', 'UTC', fb_datetime)) AS ds_utc,
  campaign_id,
  SUM(installs) AS installs,
  SUM(spend) AS spend
FROM ods_facebook_ads_report
WHERE DATE(fb_datetime) IN ('2024-01-15', '2024-01-16')  -- 多取一天确保完整
GROUP BY ds_utc, campaign_id;
 
-- 对比时两边都用UTC分区
-- Facebook (UTC): ds_utc = '2024-01-15'
-- 自有埋点 (UTC): ds = '2024-01-15'
-- 差异从20%降到3%以内

解决方案：建立一个统一的时区转换层——所有外部数据源在进入ODS时都转换为UTC。同时在元数据中标注每个外部数据源的原始时区，方便后续对账时参考。这个做法后来成了我们团队处理出海数据的标准规范。

总结

数据一致性问题分三个层面：同源、跨层、跨系统。跨系统一致性最复杂，核心方法是「统一口径→选定基准→分维度对账→设置容忍度」。面试时讲清楚一个「广告数据对不上」的真实案例，从发现问题到拆解差异来源到最终解决，就很有深度。记住：跨系统不追求100%一致，而是差异可解释、可接受。

下一篇聊ETL中最常见的准确性问题：ETL跑完了但数据是错的——数据准确性校验怎么做？

你们做过跨系统的数据对账吗？哪些系统的数据最难对齐？评论区聊聊～

关注「Wood聊大数据」| 9年大数据老兵，聊技术、聊职场、聊成长

数据治理 面试通关手册

第11-15篇：准确性校验、埋点方案、组织推动、维度质量、血缘实践

Wood聊大数据

9年大数据老兵，聊技术、聊职场、聊成长