کار با داده های تاریخی

1) اهداف و اصول

هدف: ذخیره و پردازش وضعیت های گذشته به طوری که گزارش ها، مدل ها و تحقیقات تجدید پذیر، دقیق و سازگار است.

• زمان آگاه توسط مدل های زمان طراحی صریح در طرح ها و نمایش داده شد.
• تکرارپذیری: همان گزارش برای تاریخ D همیشه همان نتیجه را تولید می کند.
• Auditability: اصل و نسب، لایه های غیر قابل تغییر، WORM ها که در آن مورد نیاز است.
• هزینه آگاه: لایه های آرشیو، فشرده سازی، ذخیره سازی سرد با SLA قابل درک است.
• حریم خصوصی توسط طراحی: مدیریت PII برای معاملات گذشته نگر و درخواست های قانونی.

2) مدل های زمان

زمان رویداد: زمان رویداد واقعی (نرخ، سپرده).
زمان پردازش زمانی که سیستم رکورد را پردازش کرده است (ممکن است متفاوت باشد).
بیت زمانی: ذخیرهسازی هر دو رویداد و زمان پردازش برای ویرایشهای عطف به ماسبق.
فواصل اعتبار: «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) لایه ها و فرمت های ذخیره سازی

دریاچه: برنز (فقط ضمیمه خام) → نقره (تمیز/SCD/عادی سازی) → طلا (ویترین).
اسیدی - форматы: دلتا/کوه یخ/هودی (MERGE/Upsert، زمان سفر، عکس های فوری).
ذخیره سازی چند لایه: گرم/گرم/سرد + WORM برای مصنوعات نظارتی.
پارتیشن بندی: 'event _ date'، 'market'، 'tenant' ؛ خوشه بندی/مرتبه Z با پیش بینی های مکرر (کاربر/بازی/ارائه دهنده).

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) داستان واقعیت: عکس های فوری و bitemporal

Snapshots: یک عکس فوری از مصالح پایان روز/ماه (مانند تعادل کیف پول) - سرعت بخشیدن به ایجاد مجدد گزارش های تاریخی.
حقایق بیت زمانی: ثابت رویداد زمان و پردازش زمان برای تشخیص رفع اواخر از محاسبات گذشته نگر.
تاریخچه دقیقاً یک بار: dedup توسط 'event _ id' + idempotent MERGE.

6) زمان سفر و تکرارپذیری

سفر در زمان: خواندن جداول «در زمان T» برای اشکال زدایی، حوادث، آشتی.
نسخه های منطقی: مصنوعات تحول (نسخه های SQL/DBT، ظروف) و برچسب های «logic_version» در جداول خروجی.
خروجی های یخ زده: مصنوعات گزارش طلا دستگیر شده و بازنویسی نمی شوند، هش و ورود به سیستم صادرات در دسترس هستند.

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

7) پر کردن и پردازش مجدد

Backfill: محدوده تاریخی اولیه/پیش بارگذاری.
پردازش مجدد: محاسبه مجدد پس از رفع اشکالات یا تغییر قوانین کسب و کار.

• Idempotency (MERGE/upsert)، محدوده ها، سهمیه ها، خشک اجرا با مقایسه متریک.
• علامت گذاری نتیجه: 'recalc _ reason'، 'logic _ version'، 'reprocessed _ at'.

1. طلای فعلی را منجمد کنید 2) تأیید DLQ/DQ ؛ 3) اجرای نقره ای ؛ 4) مقایسه معیارها ؛ 5) بازسازی طلا ؛ 6) چاپ و امضا

8) آشتی

Checksums: تطبیق حجم/مقادیر فروش با OLTP، PSP/ارائه دهندگان.
بررسی حلقه: خط لوله مستقل در نمونه (مقایسه 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، انطباق و نگهداری قانونی

کمینه سازی PII: pseudonymization، نقشه برداری محافظت شده جداگانه.
DSAR/RTBF: پیش بینی های قابل محاسبه و ویرایش انتخابی لایه های تاریخی ؛ استثنائات قانونی ذخیره سازی مستند شده است.
حقوقی نگه دارید: پرچم «مسدود کردن» حذف در محدوده/اشیاء، WORM برای مصنوعات قابل گزارش.
حسابرسی: دسترسی غیر قابل تغییر و صادرات سیاهههای مربوط.

11) DQ و اصل و نسب برای تاریخ

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: نسخه های ورودی/تبدیل/خروجی را اصلاح کنید. گراف وابستگی برای retroformations مورد نیاز است.

12) عملکرد و هزینه

تقسیم بندی: بر اساس تاریخ/بازار/مستاجر ؛ خوشه بندی تهاجمی توسط 'user _ pseudo _ id '/' game _ id'، اگر ما اغلب فیلتر می کنیم.
فرمت ها: پارکت + آمار/فشرده سازی ؛ به طور منظم خلاء/بهینه سازی.
Materialization: پیش محاسبه برای «گران» تجمع تاریخی ؛ عکس های فوری برای گزارش سه ماهه/سالانه.
بایگانی: تبدیل دسته های قدیمی به ذخیره سازی سرد (SLA برای بازیابی مستند شده است).
نمونه گیری: فقط برای کارهای تحقیقاتی، نه برای تنظیم/امور مالی.

