row_number()不能直接用作scd类型2版本号，因其为运行时实时计算、排序不确定，导致同一自然键在全量重刷或补数据时版本号不稳；正确做法是将_row_number作为物理列落库，基于max+coalesce+确定性哈希生成，确保版本连续可依赖。
ROW_NUMBER() 不能直接当 SCD 版本号用
因为 ROW_NUMBER() 是运行时按排序实时计算的，而 SCD 类型 2 要求同一自然键（如 product_id）的历史版本号必须稳定、连续、不可变。一旦重跑全量或补历史数据，只要 ORDER BY 子句里含非确定性字段（比如 load_timestamp 精度不足、或 effective_date 重复），同一行就可能拿到不同编号。
常见错误现象：
– 补跑 2022 年某天数据后，product_id = 123 的第 3 条记录突然变成第 4 条
– 下游报表发现“版本号跳变”，关联事实表时漏掉某段历史
- 正确做法是把版本号作为物理列落库，而非依赖视图或 CTE 动态算
- 排序必须带确定性哈希，例如
MD5(CONCAT(name, category))，避免effective_date相同时顺序飘移 - 新增记录的版本号 =
COALESCE(MAX(_row_number), 0) + ROW_NUMBER()，且这个MAX必须来自已落库的活跃行（WHERE is_current = TRUE或end_date IS NULL）
LAG() 推导 end_date 在补数据时会出错
有人写 LAG(end_date) OVER (PARTITION BY product_id ORDER BY effective_date) 想自动填前一行的 end_date，这在单次增量加载中看似可行，但遇到补历史或并发变更就崩。
典型风险场景：
– 补跑 2022-03-15 数据，新插入的记录打乱原有时间线，LAG() 返回错误的上一版 end_date
– 同一天两次价格调整（effective_date 相同），窗口函数排序不确定，导致 end_date 覆盖错位
– 目标表没对 effective_date 建唯一约束，数据库不保证物理插入顺序 = 窗口看到的顺序
- 稳妥做法：所有
end_date必须由上游明确提供，或 ETL 中用自连接找最小后继日期（SELECT MIN(b.effective_date) FROM dim AS a JOIN dim AS b ON a.product_id = b.product_id AND a.effective_date ） - 绝不依赖
LAG()或LEAD()自动生成端点——它们只适合分析，不适合生产级拉链表构建
Hive/Spark SQL 与 Snowflake 对窗口函数的 NULL 处理不一致
不同引擎对 ROW_NUMBER() 和 COALESCE() 的行为差异，会直接影响 _row_number 的可靠性。
关键差异点：
– Hive 3.1+ 支持标准 ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...)，但若 ORDER BY 字段含 NULL，默认排最前，而 Snowflake 默认排最后
– Spark SQL 的 COALESCE(MAX(_row_number), 0) 在空分区返回 NULL，需额外加 IFNULL(..., 0)；Snowflake 则直接支持 COALESCE
– Hive 不支持在同一个查询中先 SELECT 再 INSERT OVERWRITE 同一张表，必须拆成两步；Snowflake 允许 CTE + INSERT
- 跨平台写法建议：显式处理 NULL，比如
ORDER BY effective_date ASC, COALESCE(source_hash, '0') ASC - 不要假设引擎默认行为一致；测试时务必用真实空分区、重复日期、NULL 字段组合验证
- 物理列
_row_number必须在 INSERT 阶段就生成并写死，不能靠下游 VIEW 二次计算
代理键和版本号冗余到事实表才是性能解法
拉链表本身带时间范围（start_date/end_date），每次关联事实表都要做 BETWEEN 或 AND 范围匹配，查询性能差是硬伤。
真实业务中，90% 的 OLAP 查询并不需要追溯任意时间点——只需要“交易发生时对应的维度快照”。这时候，把稳定版本号（_row_number）或代理键（sk_product）冗余进事实表，是最直接的加速手段。
- 事实表加一列
product_sk，值来自维度表当前生效行的代理键，ETL 时一次性绑定 - 查询时不再
JOIN dim ON fact.date BETWEEN dim.start AND dim.end，而是JOIN dim ON fact.product_sk = dim.sk_product - 代价是 ETL 逻辑变重：每次维度变更，要反查事实表更新旧记录的
product_sk？不，只在新事实写入时绑定，历史事实保持原product_sk不变——这才是 SCD 2 的语义
真正容易被忽略的点：版本号必须物理落库、排序必须带哈希、end_date 必须显式计算——这三件事缺一不可。窗口函数只是工具，不是银弹。
