Робота з історичними даними

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/provaydery.
• Архівування 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, стійкий до аудиту і змін бізнес-логіки.