فرآیندهای ETL/ELT

1) هدف و زمینه

خطوط لوله ETL/ELT بارگیری، تبدیل و انتشار داده های قابل پیش بینی برای گزارش (GGR/NGR، تنظیم کننده ها)، تجزیه و تحلیل/ML و پانل های عملیاتی را فراهم می کند.

ETL: قبل از بارگیری به DWH/Lakehouse (کمتر در پشته های مدرن) تبدیل می شود.
ELT: ابتدا به Lakehouse (برنز/نقره ای) بارگیری کنید، سپس SQL/موتورها را تبدیل کنید (توصیه می شود).

2) معماری مرجع

1. مصرف/لبه: HTTP/gRPC/Batch، CDC از OLTP، S3/FTP آپلود ارائه دهنده.
2. برنز (خام، فقط ضمیمه): بارهای غیر قابل تغییر، احزاب بر اساس تاریخ/بازار/مستاجر.
3. نقره ای (تمیز/مطابق): عادی سازی، dedup، دایرکتوری ها، SCD، FX/منطقه زمانی.
4. طلا (خدمت): فروشگاه های غیرقانونی برای BI/تنظیم کننده/مدل.
5. ارکستراسیون: جریان هوا/Dagster/Prefect (DAG 'i، SLA، Retrai، Shifts).
6. DQ/قراردادها: Schema Registry + DQ - как - код، تست های مبتنی بر مصرف کننده.
7. قابلیت مشاهده: معیارهای خط لوله، تبار، سیاهههای مربوط، داشبورد هزینه.

3) انتخاب ETL در مقابل ELT

تمرین: در iGaming - ELT + CDC: بارگیری سریع، سپس استاندارد و شمارش.

4) افزایش و CDC

• CDC (Debezium/log replication): تغییرات OLTP → Bronze → MERGE در نقره.
• علامت گذاری به موقع: 'به روز شده _ at> max_loaded_ts'.
• تفاوت هش: 'md5 (ردیف)' مقایسه برای تشخیص تغییر.
• UPSERT/MERGE: توانایی بارگیری.

sql
MERGE INTO silver. payments s
USING stage. payments_delta d
ON s. transaction_id = d. transaction_id
WHEN MATCHED THEN UPDATE SET
WHEN NOT MATCHED THEN INSERT;

5) قراردادها و طرح ها

طرح اول: JSON/Avro/Protobuf در رجیستری ؛ 'schema _ version' در رویدادها/پرونده ها.
تکامل: سازگار با عقب (اضافات nullable) ؛ شکستن - '/v2 '+ دو ورودی.
فیلدهای مورد نیاز عبارتند از: event _ time (UTC)، event _ id، trace _ id، user _ pseudo _ id، market.

6) DQ-as-code (حداقل مجموعه)

yaml table: silver. payments owner: data-payments slo:
freshness_minutes: 15 completeness_percent: 99. 5 rules:
- name: unique_tx # uniqueness of transactions type: unique columns: [transaction_id]
severity: critical
- name: currency_whitelist type: in_set column: currency set: [EUR,USD,GBP,TRY,BRL]
severity: major
- name: amount_positive type: range column: amount_base min: 0. 01 severity: critical
- name: fk_user type: foreign_key column: user_pseudo_id ref_table: dim. users_scd severity: critical

7) ارکستراسیون: DAG و، وابستگی ها، SLA

طراحی DAG: از منابع به فروشگاه ؛ وابستگی آشکار بین وظایف

Retrai و idempotence: عقب نشینی، «تمیز» تکرار، بازرسی.
Catchup: گرفتن شسته و رفته از دوره های از دست رفته.

SLA: طلا، به عنوان مثال. روزانه قبل از 06:00 زمان محلی آماده است ؛ هشدار در مورد تخلفات

پارامتری کردن: بازارها/مستاجران/تاریخ از طریق vars ؛ یک قالب کار واحد.

8) Idempotence و دقیقا یک بار

In ingest: duplicates are possible → dedup by «(event_id، منبع)».
در حال پردازش: upsert/ادغام ؛ توابع تبدیل «خالص».

