质量视角的主键 / 唯一键设计

难度与前置

难度：进阶
前置：做过表级质量规则或对账；遇到过「主键重复导致指标翻倍」。
读完能做什么：从质量与对账角度定义主键/唯一键，落地检测规则与变更纪律，并用 SQL 验收。

适用场景

ODS/DWD 表白名单主键，用于去重、幂等、对账锚点
多源融合后「业务唯一」与「技术唯一」不一致
质量平台配置 uniqueness / not-null 规则前的设计评审

不适用：

仅 OLTP 库内聚簇索引调优（DBA 范畴，本文偏仓内语义）
无业务含义的代理键生成算法细节（可另文）
NULL 语义全文——本文只强调主键禁止空

真实场景（一条）

订单 DWD 宣称 order_id 主键，实际含退款重下单复用号段 + 空串，日对账 GMV 比源高 1.8%。质量视角重定：业务主键 (order_id, order_version)；技术行键 order_sk；空串视为非法；唯一性日检 + 发布门禁。两周后重复率清零，对账稳定。

怎么做（步骤清单）

1. 三种「键」分开说

类型	含义	质量用途
业务主键	业务认定唯一	对账锚点、幂等
自然唯一键	多字段组合唯一	防重复加载
代理主键 / 行键	仓内生成	不替代业务对账

text

质量规则挂在「业务主键 / 唯一键」上，不要只挂代理键「永远唯一」自我安慰

2. 设计检查清单

[ ] 键字段在源系统的稳定性（会不会复用、改号）
[ ] 跨系统是否同一语义（订单号 vs 支付单号）
[ ] 是否允许 NULL/空串（质量视角：不允许）
[ ] 历史回刷时键是否仍可定位同一实体（见幂等回刷）
[ ] SCD/拉链场景：自然键 + 版本/时间（见拉链）

3. 表级契约写进 COMMENT / 元数据

sql

CREATE TABLE dwd_order_di (
  order_sk      BIGINT COMMENT '代理行键，仓内生成',
  order_id      STRING COMMENT '业务订单号，非空',
  order_version INT    COMMENT '订单版本，从 1 递增',
  gmv           DECIMAL(18,2),
  ...
)
TBLPROPERTIES (
  'pk.business'='order_id,order_version',
  'pk.quality.rule'='unique+not_null'
);

4. 质量规则模板

规则	SQL 思想	阈值
非空	`col IS NULL OR trim(col)=''`	= 0
唯一	`GROUP BY keys HAVING COUNT(*)>1`	= 0
稳定	与昨日同键属性突变率	< 约定
引用	事实键 ∈ 维表键 ∪ 未知码	违规 = 0

5. 重复怎么处置（写入侧）

sql

-- 装载前去重：保留最新
SELECT *
FROM (
  SELECT *, ROW_NUMBER() OVER (
    PARTITION BY order_id, order_version
    ORDER BY update_ts DESC, ingest_ts DESC
  ) AS rn
  FROM staging_order
) t
WHERE rn = 1;

与幂等写入一致：同一业务键重复导入不加倍。

6. 变更纪律

text

改主键 = 破坏性变更：
1. 新字段并行、双写
2. 历史回填与对账
3. 下游切换
4. 旧键废弃公告
禁止静默「今天起 order_id 改含义」

7. 与闭环集成

唯一性失败 → 告警认领 → 修源或修装载 → 复验（质量闭环）；P0 可阻断 ADS 发布（ADS 验收）。

常见坑（至少牢记这些）

把「差不多唯一」当主键
99.9% 唯一的 0.1% 足以毁掉 SUM。
主键允许 NULL
SQL 唯一约束与 GROUP BY 对多 NULL 行为易误导；质量上直接判失败。
只用代理键做对账
源与仓对不上，发现不了业务重复。
合表后键空间冲突
多源 order_id 撞号，未加 source_sys 前缀。
拉链只按自然键不按版本
同日多变丢失，唯一性「通过」但历史错。
去重规则与主键定义不一致
质量按 A 查重，作业按 B 去重，规则永远红/绿抖动。

如何验收（可量化）

[ ] 元数据标明 business PK
[ ] 日检：非空率 100%，重复组数 0
[ ] 与源系统抽样：键集合对齐（差集可解释）
[ ] 故意注入重复行：规则失败且阻断或告警
[ ] 主键变更有双跑对账记录

sql

-- 1) 非空
SELECT
  SUM(CASE WHEN order_id IS NULL OR trim(order_id)='' THEN 1 ELSE 0 END) AS bad_id,
  SUM(CASE WHEN order_version IS NULL THEN 1 ELSE 0 END) AS bad_ver
FROM dwd_order_di
WHERE dt='${biz_date}';
-- 期望全 0

sql

-- 2) 唯一
SELECT order_id, order_version, COUNT(*) AS cnt
FROM dwd_order_di
WHERE dt='${biz_date}'
GROUP BY order_id, order_version
HAVING COUNT(*) > 1
LIMIT 100;
-- 期望 0 行

sql

-- 3) 与源差集（示意）
SELECT s.order_id
FROM ods_order_di s
LEFT JOIN dwd_order_di d
  ON s.order_id=d.order_id AND s.order_version=d.order_version
 AND d.dt='${biz_date}'
WHERE s.dt='${biz_date}' AND d.order_id IS NULL
LIMIT 100;

bash

# 质量平台规则导出验收
echo "table,rule,violations" >> pk_qa.csv
echo "dwd_order_di,unique_order_ver,0" >> pk_qa.csv

质量视角的主键 / 唯一键设计 ​

适用场景 ​

真实场景（一条） ​

怎么做（步骤清单） ​

1. 三种「键」分开说 ​

2. 设计检查清单 ​

3. 表级契约写进 COMMENT / 元数据 ​

4. 质量规则模板 ​

5. 重复怎么处置（写入侧） ​

6. 变更纪律 ​

7. 与闭环集成 ​

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

如何验收（可量化） ​

相关面试题 ​