13) ویژگی های تاریخی برای ML

ویژگی رجیستری: هر ویژگی دارای فرمول، مالک، SLO، «model _ version» است.
سازگاری آنلاین/آفلاین: یک پایگاه کد تحول، تست تکرارپذیری.
رانش مشخصه: PSI/KS توسط دوره، ذخیره سازی توزیع های تاریخی.

14) الگوهای پرس و جو

As-of: تکرارپذیری گزارشها.
تجزیه و تحلیل کوهورت: کوهورت ثبت نام/سپرده اول، پنجره نورد.
به آرامی در حال تغییر حقایق: SCD II ('رویداد _ زمان بین و COALESCE (' 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 (مسئول): مهندسی داده (مدل/SCD/backfill)، پلت فرم داده (ACID/آرشیو)، امور مالی/انطباق (آشتی/ذخیره سازی مورد نیاز).
A (پاسخگو): رئیس داده/CDO.
C (مشورت شده): حقوقی/DPO (DSAR/RTBF/Legal Hold)، SRE (هزینه/SLA)، معماری.
I (مطلع): BI/محصول/بازاریابی/عملیات.

16) نقشه راه پیاده سازی

1. جداول ACID با زمان سفر (Delta/Iceberg/Hudi) و پارتیشن بندی اولیه.

2. SCD II برای ابعاد کلیدی (کاربران/بازی ها/ارائه دهندگان).

3. عکس های فوری روزانه از aggregates بحرانی (GGR روزانه).

4. DQ-as-code (uniqueness/in_set/temporal) + lineage-graph.

• حقایق بیت زمانی، به عنوان از API/قالب SQL، backfill در حال اجرا کتاب/پردازش مجدد.
• FX/تقویم/غنی سازی DST، آشتی OLTP↔DWH/provaydery.
• بایگانی ذخیره سازی سرد، WORM برای گزارش بسته ها، حقوقی نگه دارید.

• اتوماسیون کامل «پخش و چه اگر»، مقایسه معیارها و هشدارهای رگرسیون.
• ویژگی های تاریخی و کنترل رانش ML، هزینه شارژ هزینه ذخیره سازی.
• مستندات «به عنوان» معیارها و گزارش های تجدید پذیر.

17) چک لیست پیش فروش

• جداول پشتیبانی از زمان سفر ؛ سیاست های خلاء/نگهداری سازگار است.
• SCD II برای اندازه گیری های بحرانی اجرا می شود ؛ ملحق شدن تست شده
• تصاویر واحدهای کلیدی در D/M در دسترس هستند و با جرقه بررسی می شوند.
• قوانین DQ فعال هستند ؛ lineage ورودی ها/خروجی ها و نسخه های منطقی را نمایش می دهد.
• DSAR/RTBF/Legal Hold در لایه های تاریخی آزمایش شده است.
• آرشیو ذخیره سازی سرد و بازیابی مستند و تایید شده است.
• هزینه/GB، سهم سرد، SLA بازیابی

18) اشتباهات مکرر و چگونگی اجتناب از آنها

بدون مدل زمان صریح: اضافه کردن رویداد/پردازش/اعتبار.
FX «retroactive»: همیشه در زمان رویداد، فروشگاه 'fx _ source'.
پیوستن نامعتبر با SCD: استفاده از فاصله اعتبار، not 'is _ current'.
تبدیل ویترین طلا: خروجی های قابل گزارش باید تغییر ناپذیر (یا نسخه) باشد.
بدون اصل و نسب/DQ: هیچ اثبات و بازرسی - آنها را از روز اول وارد کنید.
هزینه غیرقابل کنترل: خاموش کردن احزاب گرم، خلاء، تبدیل به سرد.

19) واژه نامه

As-of Query - درخواست داده «همانطور که آنها به زمان T نگاه کردند».
Bitemporal - تثبیت همزمان رویداد و زمان پردازش.
Snapshot - عکس فوری از وضعیت/aggregates در پایان دوره.
سفر در زمان - خواندن نسخه های تاریخی جداول.
WORM - یک بار خواندن بسیاری از.

20) خط پایین

کار با داده های تاریخی فقط «ذخیره سازی طولانی» نیست، بلکه نظم و انضباط زمان: مدل های صریح رویداد/پردازش/بیت زمانی، SCD و عکس های فوری، درخواست های تجدید پذیر، آشتی های دقیق و کنترل های انطباق، مشاهده پذیری و معماری ذخیره سازی مقرون به صرفه. با پیروی از این راهنما، شما یک پایه تاریخی جامع برای گزارش، تجزیه و تحلیل و ML خواهید داشت که انعطاف پذیر برای حسابرسی و تغییرات در منطق کسب و کار است.