数据仓库设计之缓慢变化维度（SCD）

Posted 2024-09-19 Updated 2024-09- 19

By Steven

11~14 min read

缓慢变化维度（Slowly Changing Dimensions, SCD）是数据仓库设计中的一个重要概念，涉及到如何处理维度表中的数据在一段时间内发生的变化。当维度表中的某些属性随时间变化时，需要妥善处理这些变化，以保持数据的历史记录和一致性。

SCD 主要有三种类型，分别是：

Type 1 SCD：覆盖旧值。
Type 2 SCD：保留历史记录。
Type 3 SCD：新增列记录新值。

Type 1 SCD：覆盖旧值

当维度表中的某个属性发生变化时，直接更新该属性的值，覆盖旧值。

优点

数据占用空间小。
查询简单，不需要额外的逻辑处理。

缺点

丢失历史数据，无法追溯过去的状态。
不适合需要历史记录的分析场景。

应用场景

当历史数据不重要，只需要最新数据时。
简单的应用场景，对历史数据没有追溯需求。

示例

这里以更新客户（维度表：dim_customer）的电话号码为例。

下面示例中的语句都是以 HQL 语法为例。

如下语句按照 Type 1 的方案，就是先删除对应客户记录，再重新插入新的客户记录。实际实现中，应在删除之前先获取客户之前的信息。

-- 删除旧记录
DELETE FROM dim_customer
WHERE customer_id = 12345;

-- 插入新记录
INSERT INTO TABLE dim_customer
SELECT 
    12345, -- customer_id
    'John', -- first_name
    'Doe', -- last_name
    'john.doe@example.com', -- email
    '123-456-7890', -- phone
    -- 其他字段...
FROM 
    source_table
WHERE 
    customer_id = 12345;

Type 2 SCD：保留历史记录

当维度表中的某个属性发生变化时，保留旧值的历史记录，并插入一条新记录。

优点

保留了历史数据，可以追溯过去的记录。
适合需要历史记录的分析场景。

缺点

数据量较大，因为每条记录都会保留历史版本。
查询相对复杂，需要考虑时间范围。

应用场景

当历史数据非常重要，需要追溯过去的状态。
高级分析场景，如审计、合规性分析等。

示例

同样是更新客户的电话号码，在 Type 2 SCD 下的处理逻辑如下：

-- 创建临时表来保存新记录
CREATE TEMPORARY TABLE temp_new_records AS
SELECT 
    c.customer_id,
    c.first_name,
    c.last_name,
    c.email,
    c.phone,
    CURRENT_DATE AS effective_from,
    '9999-12-31' AS effective_to
FROM 
    source_table c
WHERE 
    c.phone <> (SELECT phone FROM dim_customer WHERE customer_id = c.customer_id ORDER BY effective_to DESC LIMIT 1);

-- 更新旧记录的有效截止日期
INSERT INTO TABLE dim_customer
SELECT 
    customer_id,
    first_name,
    last_name,
    email,
    phone,
    effective_from,
    CURRENT_DATE - INTERVAL 1 DAY -- 更新有效截止日期
FROM 
    dim_customer
WHERE 
    customer_id IN (SELECT customer_id FROM temp_new_records) AND effective_to = '9999-12-31';

-- 插入新记录
INSERT INTO TABLE dim_customer
SELECT 
    customer_id,
    first_name,
    last_name,
    email,
    phone,
    effective_from,
    effective_to
FROM 
    temp_new_records;

Type 3 SCD：新增列记录新值

当维度表中的某个属性发生变化时，不在原字段中更新，而是在现有记录中新增一个字段来记录新值。

优点

保留历史数据，同时不会破坏原有的数据结构。
可以追溯历史记录，同时查询相对简单。

缺点

随着时间推移，维度表的列数会逐渐增加，可能导致表结构膨胀。
需要额外的逻辑来管理新增的列。

应用场景

当需要保留历史数据，但又不想破坏原有数据结构时。
适用于某些特定的业务场景，如财务审计。

示例

用 Type 3 方案来更新客户的电话号码的步骤：

-- 创建新表
CREATE TABLE dim_customer_new LIKE dim_customer;

-- 添加新列
ALTER TABLE dim_customer_new ADD COLUMNS (phone_new VARCHAR(255));

-- 将旧表中的数据复制到新表
INSERT INTO TABLE dim_customer_new
SELECT 
    customer_id,
    first_name,
    last_name,
    email,
    phone,
    NULL -- 新列默认为空
FROM 
    dim_customer;

-- 更新新表中的新列
INSERT INTO TABLE dim_customer_new
SELECT 
    customer_id,
    first_name,
    last_name,
    email,
    phone,
    '987-654-3210' -- 新值
FROM 
    dim_customer
WHERE 
    customer_id = 12345;

-- 删除旧表
DROP TABLE dim_customer;

-- 重命名新表为旧表
ALTER TABLE dim_customer_new RENAME TO dim_customer;

总结

处理缓慢变化维度（SCD）是数据仓库设计中的一个重要环节。选择那种类型方案取决于业务需求和数据的特性。Type 1 SCD 适合不需要历史记录的场景，Type 2 SCD 保留了历史记录，适合需要追溯过去状态的高级分析场景，而 Type 3 SCD 则在保留历史数据的同时，不会破坏原有的数据结构。在实际应用中，还需要根据具体情况选择合适的 SCD 类型，并在 ETL 流程中妥善处理数据的变化。

我的博客

Hive 大数据数据仓库

License: CC BY 4.0