در سینک: تعهدات معاملاتی یا نوشتن idempotent ؛ کنترل «شمارش دوگانه»

Outbox/Inbox: انتشار معاملات رویدادهای دامنه از OLTP.

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

Backfill: محدوده اولیه/تاریخی.
پردازش مجدد - محاسبه زمانی که منطق تغییر/اصلاحات.
Guardrails: محدوده محدوده، سهمیه، پنجره های زمان، خشک اجرا با مقایسه متریک.
علامت گذاری: 'logic _ version'، 'reprocessed _ at'، 'recalc _ reason'.

10) مدل سازی نقره/طلا

نقره (3NF/BCNF): حقایق «واقعیت _ شرط/پرداخت/پرداخت»، ابعاد «کم _ کاربران/بازی/ارائه دهندگان/بازار (SCD II)»، استاندارد سازی ارز/منطقه زمانی.
طلا: فروشگاه های غیرقانونی برای BI/تنظیم کننده/مدل ؛ بسته های صادرات غیر قابل تغییر (WORM) + امضا.

مثال طلا: GGR روزانه

sql
CREATE OR REPLACE VIEW gold. ggr_daily AS
SELECT
DATE(b. event_time) AS event_date,
b. market,
g. provider_id,
SUM(b. stake_base) AS stakes_eur,
SUM(p. amount_base) AS payouts_eur,
SUM(b. stake_base) - SUM(p. amount_base) AS ggr_eur
FROM silver. fact_bets b
LEFT JOIN silver. fact_payouts p
ON p. user_pseudo_id = b. user_pseudo_id
AND p. game_id = b. game_id
AND DATE(p. event_time) = DATE(b. event_time)
JOIN dim. games g ON g. game_id = b. game_id
GROUP BY 1,2,3;

11) حفظ حریم خصوصی و اقامت

به حداقل رساندن PII: نشانه گذاری ؛ نقشه های شناسه های واقعی در حلقه جدا شده.
RLS/CLS: دسترسی به سیاست های نقش/صلاحیت، پوشش.
اقامت: دایرکتوری ها/کلید های جداگانه برای EEA/UK/BR ؛ ممنوعیت عضویت بین المللی بدون دلیل.
DSAR/RTBF & Legal Hold: ویرایش های انتخابی، بایگانی WORM برای گزارش، ممیزی صادرات.

12) قابلیت مشاهده و SLO

• طراوت نقره p95 ≤ 15 دقیقه ؛ طلا روزانه آماده است تا 06:00 قفل. زمان.
• کامل ≥ 99 5٪، اعتبار (طرح) ≥ 99. 9%.
• موفقیت کسب و کار ≥ 99 0٪، حوادث MTTR ≤ 24-48 ساعت.

داشبورد: نقشه حرارتی تازه، قیف از دست دادن DQ، هزینه/پرس و جو و هزینه/GB، نمودار خطی.

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

تقسیم بندی: تاریخ/بازار/مستاجر ؛ خوشه بندی/مرتبه Z توسط فیلترها.
فرمت ها: پارکت + ACID (دلتا/کوه یخ/هودی)، فشرده سازی و آمار.
فشرده سازی: مبارزه با فایل های کوچک (OPTIMIZE/VACUUM).
مواد: مصالح پایدار ؛ اجتناب از غول در پرواز می پیوندد.
بازپرداخت: بودجه، بازپرداخت سهمیه/بازپرداخت ؛ برنامه ریزی در پنجره های کم بار.

14) نمونه هایی از وظایف DAG معمولی (شبه کد جریان هوا)

python with DAG("elt_payments_daily", schedule="@daily", start_date=..., catchup=True) as dag:
extract = BashOperator(task_id="extract_cdc", bash_command="run_cdc_to_bronze. sh {{ ds }}")
load  = BashOperator(task_id="load_to_silver", bash_command="sql/run_merge_silver. sql {{ ds }}")
dq   = BashOperator(task_id="dq_checks", bash_command="dq/run_checks. sh silver. payments {{ ds }}")
gold  = BashOperator(task_id="build_gold_ggr", bash_command="sql/build_gold_ggr. sql {{ ds }}")
export = BashOperator(task_id="export_regulator", bash_command="export/run_worm_pack. sh {{ ds }}")

