题干与适用场景
这是维度建模与历史语义题。客户维度的地址变化不能只看“最新值”：财务报表可能要求回答某天客户属于哪个地区。假设源系统每天发送全量快照、没有变更日志，目标表需要支持当前状态、历史回溯和可重跑处理。Dataquest 将 SCD Type 1、2、3 列为数据工程面试题；AWS 的示例也以无 CDC 的全量文件实现 Type 2。
面试官考察点
- 能否先定义报表要回答“现在”还是“当时”，再选择 SCD 类型。
- 能否区分覆盖旧值、追加版本、保留一个前值的存储语义。
- 能否用自然键、代理键、有效时间和当前标志维护唯一性。
- 能否处理迟到快照、重复文件、删除、回放与并发写入，而不是只写一条
UPDATE。
回答前需要澄清的问题
- 报表是否必须按事件发生时间还原历史，还是只要当前画像？
- 源快照是否带版本号、抽取时间或水位，是否可能乱序到达？
- 删除是业务删除、暂时缺席，还是源系统漏字段？
- 允许维度历史修订事实表外键吗？是否需要审计、回放和幂等重跑？
30 秒回答框架
“我先把时间语义写成契约：Type 1 只保留当前值，Type 2 为每次业务变化新增版本并保存 validfrom、validto，Type 3 只保留一个或少量前值。若要点时分析，我选 Type 2，用自然键找当前行、代理键连接事实表，并由快照水位识别迟到和删除。批次先落地并去重，事务地关闭旧版本、插入新版本；重复批次只允许产生同样结果。”
分步骤深入解答
第一步：先确定历史问题
Type 1 适合纠错或不需要历史的画像，直接覆盖；Type 2 适合审计、收入归属和点时查询，完整保留版本；Type 3 只适合“当前值与上一个值”这类固定窗口。若需求说“回看上季度的客户地区”，Type 1 和 Type 3 都丢失信息，必须选 Type 2。
第二步：设计 Type 2 约束
维度行包含代理键、自然键、属性、validfrom、validto、iscurrent 和来源批次水位。对同一自然键，最多一行 iscurrent = true；有效区间不能重叠。AWS 示例使用开始/结束日期、当前标志和逻辑删除来保留全历史，Microsoft 的 Type 2 配置也要求自然键、代理键、两个日期和活动标志。
第三步：从全量快照推导变化
先把文件写入不可变的 landing 表，记录文件哈希、抽取时间和批次序号。对每个自然键与当前维度做哈希比较：新键插入，属性变化关闭旧行并插入新行，相同哈希跳过。快照中缺失的键只有在“这是完整快照”契约成立时才能标记删除；增量文件不能用缺失推断删除。
第四步：处理迟到与乱序
为每个批次保存单调水位，拒绝或隔离低于已应用水位的文件。若业务允许历史修订，可在事务中找到受影响时间区间，重新切分版本并重算事实连接；若不允许修改已发布报表，就把迟到批次放入修订队列并发布影响范围。不能仅按到达时间覆盖，因为它会把旧事实错误地显示为新状态。
第五步：删除、重跑与并发
完整快照缺失的自然键生成逻辑删除版本或关闭当前行，保留审计记录。批次表以文件哈希或来源批次做幂等键；同一批次重跑不得重复插入版本。使用单一自然键分区的事务或合并锁，确保关闭旧行与插入新行原子完成。并发批次必须按水位排序，不能让较旧批次重新打开已关闭区间。
可验证的 SQL 处理骨架
-- current_dim: one current row per customer_id
BEGIN;
UPDATE dim_customer AS old
SET valid_to = :as_of,
is_current = FALSE,
source_batch = :batch_id
FROM stage_customer AS incoming
WHERE old.customer_id = incoming.customer_id
AND old.is_current = TRUE
AND old.row_hash <> incoming.row_hash;
INSERT INTO dim_customer (
customer_key, customer_id, city, valid_from, valid_to,
is_current, source_batch, row_hash
)
SELECT nextval('dim_customer_key_seq'), incoming.customer_id,
incoming.city, :as_of, NULL, TRUE, :batch_id, incoming.row_hash
FROM stage_customer AS incoming
LEFT JOIN dim_customer AS old
ON old.customer_id = incoming.customer_id
AND old.is_current = TRUE
WHERE old.customer_id IS NULL
OR old.row_hash <> incoming.row_hash;
COMMIT;生产实现还要给 batchid 建唯一约束，并在提交前检查每个自然键只有一行当前记录、区间不重叠和输入水位合法。SQL 骨架假设 stagecustomer 已经按批次去重；它不是处理乱序的完整方案。
高质量示范回答
“我先问报表要保留哪种历史。只要当前值就用 Type 1；要回答某个过去日期的状态就用 Type 2；只需比较当前与上一个值才用 Type 3。对于每天全量快照，我会把原始文件落地并以批次水位和文件哈希去重，用自然键和属性哈希识别新增、变化与未变化。Type 2 更新必须在一个事务中关闭旧行、插入新行，并保证当前行唯一、时间区间不重叠。缺失键只有在完整快照契约下才代表删除；迟到批次按水位隔离，是否回修历史要由报表发布契约决定。”
常见错误
- 看到“维度变化”就默认 Type 2 → 存储和查询成本上升 → 先问历史查询契约。
- 用到达时间当有效时间 → 乱序快照污染历史 → 使用业务生效时间或明确的修订队列。
- 把增量文件中缺失的键当删除 → 正常增量被误删 → 只有完整快照才能推断缺失删除。
- 只更新
is_current不插入代理键版本 → 事实无法点时连接 → 关闭旧行并新增版本。 - 没有批次幂等键 → 重跑生成重复历史 → 以文件哈希或批次号做唯一约束。
追问及应对
迟到事件要回修已经发布的报表吗？
先确认报表是否允许修订。允许时按业务时间重切有效区间并重算受影响事实；不允许时保留原结果，发布带影响范围的修订批次。两种契约都要记录原始批次，避免静默改数。
同一客户在一个快照中出现两行怎么办？
把它视为输入质量错误，按业务版本或来源序号确定唯一记录；无法确定时隔离批次并报警。任意 LIMIT 1 会让历史结果不可复现。
Type 3 什么时候比 Type 2 更合适？
只需要“当前地区”和“上一个地区”，且不会查询更早版本时，Type 3 的列模型更省空间、查询更简单。需求一旦扩大到任意日期回溯，就应迁移到 Type 2 并说明历史补建成本。
如何验证没有区间重叠？
按自然键排序，检查相邻版本的结束时间不晚于下一版本的开始时间，并检查每个自然键最多一个当前行。把检查作为批次发布门槛，而不是只在事后抽样。