Условие

Бизнес жалуется: «По старому отчёту у клиента Иванова был сегмент VIP в январе, а сейчас он Standard — но в декабрьском отчёте сегмент тоже отображается как Standard, хотя тогда был VIP». В чём проблема и как её решить? Реализуйте SCD Type 2 для таблицы dim_customer.

Решение

Подход

Проблема в SCD Type 1: при обновлении атрибута старое значение затирается. Декабрьские заказы агрегируются с текущим сегментом, а не с тем, что был на дату заказа.

SCD Type 2 хранит историю: каждое изменение атрибута создаёт новую строку с valid_from/valid_to/is_current.

Схема

CREATE TABLE dim_customer (
    customer_key    BIGSERIAL PRIMARY KEY,   -- surrogate key
    customer_id     VARCHAR(50)  NOT NULL,    -- natural key
    full_name       VARCHAR(255),
    email           VARCHAR(255),
    segment         VARCHAR(50),
    city            VARCHAR(100),
    valid_from      DATE         NOT NULL,
    valid_to        DATE         NOT NULL DEFAULT '9999-12-31',
    is_current      BOOLEAN      NOT NULL,
    hash_diff       VARCHAR(64)               -- md5 от tracked-атрибутов
);
 
CREATE INDEX ix_dim_cust_nk     ON dim_customer (customer_id, is_current);
CREATE INDEX ix_dim_cust_period ON dim_customer (customer_id, valid_from, valid_to);

Загрузка (merge)

-- 1) staging со свежим срезом
CREATE TEMP TABLE stg_customer AS
SELECT customer_id, full_name, email, segment, city,
       md5(concat_ws('|', full_name, email, segment, city)) AS hash_diff
FROM source.customer_snapshot;
 
-- 2) закрыть строки, где hash_diff изменился
UPDATE dim_customer dc
SET valid_to   = CURRENT_DATE - 1,
    is_current = false
FROM stg_customer s
WHERE dc.customer_id = s.customer_id
  AND dc.is_current
  AND dc.hash_diff <> s.hash_diff;
 
-- 3) вставить новые версии для тех же клиентов
INSERT INTO dim_customer (customer_id, full_name, email, segment, city,
                          valid_from, valid_to, is_current, hash_diff)
SELECT s.customer_id, s.full_name, s.email, s.segment, s.city,
       CURRENT_DATE, DATE '9999-12-31', true, s.hash_diff
FROM stg_customer s
LEFT JOIN dim_customer dc
       ON dc.customer_id = s.customer_id AND dc.is_current
WHERE dc.customer_id IS NULL                       -- совсем новый клиент
   OR dc.hash_diff <> s.hash_diff;                 -- изменился атрибут

Использование в fact-JOIN

SELECT dc.segment, SUM(f.amount) AS revenue
FROM fact_orders f
JOIN dim_customer dc
  ON dc.customer_id = f.customer_id
 AND f.order_date BETWEEN dc.valid_from AND dc.valid_to
WHERE f.order_date BETWEEN '2024-12-01' AND '2024-12-31'
GROUP BY dc.segment;

Декабрьский отчёт корректно агрегирует по сегменту, актуальному на дату заказа.

dbt-snapshots

В dbt SCD Type 2 делается одной командой:

# snapshots/dim_customer.sql
{% snapshot dim_customer %}
{{
    config(
      target_schema='snapshots',
      unique_key='customer_id',
      strategy='check',
      check_cols=['full_name', 'email', 'segment', 'city']
    )
}}
SELECT * FROM {{ source('raw', 'customer') }}
{% endsnapshot %}

Подводные камни

JOIN без диапазона дат — главная ошибка: JOIN dim_customer ON customer_id без условия BETWEEN valid_from AND valid_to даст N строк на одного клиента.
valid_to overlap — два пересекающихся периода для одного customer_id ломают агрегацию. Запретить триггером / тестом.
is_current = true ровно для одной строки на customer_id. Проверка: COUNT(*) FILTER (WHERE is_current) = 1.
Surrogate key обязателен: natural key (customer_id) не уникален в SCD-2.
PII в hash_diff — md5 от email безопасен; не хранить сам email в hash.
«А что, если атрибут не tracked?» — определить заранее, какие колонки SCD-2 (segment), какие SCD-1 (last_login). Иначе таблица распухнет.
Late-arriving fact: заказ пришёл «задним числом» с датой 2024-06, а dim уже обновился. JOIN по диапазону справится; UPDATE — нет.

Эталонный ответ

Проблема — SCD Type 1: атрибуты затёрты. Решение — SCD Type 2 с историчными строками (valid_from, valid_to, is_current) и JOIN'ом fact-таблицы по диапазону дат. Реализация — три шага: insert новых, close + insert изменившихся, через сравнение hash_diff. В dbt: snapshot со стратегией check по нужным колонкам.

DataLearn DE-101: SCD Type 2 — история изменения атрибутов