extract >> load >> dq >> gold >> export

15) فرآیندها و RACI

R (مسئول): مهندسی داده (DAG، مدل نقره/طلا)، پلت فرم داده (infra، رجیستری، DQ).
A (پاسخگو): رئیس داده/CDO.
C (مشورت): انطباق/حقوقی/DPO (PII/اقامت/نگهداری قانونی)، امور مالی (FX/GGR)، ریسک (RG/AML)، SRE (SLO/стоимость).
I (مطلع): BI/محصول/بازاریابی/عملیات.

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

1. دریاچه برنز/نقره ای (اسید) + CDC/افزایش برای پرداخت/گیم پلی.

2. DQ-like-code (قوانین 10-15) و داشبورد Freshness/Completeness اساسی.

3. اولین نمایشگاه طلا (GGR روزانه) با SLA «تا 06:00»، صادرات WORM با امضا.

4. DAG و ارکستراسیون هشدار در SLA/DQ.

• پسوند دامنه، SCD II برای کاربران/بازی ها/ارائه دهندگان.
• لایه معنایی معیارها ؛ تجزیه و تحلیل خط/تاثیر ؛ روش های پر کردن/پردازش مجدد.
• منطقه بندی (EEA/UK)، RLS/CLS، کنترل هزینه (سهمیه/بازپرداخت).

• شبیه ساز پخش (چه اگر)، تولید خودکار مستندات ویترین/معیارها.
• بهینه سازی هزینه (خوشه بندی، تحقق، TTL، فشرده سازی).
• تمرینات DR و بازیابی زمان سفر.

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

• قراردادها/طرح ها در رجیستری، تست سازگاری سبز.
• CDC/increments و MERGE بی نظیر هستند ؛ dedup به مصرف.
• قوانین DQ فعال هستند (بحرانی → شکست + DLQ)، داشبورد SLA پیکربندی شده است.
• ویترین های طلا فرمول های متریک مستند شده در لایه معنایی هستند.
• RBAC/ABAC، رمزگذاری، اقامت، DSAR/RTBF/حقوقی نگه تایید شده است.
• فشرده سازی/OPTIMIZE/VACUUM در یک برنامه ؛ محدودیت های backfill/replay
• Runbook و حوادث و پردازش مجدد، صادرات حسابرسی (WORM + هش).

18) ضد الگوهای و خطرات

بارگذاری کامل «فقط در مورد»: استفاده از CDC/افزایش.
مخلوط کردن داده های خام و گزارش شده: برنز/نقره/طلا را جداگانه نگه دارید.
فقدان DQ و اصل و نسب: بدون اثبات و تکرارپذیری.
PII در لایه های تحلیلی: جدا کردن نقشه ها، اعمال CLS/RLS.
یکپارچه «شب» jabs: له، موازی در دسته.
نادیده گرفتن هزینه: نگه داشتن چشم در فایل های کوچک، تحقق aggregates، معرفی سهمیه.

19) واژه نامه (کوتاه)

ETL/ELT - استخراج/تبدیل/بارگیری (قبل/بعد از بارگیری).
CDC - تغییرات را ضبط کنید.
SCD - تاریخچه اندازه گیری (I/II/III).
WORM - ذخیره سازی غیر قابل تغییر بسته های گزارش.
سفر در زمان - خواندن نسخه های تاریخی جداول.

20) خط پایین

ETL/ELT مدرن اسکریپت نیست، بلکه یک پلت فرم مدیریت شده است: قراردادها و DQ، افزایش ایده آل/CDC، نظم لایه برنز/نقره ای/طلا، قابلیت مشاهده و SLO، حریم خصوصی و اقتصاد. با پیروی از این راهنما، شما خطوط لوله قابل تجدید و قابل شنیدن را دریافت خواهید کرد که به طور مداوم گزارش، محصول و مدل ها را در مقیاس و بدون شگفتی ارائه می دهند.