对账（源 vs 仓 / 层间）怎么设计

难度与前置

难度：中级
前置：能说清「一行含义」与金额口径；会写分组汇总 SQL。
读完能做什么：设计源↔仓、层间两套对账，带阈值与原因码，并挂到调度出门禁。

适用场景

渠道/业务库金额与仓内 DWD/DWS 不一致，需要常态化对账
分层落地后要证明 DWD→DWS→ADS 没有「静默丢数/翻倍」
审计或业务要求每日差额可解释

不适用：

无共同粒度、无法对齐主键的「感觉不对」（先定契约）
实时秒级与离线 T+1 强行逐笔相等（应定义延迟窗口与对账时刻）
口径未统一就对账（先统一 cost_amount 定义，见分层文）

真实场景（一条）

投放：渠道结算文件 vs dwd_ad_spend_detail_di vs dws_ad_channel_play_1d。

曾出现：

源仓差 1.2%：一半是时区切日，一半是 DWD 过滤测试渠道未建原因码
层间差：DWS 重跑用了旧过滤条件，ADS 日报与对账 ADS 各说各话

目标：每日三方对账表落地，差额进原因码；P0 超阈值阻断 ADS 对外发布。

怎么做（步骤清单）

1. 先定对账契约（一页纸）

项	必须写清
双方对象	如：渠道文件 vs DWD；DWD vs DWS
时间口径	入账业务日 / 事件日；截止水位（如 T+1 10:00）
粒度	日 × 渠道；或日 × 渠道 × 结算主体
指标	`cost_amount` 不含税本币；币种兑换规则
过滤	是否含测试、赠款、作废单
阈值	绝对额 + 比率；分级 P0/P1
原因码	迟到、过滤、汇率、重复、未知

没有契约就开对账，只会产出「差一堆」工单。

2. 源 vs 仓：落地中间汇总再比

不要每次用原始亿级明细 Join。先汇到对账粒度：

sql

-- 源侧日汇总（示意）
CREATE TABLE IF NOT EXISTS mid_recon_src_channel_1d (
  channel_id STRING,
  cost_src DECIMAL(18,4),
  row_cnt_src BIGINT
) PARTITIONED BY (dt STRING);

INSERT OVERWRITE TABLE mid_recon_src_channel_1d PARTITION (dt='${biz_date}')
SELECT channel_id,
       SUM(cost_amount) AS cost_src,
       COUNT(*) AS row_cnt_src
FROM staging_channel_bill_file
WHERE dt = '${biz_date}'
GROUP BY channel_id;

sql

-- 仓侧同粒度
INSERT OVERWRITE TABLE mid_recon_dwd_channel_1d PARTITION (dt='${biz_date}')
SELECT channel_id,
       SUM(cost_amount) AS cost_dwd,
       COUNT(*) AS row_cnt_dwd
FROM dwd_ad_spend_detail_di
WHERE dt = '${biz_date}' AND is_valid = true
GROUP BY channel_id;

sql

-- 对账结果
INSERT OVERWRITE TABLE ads_recon_channel_1d PARTITION (dt='${biz_date}')
SELECT
  COALESCE(s.channel_id, d.channel_id) AS channel_id,
  s.cost_src,
  d.cost_dwd,
  COALESCE(d.cost_dwd, 0) - COALESCE(s.cost_src, 0) AS diff_cost,
  CASE
    WHEN s.channel_id IS NULL THEN 'SRC_MISSING'
    WHEN d.channel_id IS NULL THEN 'DWD_MISSING'
    WHEN ABS(COALESCE(d.cost_dwd,0)-COALESCE(s.cost_src,0)) <= 0.01 THEN 'MATCH'
    ELSE 'DIFF_PENDING'
  END AS reason_code
FROM mid_recon_src_channel_1d s
FULL OUTER JOIN mid_recon_dwd_channel_1d d
  ON s.channel_id = d.channel_id AND s.dt = d.dt
