Работа с историческими данными

1) Назначение и принципы

Цель: хранить и обрабатывать прошлые состояния так, чтобы отчеты, модели и расследования были воспроизводимыми, точными и комплаентными.

• Time-aware by design: явные модели времени в схемах и запросах.
• Reproducibility: один и тот же отчет за дату D всегда дает тот же результат.
• Auditability: доказуемое происхождение (lineage), неизменяемые слои, WORM там, где нужно.
• Cost-aware: архивные слои, компрессия, cold storage с понятными SLA.
• Privacy-by-design: управление PII при ретроспективных операциях и правовых запросах.

2) Модели времени

Event-time: время фактического события (ставка, депозит).
Processing-time: когда система обработала запись (может отличаться).
Bitemporal: хранение и event-, и processing-времени для правок задним числом.
Validity-интервалы: `valid_from`, `valid_to`, `is_current`.
As-of queries: выборка данных «как знали в момент T».

sql event_time TIMESTAMP, -- event time processed_at TIMESTAMP, -- TIMESTAMP valid_from processing time, -- start of version validity valid_to TIMESTAMP, -- end of validity (NULL if current)
is_current   BOOLEAN

3) Слои хранения и форматы

Lakehouse: Bronze (raw append-only) → Silver (clean/SCD/нормализация) → Gold (витрины).
ACID-форматы: Delta/Iceberg/Hudi (MERGE/Upsert, time-travel, snapshots).
Tiered storage: hot/warm/cold + WORM для регуляторных артефактов.
Партиционирование: `event_date`, `market`, `tenant`; кластеризация/Z-order по частым предикатам (user/game/provider).

4) Историзация измерений (SCD)

SCD I: перезапись — для некритичных правок.
SCD II: полная история; рекомендуется для RG/KYC/каналов трафика/атрибутов игр.
SCD III: «до/после» — редкие кейсы сравнения.

sql
MERGE INTO dim. users_scd t
USING stage. users u
ON t. user_pseudo_id = u. user_pseudo_id AND t. is_current = TRUE
WHEN MATCHED AND (t. rg_status <> u. rg_status OR t. country <> u. country) THEN
UPDATE SET is_current = FALSE, valid_to = CURRENT_TIMESTAMP
WHEN NOT MATCHED THEN
INSERT (user_pseudo_id, country, rg_status, valid_from, valid_to, is_current)
VALUES (u. user_pseudo_id, u. country, u. rg_status, CURRENT_TIMESTAMP, NULL, TRUE);

5) История фактов: снимки и бitemporal

Снимки (snapshots): снимок агрегатов на конец дня/месяца (например, баланс кошелька) — ускоряют воссоздание исторических отчетов.
Bitemporal факты: фиксируем event-time и processing-time, чтобы отличать поздние исправления от ретроспективных расчетов.
Exactly-once история: дедуп по `event_id` + идемпотентные MERGE.

6) Time-travel и воспроизводимость

Time-travel: чтение таблиц «на момент Т» для отладки, инцидентов, сверок.
Версионирование логики: артефакты трансформаций (версии SQL/DBT, контейнеров) и метки «logic_version» в выходных таблицах.
Frozen outputs: Gold-артефакты отчетности фиксируются и не переписываются, доступен hash и журнал экспорта.

sql
SELECT
FROM silver. fact_bets VERSION AS OF 1678901234567
WHERE event_date = DATE '2025-10-31';

7) Backfill и Reprocessing

Backfill: первичная/догрузка исторического диапазона.
Reprocessing: пересчет после исправления багов или смены бизнес-правил.

• Идемпотентность (MERGE/upsert), диапазоны, квоты, «темный прогон» (dry-run) с сравнением метрик.
• Маркируем результат: `recalc_reason`, `logic_version`, `reprocessed_at`.

1. Freeze текущие Gold; 2) проверка DLQ/DQ; 3) прогон Silver; 4) сравнение метрик; 5) пересборка Gold; 6) публикация и подпись.

8) Сверки точности (reconciliation)

Контрольные суммы: сверка оборотов/количеств с OLTP, PSP/провайдерами.
Двухконтурная проверка: независимый pipeline на выборке (A/B сравнение).
Допуски: например, расхождение GGR ≤ 0.2% за день.

sql
-- Duplicates
SELECT transaction_id, COUNT() c
FROM silver. payments
GROUP BY transaction_id
HAVING COUNT() > 1;

-- Unknown Currencies/Markets
SELECT p. currency
FROM silver. payments p
LEFT JOIN ref. currencies r ON r. code = p. currency
WHERE r. code IS NULL;

9) Валюты, время, календарь: историческая корректность

FX на дату события: фиксируем `fx_rate_used` и `fx_source`.
Локальное время рынка: DST/таймзоны через справочник календарей.
Праздники/сезонность: отдельная таблица календаря, используем в моделях и отчетах.

sql
SELECT p. transaction_id,
p. amount_orig,
r. rate AS fx_rate_used,
p. amount_orig r. rate AS amount_base,
r. fx_source
FROM bronze. payment_events p
JOIN dim. fx_rates r
ON r. date = DATE(p. event_time) AND r. ccy_from = p. currency AND r. ccy_to = 'EUR';

10) PII, комплаенс и Legal Hold

