Как построить SQL-запрос для медленно меняющегося измерения (SCD Type 2), чтобы получить «актуальный на дату события» статус клиента?

Короткий ответ

Нужно присоединить таблицу фактов к таблице SCD2 (с полями valid_from/valid_to) по условию, что дата события попадает в интервал действия версии записи: fact.event_date BETWEEN scd.valid_from AND scd.valid_to.

Как это работает подробнее

Как это работает: SCD Type 2 хранит историю изменений атрибута как несколько строк на один бизнес-объект, каждая с периодом действия (valid_from, valid_to, где valid_to может быть NULL или датой в будущем для текущей версии). Соединение по интервалу даёт именно ту версию атрибута, которая была актуальна в момент события, а не текущую версию на сегодня.

Пример кода

PostgreSQL: temporal join к SCD Type 2Копировать

SELECT f.order_id, f.event_date, scd.region
FROM orders f
JOIN customer_scd2 scd
  ON scd.customer_id = f.customer_id
  AND f.event_date >= scd.valid_from
  AND (f.event_date < scd.valid_to OR scd.valid_to IS NULL);

Что использовать на практике

• На практике: частая ошибка в компаниях без SCD2 — соединение факта с «текущим» измерением (например, текущий регион клиента), что искажает исторические отчёты: если клиент переехал из региона A в регион B, все его прошлые заказы в отчёте задним числом «переезжают» в регион B, хотя фактически были сделаны из региона A.
• Проверка: Проверка: для клиента, сменившего регион, заказы до даты смены должны показывать старый регион, а после — новый.

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

• Частые ошибки: забывают обработать открытый интервал (valid_to IS NULL для текущей активной версии) отдельным условием (OR scd.valid_to IS NULL), из-за чего события после последнего обновления записи не находят подходящую версию и теряются при INNER JOIN.