Равандҳои ETL/ELT

1) Мақсад ва контекст

Қубурҳои ETL/ELT боркунии пешгӯишаванда, тағирот ва нашри маълумотро барои гузориш (GGR/NGR, танзимгарон), таҳлил/ML ва панелҳои амалиётӣ таъмин мекунанд.

ETL: пеш аз бор кардан ба DWH/Lakehouse табдил диҳед (камтар дар стекҳои муосир).
ELT: аввал ба Lakehouse (биринҷӣ/нуқра) бор кунед, баъд SQL/муҳаррикҳоро табдил диҳед (тавсия дода мешавад).

2) Меъмории истинод

1. Instest/Edge: HTTP/GRPC/Batch, CDC аз OLTP, провайдери боргузорӣ S3/FTP.
2. Биринҷӣ (хом, танҳо замима): бори гарони тағйирнопазир, ҳизбҳо аз рӯи сана/бозор/иҷорагир.
3. Нуқра (тоза/мувофиқат): нормализатсия, тарҳ, феҳристҳо, SCD, FX/вақтҳо.
4. Тилло (хизмат): дӯконҳои ғайримуқаррарӣ барои BI/танзимкунанда/моделҳо.
5. Оркестр: Airflow/Dagster/Prefect (DAG 'i, SLA, Retrai, Shifts).
6. DQ/Шартномаҳо: Феҳристи схема + DQ-kak-kod, санҷишҳои истеъмолкунандагон.
7. Мушоҳида: ченакҳои қубур, насл, гузоришҳо, панели хароҷот.

3) интихоби ETL vs ELT

Амалия: дар IGaming - ELT + CDC: зуд бор кунед, пас стандартизатсия кунед ва ҳисоб кунед.

4) Афзоиш ва CDC

• CDC (нусхабардории Debezium/log): OLTP тағир меёбад → биринҷӣ → MERGE дар нуқра.
• Аломати обӣ аз рӯи вақт: 'updated _ at> max_loaded_ts'.
• Hash diff: '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' дар рӯйдодҳо/файлҳо.
Эволютсия: бозгашт мувофиқ (иловаҳои беэътибор); шикастан - '/v2 '+ вуруди дугона.
Майдонҳои зарурӣ 'event _ time (UTC)', 'event _ id', 'trace _ id', 'user _ pseudo _ id', 'бозор' мебошанд.

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 ба вақти маҳаллӣ омода аст; огоҳӣ дар бораи қонунвайронкуниҳо.
Параметри: бозорҳо/иҷорагирон/санаҳо тавассути вариантҳо; як қолаби ягонаи корӣ.

8) Idempotence ва маҳз як-як

Ҳангоми ворид кардан: нусхабардорӣ аз рӯи '(event_id, манбаъ) имконпазир аст.
Ҳангоми коркард: upsert/merge; функсияҳои табдилдиҳии "пок".
Дар танӯр: супоришҳои транзаксионӣ ё idempotent менависанд; назорати "ҳисобкунии дукарата".
Outbox/Inbox: нашри транзаксионии рӯйдодҳои домейн аз OLTP.

9) Backfill i коркард

Backfill: диапазонҳои ибтидоии пуркунӣ/таърихӣ.
Коркард - ҳисобкунии дубора ҳангоми тағир додани мантиқ/ислоҳ.
Муҳофизаҳо: маҳдудиятҳои диапазон, квотаҳо, тирезаҳои вақт, хушккунӣ бо муқоисаи метрӣ.
Ишора: 'мантиқ _ версия', 'коркард _ at', 'recalc _ reason'.

10) Моделсозии нуқра/тиллоӣ

Силвер (3NF/BCNF): далелҳои 'fact _ bets/пардохтҳо/пардохтҳо', андозаҳои 'dim _ users/бозиҳо/провайдерҳо/бозорҳо (SCD II)', стандартикунонии асъор/вақтҳо.
Тилло: дӯконҳои ғайримуқаррарӣ барои BI/танзимкунанда/моделҳо; бастаҳои содиротии тағйирнопазир (WORM) + имзо.

Намунаи тилло: GGR Daily

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 & Hold Legal: таҳрирҳои интихобӣ, бойгонии WORM барои ҳисоботдиҳӣ, аудити содиротӣ.

12) Мушоҳида ва SLO

• Тару тоза нуқра p95 ≤ 15 дақ; Ҳар рӯз тилло то соати 06:00 баста мешавад. вақт.
• Пуррагӣ ≥ 99. 5%, Эътибор (схема) ≥ 99. 9%.
• Муваффақияти ҷойҳои корӣ ≥ 99. 0%, ҳодисаҳои MTTR ≤ 24-48 соат.

Панели панелҳо: Харитаи гармидиҳӣ, харитаи талафоти DQ, арзиш/дархост ва арзиш/ГБ, графикаи насл.

13) Иҷро ва арзиши