PII-минимизация: псевдонимизация, отдельный защищенный маппинг.
DSAR/RTBF: вычислимые проекции и селективные редактирования исторических слоев; исключения по законной обязанности хранения документируются.
Legal Hold: флаги «заморозки» удалений на диапазоны/объекты, WORM для отчетных артефактов.
Аудит: неизменяемые логи доступов и экспортов.

11) DQ и lineage для истории

yaml table: silver. fact_bets slo:
completeness_percent: 99. 5 freshness_minutes: 60 rules:
- name: unique_bet type: unique columns: [bet_id]
severity: critical
- name: market_known type: in_set column: market set_ref: ref. markets
- name: ts_in_range type: temporal expression: "event_time BETWEEN date_sub(now(), interval 5 year) AND now()"

Lineage: фиксируем версии входов/трансформаций/выходов; граф зависимостей обязателен для ретроизменений.

12) Производительность и стоимость

Партиционирование: по дате/рынку/тенанту; агрессивная кластеризация по `user_pseudo_id`/`game_id`, если часто фильтруем.
Форматы: Parquet + статистики/компрессия; регулярный VACUUM/OPTIMIZE.
Материализация: precompute для «дорогих» исторических агрегаций; снапшоты для поквартальной/годовой отчетности.
Архивирование: перевод старых партиций в cold storage (SLA на восстановление документируется).
Сэмплирование: только для исследовательских задач, не для регуляторки/финансов.

13) Исторические фичи для ML

Feature registry: каждая фича имеет формулу, owner, SLO, `model_version`.
Согласованность online/offline: одна кодовая база трансформаций, тесты репроходимости.
Дрейф признаков: PSI/KS по периодам, хранение исторических распределений.

14) Паттерны запросов

As-of (на дату): воспроизводимость отчетов.
Cohort-анализ: когорты регистраций/первых депозитов, rolling окна.
Slowly changing facts: корректные join’ы с SCD II (`event_time BETWEEN valid_from AND COALESCE(valid_to, '9999-12-31')`).

sql
SELECT b. bet_id, u. rg_status
FROM silver. fact_bets b
JOIN dim. users_scd u
ON u. user_pseudo_id = b. user_pseudo_id
AND b. event_time >= u. valid_from
AND (u. valid_to IS NULL OR b. event_time < u. valid_to);

15) Процессы и RACI

R (Responsible): Data Engineering (модели/SCD/backfill), Data Platform (ACID/архив), Finance/Compliance (сверки/требования хранения).
A (Accountable): Head of Data/CDO.
C (Consulted): Legal/DPO (DSAR/RTBF/Legal Hold), SRE (стоимость/SLA), Архитектура.
I (Informed): BI/Продукт/Маркетинг/Операции.

16) Дорожная карта внедрения

1. ACID-таблицы с time-travel (Delta/Iceberg/Hudi) и базовое партиционирование.

2. SCD II для ключевых измерений (users/games/providers).

3. Ежедневные snapshots критичных агрегатов (GGR Daily).

4. DQ-как-код (uniqueness/in_set/temporal) + lineage-граф.

• Bitemporal факты, as-of API/SQL-шаблоны, runbooks backfill/reprocessing.
• FX/календарь/DST-обогащение, сверки OLTP↔DWH/провайдеры.
• Архивирование cold storage, WORM для отчетных пакетов, Legal Hold.

• Полная автоматизация «replay & what-if», сравнение метрик и алерты регрессий.
• Исторические фичи и дрейф-контроль ML, chargeback по стоимости хранения.
• Документация «as-of» метрик и воспроизводимых отчетов.

17) Чек-лист перед продом

• Таблицы поддерживают time-travel; политики VACUUM/RETENTION согласованы.
• SCD II реализован для критичных измерений; join’ы протестированы.
• Снимки ключевых агрегатов на D/M доступны и проверены сверками.
• DQ-правила активны; lineage отображает входы/выходы и версии логики.
• DSAR/RTBF/Legal Hold протестированы на исторических слоях.
• Архивирование и восстановление из cold storage документировано и проверено.
• Стоимость хранения под контролем (cost/GB, доля cold, SLA восстановления).

18) Частые ошибки и как их избегать

Отсутствие явной модели времени: добавляйте event/processing/validity.
FX «задним числом»: всегда курс на момент события, хранить `fx_source`.
Неправильные join’ы с SCD: используйте интервал валидности, а не `is_current`.
Мутирующие Gold-витрины: отчетные выходы должны быть неизменяемыми (или с версионированием).
Без lineage/DQ: нет доказуемости и контрольных точек — вводите их с первого дня.
Неуправляемая стоимость: отключите горячие партиции, вакуумируйте, переводите в cold.

19) Глоссарий

As-of Query — запрос данных «как они выглядели на момент Т».
Bitemporal — одновременная фиксация event и processing времени.
Snapshot — материализованный снимок состояния/агрегатов на конец периода.
Time-travel — чтение исторических версий таблиц.
WORM — неизменяемое хранение (Write Once Read Many).

20) Итог

Работа с историческими данными — это не просто «длинное хранение», а дисциплина времени: явные модели event/processing/bitemporal, SCD и snapshots, reproducible as-of запросы, строгие сверки и комплаенс-контроли, наблюдаемость и экономная архитектура хранения. Следуя этому руководству, вы получите надежный исторический фундамент для отчетности, аналитики и ML, устойчивый к аудиту и изменениям бизнес-логики.