Что такое slowly changing dimensions (SCD)? Опишите различные типы SCD и сценарии их применения.?

Slowly Changing Dimensions (SCD)

SCD — это методология управления изменениями справочных (dimension) данных в хранилищах данных (data warehouses). Справочники редко меняются, но изменения нужно отслеживать для исторического анализа.

Типы SCD

SCD Type 0: Не меняется

Данные справочника никогда не изменяются. Самый простой случай.

-- Пример: страны (код, название)
CREATE TABLE countries (
    country_id INT PRIMARY KEY,
    country_code VARCHAR(2),
    country_name VARCHAR(100)
);

-- Вставка один раз, обновления запрещены
INSERT INTO countries VALUES (1, 'US', 'United States');

Когда используется: постоянные справочники (страны, валюты, коды)

SCD Type 1: Перезапись (Overwrite)

Обновляются только текущие значения, история теряется.

-- Пример: справочник сотрудников
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    name VARCHAR(100),
    department VARCHAR(100),
    salary DECIMAL(10,2)
);

-- День 1: Боб в отделе HR, зарплата 50k
INSERT INTO employees VALUES (1, 'Bob', 'HR', 50000);

-- День 100: Боб переведен в IT, зарплата 60k
-- Type 1: просто обновляем
UPDATE employees SET department = 'IT', salary = 60000 WHERE employee_id = 1;

-- После обновления видим только текущее состояние
SELECT * FROM employees WHERE employee_id = 1;
-- 1, Bob, IT, 60000

Плюсы: простота, минимум места Минусы: потеря истории, невозможно анализировать изменения

SCD Type 2: Добавление новой строки (Version)

Зависит история всех изменений с временными метками.

CREATE TABLE employees_scd2 (
    employee_id INT,
    name VARCHAR(100),
    department VARCHAR(100),
    salary DECIMAL(10,2),
    effective_date DATE,
    end_date DATE,
    is_current BOOLEAN,
    PRIMARY KEY (employee_id, effective_date)
);

-- День 1: Боб в HR
INSERT INTO employees_scd2 VALUES 
(1, 'Bob', 'HR', 50000, '2024-01-01', '9999-12-31', TRUE);

-- День 100: Боб переводят в IT
-- Step 1: Закрываем старую запись
UPDATE employees_scd2 
SET is_current = FALSE, end_date = '2024-04-10'
WHERE employee_id = 1 AND is_current = TRUE;

-- Step 2: Добавляем новую запись
INSERT INTO employees_scd2 VALUES
(1, 'Bob', 'IT', 60000, '2024-04-11', '9999-12-31', TRUE);

-- Теперь видим историю:
SELECT * FROM employees_scd2 WHERE employee_id = 1 ORDER BY effective_date;
-- 1, Bob, HR, 50000, 2024-01-01, 2024-04-10, FALSE
-- 1, Bob, IT, 60000, 2024-04-11, 9999-12-31, TRUE

Плюсы: полная история, точечные запросы по дате Минусы: дублирование данных, сложность обновлений

SCD Type 3: Хранение предыдущего значения

Хранит текущее и одно предыдущее значение в одной строке.

CREATE TABLE employees_scd3 (
    employee_id INT PRIMARY KEY,
    name VARCHAR(100),
    current_department VARCHAR(100),
    previous_department VARCHAR(100),
    current_salary DECIMAL(10,2),
    previous_salary DECIMAL(10,2),
    updated_date DATE
);

-- День 1: Боб в HR
INSERT INTO employees_scd3 VALUES
(1, 'Bob', 'HR', NULL, 50000, NULL, '2024-01-01');

-- День 100: Боб переводят в IT
UPDATE employees_scd3 
SET previous_department = current_department,
    current_department = 'IT',
    previous_salary = current_salary,
    current_salary = 60000,
    updated_date = '2024-04-11'
WHERE employee_id = 1;

-- Результат: видим текущее и одно предыдущее
SELECT * FROM employees_scd3 WHERE employee_id = 1;
-- 1, Bob, IT, HR, 60000, 50000, 2024-04-11

Плюсы: компактность, быстрые запросы Минусы: только одно предыдущее значение, неполная история

SCD Type 4: Отдельная History таблица

Текущие данные в основной таблице, полная история в отдельной.

-- Текущая таблица (Type 1)
CREATE TABLE employees_current (
    employee_id INT PRIMARY KEY,
    name VARCHAR(100),
    department VARCHAR(100),
    salary DECIMAL(10,2)
);

-- История (Type 2)
CREATE TABLE employees_history (
    employee_id INT,
    name VARCHAR(100),
    department VARCHAR(100),
    salary DECIMAL(10,2),
    effective_date DATE,
    end_date DATE,
    PRIMARY KEY (employee_id, effective_date)
);

-- Update процесс
-- Step 1: Архивируем старую запись в history
INSERT INTO employees_history
SELECT employee_id, name, department, salary, 
       effective_date, CURRENT_DATE - 1 as end_date
FROM employees_history
WHERE employee_id = 1 AND end_date = '9999-12-31';

-- Step 2: Обновляем текущую
UPDATE employees_current 
SET department = 'IT', salary = 60000 WHERE employee_id = 1;

Плюсы: разделение текущего/исторического Минусы: дополнительная сложность управления

Практический пример: E-commerce категория

import pandas as pd
from datetime import datetime, timedelta

class ProductCategoryDimension:
    """Управление Category Dimension с SCD Type 2"""
    
    def __init__(self, db):
        self.db = db
    
    def update_category(self, category_id: int, 
                       category_name: str, 
                       parent_id: int):
        """Обновление категории (SCD Type 2)"""
        
        # Закрыть старую версию
        self.db.execute("""
            UPDATE dim_product_category 
            SET is_current = FALSE, end_date = CURRENT_DATE
            WHERE category_id = %s AND is_current = TRUE
        """, (category_id,))
        
        # Добавить новую версию
        self.db.execute("""
            INSERT INTO dim_product_category 
            (category_id, category_name, parent_id, 
             effective_date, end_date, is_current)
            VALUES (%s, %s, %s, CURRENT_DATE, '9999-12-31', TRUE)
        """, (category_id, category_name, parent_id))
    
    def get_category_at_date(self, category_id: int, query_date: str):
        """Получить состояние категории на дату"""
        return self.db.execute("""
            SELECT * FROM dim_product_category
            WHERE category_id = %s 
            AND effective_date <= %s 
            AND end_date >= %s
        """, (category_id, query_date, query_date))

Сравнение типов SCD

Выбор типа SCD

def choose_scd_type(dimension_name: str) -> str:
    """
    Рекомендации по выбору типа SCD
    """
    scd_selection = {
        # Type 0: никогда не меняется
        'countries': 0,  # коды стран
        'currencies': 0,  # валюты
        
        # Type 1: текущее состояние достаточно
        'customer_segment': 1,  # сегмент клиента может меняться
        'product_description': 1,  # описание может переписываться
        
        # Type 2: нужна полная история
        'employee': 2,  # отдел, должность, зарплата
        'customer_address': 2,  # адреса доставки
        'product_price': 2,  # цены историчны
        
        # Type 3: нужна история и компактность
        'product_category': 3,  # категория меняется редко
        
        # Type 4: разделение текущего/исторического
        'complex_hierarchy': 4,  # иерархические справочники
    }
    return scd_selection.get(dimension_name, 1)

Выбор типа SCD критичен для Data Engineer, так как влияет на архитектуру хранилища, производительность и возможность анализа исторических данных.