WHERE s.dt = '${biz_date}' OR d.dt = '${biz_date}';

3. 层间对账：同口径、同过滤

sql

-- DWD vs DWS（过滤条件必须与 DWS 定义一致）
SELECT
  d.dt,
  d.cost_dwd,
  w.cost_dws,
  d.cost_dwd - w.cost_dws AS diff
FROM (
  SELECT dt, SUM(cost_amount) AS cost_dwd
  FROM dwd_ad_spend_detail_di
  WHERE dt = '${biz_date}' AND is_valid = true
  GROUP BY dt
) d
JOIN (
  SELECT dt, SUM(cost_amount) AS cost_dws
  FROM dws_ad_channel_play_1d
  WHERE dt = '${biz_date}'
  GROUP BY dt
) w ON d.dt = w.dt;

层间差值只允许：约定舍入、合法过滤（且过滤有原因字段可汇总解释）。

4. 原因码闭环（禁止长期「未知」）

原因码	含义	动作
`LATE_ARRIVAL`	源迟到，下日补	补数单；不进 P0 永久失败
`FILTER_TEST`	测试渠道过滤	用过滤金额解释差额
`TZ_CUT`	时区切日差	修正分区口径或容忍带说明
`DUP_KEY`	重复入账	P0；修幂等
`UNKNOWN`	未归类	必须有 owner 与到期日

sql

-- 未知原因占比监控
SELECT dt,
       SUM(CASE WHEN reason_code = 'UNKNOWN' THEN 1 ELSE 0 END) AS unk_cnt,
       COUNT(*) AS all_cnt
FROM ads_recon_channel_1d
WHERE dt = '${biz_date}'
GROUP BY dt;

5. 阈值与调度门禁

text

P0: |diff_ratio| > 0.5% 或 |diff_cost| > 10000（示例）→ 阻断 ADS 发布
P1: 超软阈值 → 告警，可发布但贴「待复核」

依赖边：ads_recon_channel_1d PASS → 允许 ads_ad_daily_report_1d 对外。参见调度依赖怎么设计。

常见坑（至少牢记这些）

粒度不对齐就 Join
文件按结算主体、仓按渠道，差额无意义。先汇同维。
一边含税一边不含税仍叫对账
先统一口径字段，再比数字。
差额无原因码
每天人工翻明细，不可规模化。强制原因码 + 未知清零目标。
实时与离线强行逐笔相等
忽略水位。应对「对账时刻」与延迟桶。
对账通过但层间未比
源仓对上了，DWS 自己算错。源仓 + 层间至少两道。

如何验收（可量化）

[ ] 对账契约文档齐全（粒度/口径/阈值/原因码）
[ ] 每日 ads_recon_* 落地；P0 超阈值阻断下游
[ ] UNKNOWN 占比有周趋势，且有清零责任人
[ ] 层间 diff 在舍入误差内或可按过滤原因解释
[ ] 抽一日：人工抽 3 个 DIFF 渠道能追溯到明细

sql

-- P0 门禁查询
SELECT COUNT(*) AS p0_breach
FROM ads_recon_channel_1d
WHERE dt = '${biz_date}'
  AND reason_code NOT IN ('MATCH', 'FILTER_TEST', 'LATE_ARRIVAL')
  AND ABS(diff_cost) > 10000;
-- 期望：0；否则调度失败

bash

# 调度：recon 任务失败时下游 ADS publish 未触发
# 人工注入一笔 DIFF 后验证门禁

对账（源 vs 仓 / 层间）怎么设计 ​

适用场景 ​

真实场景（一条） ​

怎么做（步骤清单） ​

1. 先定对账契约（一页纸） ​

2. 源 vs 仓：落地中间汇总再比 ​

3. 层间对账：同口径、同过滤 ​

4. 原因码闭环（禁止长期「未知」） ​

5. 阈值与调度门禁 ​

常见坑（至少牢记这些） ​

如何验收（可量化） ​

相关面试题 ​