| 步骤 | 识别变更 / 关旧 / 插新 |
|---|---|
| 水位 | last_load_ts 过滤增量源 |
| 幂等 | 按批次记录版本来源 |
步骤 1：标出变更的自然键
步骤 1：标出变更的自然键
WITH src AS (
SELECT customer_id, name, phone,
md5(concat_ws('|', name, phone)) AS row_hash
FROM stg_customer WHERE load_ts > :last_load_ts),
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)
SELECT customer_id FROM chg;同批统一取批次日期作为 start_date，避免同一批生成多个小版本。