OBenner/data-engineering: SCD Type 1, 2, 3

Условие

Что такое Slowly Changing Dimensions (SCD)? Опишите Type 1, Type 2, Type 3. Что выберете для таблицы dim_customer, у которой меняется адрес и тарифный план?

Решение

Подход

SCD — техника моделирования размерностей, у которых атрибуты меняются медленно. Например, пользователь поменял город или подписку. Вопрос — что делать с историей.

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

UPDATE dim_customer SET city = 'Berlin' WHERE id = 42 — теряем историю. Дёшево, просто, но любой исторический отчёт «выручка по городам в 2023» начнёт показывать новые города.

Type 2 — версионируем строки.

Каждое изменение = новая строка с valid_from/valid_to. Старая закрывается, новая открывается. История сохраняется полностью.

CREATE TABLE dim_customer (
    surrogate_key BIGSERIAL PRIMARY KEY,
    customer_id   BIGINT,
    name          TEXT,
    city          TEXT,
    plan          TEXT,
    valid_from    DATE NOT NULL,
    valid_to      DATE,
    is_current    BOOLEAN
);

Type 3 — отдельные колонки для предыдущего значения.

current_city + previous_city + city_changed_at. Подходит для «понять одно последнее изменение», но не полная история.

Type 4 — историческая таблица.

dim_customer хранит только текущее состояние, отдельная dim_customer_history — все изменения. Удобно при частых изменениях, чтобы не раздувать основную dim.

Type 6 — гибрид (1 + 2 + 3).

Сочетает: историческая строка (Type 2), флаг текущей версии и колонка current_value для удобства join-ов с фактами «по последнему значению».

Для dim_customer с адресом и тарифом

• Если отчёты «выручка по городам в прошлом квартале» нужны исторически верными — Type 2.
• Если адрес — справочная информация и не критичен для отчётов — Type 1 на адресе.
• Тарифный план почти всегда нужен исторически (выручка по плану в момент покупки) — Type 2.
• Часто на одной таблице комбинируют: Type 2 для важных атрибутов, Type 1 для второстепенных.

dbt-снэпшот

dbt из коробки умеет SCD Type 2 через snapshots:

{% snapshot dim_customer_snapshot %}
{{
    config(
      target_database='analytics',
      unique_key='customer_id',
      strategy='check',
      check_cols=['city', 'plan'],
    )
}}
SELECT * FROM {{ source('crm', 'customers') }}
{% endsnapshot %}

dbt сам поддерживает dbt_valid_from, dbt_valid_to.

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

1. Join фактов с dim. Для SCD Type 2 факт-строка с датой D должна джойниться с версией dim, где D BETWEEN valid_from AND COALESCE(valid_to, '9999-12-31'). Простой JOIN ON customer_id даст карту повторов или потеряет факт.
2. Surrogate key vs natural key. Surrogate key уникален для каждой версии; natural (customer_id) — общий для всех версий клиента. В фактах хранят surrogate_key.
3. Late-arriving dimensions. Размерность пришла позже факта; нужно либо ждать (плохо), либо использовать «unknown member» и обновлять задним числом.
4. Раздувание таблицы. Type 2 на быстро меняющемся атрибуте (например, баланс) — антипаттерн; такие атрибуты живут в фактовых таблицах.

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

SCD Type 1 — перезапись (без истории), Type 2 — версии строк с valid_from/valid_to (полная история), Type 3 — отдельные колонки для предыдущего значения. Для важных бизнес-атрибутов (тариф, адрес для выручки по гео) — Type 2. dbt-snapshots делают это из коробки. В фактах джойниться с dim надо по surrogate key и временному окну, а не по customer_id.