如何识别缓慢变化维度（Type 2）表中的无效记录？

原始错误数据 #

目标修正后数据 #

识别缺失终结日期的idperson #

使用以下SQL查询找出所有没有end = '9999-12-31'记录的idperson：

SELECT idperson, name, role
FROM your_dim_table
GROUP BY idperson, name, role
HAVING MAX(end) <> '9999-12-31';

若需要同时获取该用户最新维度记录的结束日期，可使用：

WITH latest_records AS (
    SELECT 
        idperson, 
        name, 
        role, 
        MAX(end) AS latest_end_date
    FROM your_dim_table
    GROUP BY idperson, name, role
    HAVING MAX(end) <> '9999-12-31'
)
SELECT * FROM latest_records;

手动修正步骤 #

1. 对查询出的每个idperson，插入一条终结记录，确保新记录的start为原最新记录end的次日，IsActive设为0，end设为9999-12-31：
   以Tom（idperson=3456）为例：

INSERT INTO your_dim_table (idperson, name, role, IsActive, start, end)
VALUES (3456, 'tom', 'accountant', 0, '2022-08-31', '9999-12-31');

2. 若原最新记录的IsActive仍为1（不符合Type2维度规则），同步更新该字段：

UPDATE your_dim_table
SET IsActive = 0
WHERE iddim = 3;

内容的提问来源于stack exchange，提问作者Andres Mora