Тақсимот: сана/бозор/иҷорагир; кластерӣ/Z-фармоиш аз ҷониби филтрҳо.
Форматҳо: Parquet + ACID (Delta/Iceberg/Hudi), фишурдасозӣ ва омор.
Фишурдасозӣ: мубориза бо файлҳои хурд (OPTIMIZE/VACUUM).
Материализатсия: агрегатҳои устувор; канорагирӣ кунед.
Баргардонидани маблағ: буҷетҳо, квотаҳои такрорӣ/backfill; банақшагирӣ дар тирезаҳои сарбории кам.

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, моделҳои нуқра/тиллоӣ), Платформаи маълумот (инфра, регистр, DQ).
A (Ҳисоботдиҳанда): Роҳбари маълумот/CDO.
C (Машварат): Мутобиқат/Ҳуқуқӣ/DPO (PII/истиқомат/Нигоҳдории ҳуқуқӣ), Молия (FX/GGR), Хавф (RG/AML), SRE (SLO/stoimostel).
I (Маълумот): BI/Маҳсулот/Маркетинг/Амалиёт.

16) Харитаи роҳсозӣ

1. Lakehouse биринҷӣ/нуқра (ACID) + CDC/афзоиш барои пардохт/бозӣ.

2. DQ-like-code (қоидаҳои 10-15) ва панелҳои асосии Freshness/пуррагӣ.

3. Аввалин намоиши тиллоӣ (GGR Daily) бо SLA "то соати 06:00", содироти WORM бо имзо.

4. DAG ва оркестри ҳушдор дар SLA/DQ.

• Тамдиди домейн, SCD II барои корбарон/бозиҳо/провайдерҳо.
• Қабати семантикии ченакҳо; таҳлили насл/таъсир; расмиёти backfill/коркард.
• Минтақасозӣ (EEA/UK), RLS/CLS, назорати хароҷот (квота/пардохт).

• Такрори симулятор (чӣ-агар), тавлиди худкори ҳуҷҷатҳои намоишӣ/ченакҳо.
• Оптимизатсияи хароҷот (кластерӣ, материализатсия, TTL, фишурдасозӣ).
• Машқҳои DR ва барқароркунии вақти сафар.

17) Рӯйхати санҷиши пеш аз фурӯш

• Шартномаҳо/схемаҳо дар Феҳрист, мутобиқат сабзро месанҷад.
• CDC/афзоиш ва MERGE idempotent мебошанд; dedup ба indest.
• Қоидаҳои DQ фаъоланд (интиқодӣ → ноком + DLQ), панелҳои SLA танзим карда шудаанд.
• Намоишҳои тиллоӣ ҳуҷҷатгузорӣ шудаанд, формулаҳои метрӣ дар қабати семантикӣ.
• RBAC/ABAC, рамзгузорӣ, иқомат, DSAR/RTBF/Hold Legal тасдиқ карда шуд.
• Фишурдасозӣ/ОПТИМИЗАТСИЯ/ВАКУУМ аз рӯи ҷадвал; маҳдудиятҳои backfill/такрорӣ.
• Дафтарчаи корӣ ва ҳодисаҳо ва коркард, содироти аудит (WORM + hash).

18) Анти-намунаҳо ва хатарҳо

Боркунии пурраи "танҳо дар ҳолати": CDC/афзоишро истифода баред.
Омезиши маълумоти хом ва гузоришшуда: Биринҷӣ/нуқра/тиллоро алоҳида нигоҳ доред.
Набудани DQ ва насл: исбот ва такрористеҳсолкунӣ нест.
PII дар қабатҳои таҳлилӣ: харитасозии изолятсия, истифодаи CLS/RLS.
Ҷабҳаҳои "шабона" -и монолитӣ: пошидан, параллел дар партияҳо.
Арзишро нодида гиред: файлҳои хурдро аз назар гузаронед, агрегатҳоро ҷобаҷо кунед, квотаҳоро ҷорӣ кунед.

19) Луғат (мухтасар)

ETL/ELT - истихроҷ/табдилдиҳӣ/боркунӣ (пеш аз/пас аз боркунӣ).
CDC - Тағирот.
SCD - таърихнигории андозагирӣ (I/II/III).
WORM - нигоҳдории тағйирнопазири бастаҳои ҳисоботӣ.
Вақти сафар - хондани версияҳои таърихии ҷадвалҳо.

20) Сатри поён

ETL/ELT муосир скриптҳо нест, балки платформаи идорашаванда аст: шартномаҳо ва DQ, афзоишҳои номатлуб/CDC, интизоми қабати биринҷӣ/нуқра/тиллоӣ, мушоҳида ва SLO, махфият ва иқтисодиёт. Бо риояи ин дастур, шумо қубурҳои такроршаванда ва аудиториро мегиред, ки пайваста ҳисобот, маҳсулот ва моделҳоро дар миқёс ва бидуни ногаҳонӣ ба даст меоранд.