Что такое Slowly Changing Dimensions (типы SCD)?

Что такое Slowly Changing Dimensions (типы SCD)

SCD (Slowly Changing Dimensions) — способы обработки изменений в справочниках.

Типы SCD

Type 0: No Change — не отслеживаем изменения (ID, коды).

Type 1: Overwrite — перезаписываем:

UPDATE dim_customer SET email = 'new@mail.com' WHERE customer_id = 123;

Проблема: теряем историю.

Type 2: Add History Rows — добавляем новую строку с версией:

CREATE TABLE dim_customer (
    customer_key INT,
    customer_id INT,
    customer_name VARCHAR,
    email VARCHAR,
    start_date DATE,
    end_date DATE,
    is_current BOOLEAN,
    version INT
);

-- История: каждое изменение - новая строка
INSERT INTO dim_customer VALUES
    (1, 123, 'John', 'old@mail.com', '2020-01-01', '2023-12-31', FALSE, 1),
    (2, 123, 'John', 'new@mail.com', '2024-01-01', NULL, TRUE, 2);

Type 3: Previous Value — храним текущее и предыдущее:

CREATE TABLE dim_product (
    product_id INT PRIMARY KEY,
    name VARCHAR,
    current_price DECIMAL,
    previous_price DECIMAL,
    price_change_date DATE
);

UPDATE dim_product 
SET previous_price = current_price, 
    current_price = 99.99,
    price_change_date = TODAY()
WHERE product_id = 456;

Type 4: History Table — отдельная таблица истории:

-- Основная таблица
CREATE TABLE dim_customer (customer_id INT, email VARCHAR);

-- История
CREATE TABLE dim_customer_history (
    customer_id INT,
    email VARCHAR,
    change_date TIMESTAMP,
    change_type VARCHAR
);

Type 5: Hybrid — комбинация Type 1 и Type 2.

Практический пример: SCD2

def update_customer_dimension():
    new_data = pd.read_sql(
        'SELECT customer_id, name, email, city FROM raw_customers',
        source_conn
    )
    
    existing = pd.read_sql(
        'SELECT customer_id, email, city FROM dim_customer WHERE is_current = TRUE',
        target_conn
    )
    
    # Определяем изменения
    changed = new_data.merge(existing, on='customer_id', how='left')
    changed['has_change'] = (changed['email_x'] != changed['email_y']) | (changed['city_x'] != changed['city_y'])
    changed_rows = changed[changed['has_change']]
    
    # Закрываем старые записи
    for idx, row in changed_rows.iterrows():
        target_conn.execute(
            'UPDATE dim_customer SET end_date = TODAY(), is_current = FALSE WHERE customer_id = %s',
            (row['customer_id'],)
        )
    
    # Добавляем новые версии
    for idx, row in changed_rows.iterrows():
        target_conn.execute('''
            INSERT INTO dim_customer 
            (customer_id, email, city, start_date, is_current, version)
            VALUES (%s, %s, %s, TODAY(), TRUE, %s)
        ''', (row['customer_id'], row['email_x'], row['city_x'], 2))
    
    target_conn.commit()

Выбор типа

В практике: SCD2 в 80% для Data Warehouse.