变更检测是什么
变更检测决定拉链维表里哪些成员需要开新版本，主流做法是两条：hashdiff 用整行拼接哈希快速判定「是否变更」，逐列比较能精确定位「改了哪一列」。前者快但不知道变在哪，后者明确但列多时写法冗长。组合使用效果最好：先按哈希筛出真正变化的成员，再对这批做逐列定位，既省扫描又能产出变更明细供审计使用。
基本信息
| hashdiff | 整行指纹，判定是否变更，快 |
|---|---|
| 逐列比较 | 定位改了哪列，慢但结果可读 |
| 推荐组合 | 先哈希预筛，再逐列定位变更列 |
| 空值处理 | 拼接时用 coalesce 统一占位，比较时用空值安全等号 |
| 前置条件 | 两端的字段顺序、分隔符与空值占位完全一致 |
典型使用场景
- 增量拉链：只对哈希变化的成员开展开新版本的处理
- 变更归因：向业务方解释这次变的是名称还是等级
- 数据质量排查：同一批里变更成员比例异常升高，提示上游口径变动
- 审计留痕：变更明细表记录改前值、改后值与批次
- 多列宽表：属性几十列时逐列写比较条件不现实，先用哈希收缩范围
- 回归验证：改造前后各跑一次判定，确认变更集合完全一致
哈希预筛加逐列定位
哈希预筛加逐列定位
-- 第 1 步：哈希快速筛出变更成员
WITH src AS (
SELECT customer_id, name, phone,
md5(concat_ws('|', coalesce(name,'#'),
coalesce(phone,'#'))) AS row_hash
FROM stg_customer),
chg AS (
SELECT s.customer_id
FROM src s
JOIN dim_customer d
ON s.customer_id = d.customer_id AND d.is_current = 1
WHERE s.row_hash <> d.row_hash)
-- 第 2 步：只对这一批逐列定位改了哪列
SELECT d.customer_id,
CASE WHEN NOT (d.name <=> s.name) THEN 'name'
WHEN NOT (d.phone <=> s.phone) THEN 'phone' END AS changed_col,
d.name AS old_name, s.name AS new_name
FROM dim_customer d
JOIN stg_customer s
ON d.customer_id = s.customer_id AND d.is_current = 1
WHERE d.customer_id IN (SELECT customer_id FROM chg);两种检测方式对照
| 对比项 | hashdiff | 逐列比较 |
|---|---|---|
| 速度 | 整行一次计算，快 | 列多时条件冗长且慢 |
| 结果信息 | 只知道变没变 | 知道改了哪几列 |
| 维护成本 | 加列要同步改哈希口径 | 加列要同步加比较条件 |
| 空值处理 | 需 coalesce 占位 | 需空值安全等号 |
| 适用场景 | 大批量预筛、日常增量 | 变更明细、审计与排查 |
拼接时用 coalesce 把空值替换成固定标记，比较时用空值安全等号（如 MySQL 的 <=>、PostgreSQL 的 IS NOT DISTINCT FROM），是两条必须同时满足的规则。任一端没做处理，正常记录会被反复误判为变更，拉链版本数上涨而没人察觉。