求助：使用Pentaho构建SCD Type 2数据仓库的实操指导与项目示例

一、代理键（surrogate_id）的正确加载方式 #

代理键是数据仓库维度表的唯一标识，SCD Type 2 中同一业务实体的不同版本需要对应不同代理键，加载时遵循以下逻辑：

• 生成唯一代理键：
  • 用Generate Row步骤：先查询维度表当前最大代理键，设置自增起始值为该最大值+1，为每条新/变更记录生成唯一ID。
  • 用数据库序列：如果目标库支持（如PostgreSQL、Oracle），通过Table Input执行SELECT nextval('your_sequence_name') AS surrogate_id，再用Join Rows (Cartesian)将序列值与待加载数据关联。
• 关联业务数据与代理键：
  核心是通过**自然键（业务主键，如客户ID）**匹配源数据与维度表记录：
  • 新增记录：自然键在维度表中无匹配，直接生成新代理键并加载。
  • 变更记录：自然键匹配到维度表的当前版本记录，先将原版本的end_date设为当前日期、is_current设为N，再为变更后的新记录生成新代理键并加载。

二、完整实操案例：客户维度 SCD Type 2 搭建 #

以客户维度为例，字段包括：surrogate_id（代理键）、customer_id（自然键）、name、email、phone、start_date、end_date、is_current，步骤如下：

1. 抽取源数据
   用Table Input步骤从业务库读取客户数据，SQL示例：

   SELECT customer_id, name, email, phone FROM business_db.customers
   

2. 读取现有维度数据
   另一个Table Input读取数据仓库的客户维度表：

   SELECT surrogate_id, customer_id, name, email, phone, is_current 
   FROM dw.dim_customer WHERE is_current = 'Y'
   

3. 识别新增/变更记录
   用Merge Join步骤，以customer_id为连接键做左连接：

   • 新增记录：维度表侧字段为NULL（源数据有、维度无）。
   • 变更记录：源数据与维度表的name/email/phone任一字段不一致。
   • 无变化记录：字段完全匹配，直接过滤丢弃。

4. 更新旧版本记录
   对变更记录，用Filter Rows筛选出维度表侧有值的记录，添加end_date（当前日期）、is_current='N'（用Add Constants步骤），再通过Table Output（选择更新模式，按surrogate_id匹配）更新维度表的旧版本记录。

5. 生成新代理键
   对新增+变更的待加载记录，用Generate Row生成自增ID，或通过数据库序列获取唯一值。

6. 加载新版本记录
   用Add Constants添加：

   • start_date：当前日期（通过Get System Info步骤获取）
   • end_date：'9999-12-31'
   • is_current：'Y'
     最后用Table Output将记录插入维度表。

7. 结果验证
   查询维度表，确认同一customer_id的不同版本记录时间区间无重叠，且只有一条is_current='Y'的记录。

关键注意事项 #

• 小数据量场景优先用Lookup步骤：替代Merge Join直接查询维度表当前记录，配置更简单。
• 日期一致性：统一用Get System Info获取当前时间，避免不同步骤时间差导致的逻辑错误。
• 事务控制：在转换开头添加Start Transaction，结尾添加Commit Transaction，确保更新和插入操作原子性。

内容的提问来源于stack exchange，提问作者Süleyman Səməndov