Деректерді қалыпқа келтіру

1) Мақсаты

Қалыпқа келтіру жаңартулардың телнұсқалары мен ауытқуларын жояды, бірыңғай анықтамалықтар мен кілттерді орнатады, деректерді сүйемелдеумен келісілген және арзан етеді. iGaming-те бұл GGR/NGR, AML/RG-талдаулар, реттеушілік есептілік, антифрод және ML үшін өте маңызды.

2) Қайда қалыпқа келтіреміз

Bronze (raw): қалыпқа келтірмейміз - форензия үшін қалай (append-only) болса, сондай сақтау.
Silver (clean/conform): негізгі қалыпқа келтіру (3NF/BCNF, анықтамалықтар, кілттер, SCD).
Gold (serve): мақсатты сөрелер -/BI оқу үшін басқарылатын денормализация болуы мүмкін.

3) Базалық қағидаттар

1. Schema-first: барлық кестелерде анық схемалар мен кілттер бар.
2. Бірыңғай идентификаторлар: 'user _ pseudo _ id', 'session _ id', 'game _ id', 'provider _ id', 'transaction _ id'.
3. Бірыңғай анықтамалықтар: валюталар, нарықтар/юрисдикциялар, KYC/RG мәртебелері, ойын провайдерлері, трафик арналары.
4. Уақыт және валюта: 'event _ time' (UTC) және қалыпқа келтірілген 'amount _ base' + 'fx _ source' сақтау.
5. Эволюция: семантикалық нұсқалар, тек «үнсіз» үзілістерсіз үйлесімді өзгерістер.
6. PII-азайту: пайдаланушы - псевдо-ID арқылы; маппинг жеке сақталады, кіру шектелген.

4) Қалыпты пішіндер жылдам

1НФ: атомарлық мәндер, бағандардағы массивтерсіз (→ child-кестелер массивтері).
2НФ: төлсипаттар барлық құрамдас кілтке байланысты.
3НФ: транзиттік тәуелділіктер жоқ (атрибут кілтке ғана байланысты).
BCNF: әрбір детерминант - кілт. «Өзек» үшін қолдану (payments/gameplay).

Практика: Silver-төлем модельдері мен ойын белсенділігін 3НФ; анықтамалықтар мен сілтемелік кестелер үшін - неғұрлым қатаң BCNF.

5) Эталондық домендік модель (Silver)

5. 1 Анықтамалықтар

`dim. users '(псевдо-ID, ел, жас ауқымы, RG-мәртебелері).
`dim. games '(game_id, provider_id, жанр, RTP, құбылмалылық).
`dim. providers '(provider_id, түрі, лицензиясы).
`dim. markets '(юрисдикция коды, реттеуші).
`dim. fx_rates` (date, ccy_from, ccy_to, rate, fx_source).

5. 2 Фактілер (оқиғалар/транзакциялардың тар кестелері)

`fact. payments` (transaction_id, user_pseudo_id, amount_orig, currency, amount_base, market, event_time, psp_ref, method).
`fact. bets` (bet_id, user_pseudo_id, game_id, stake_base, stake_ccy, outcome, event_time).
`fact. payouts` (payout_id, user_pseudo_id, game_id, amount_base, event_time).

Байланыстар: тұрақты кілттер бойынша анықтамалықтар. Барлық сомаларды «бастапқы валютада» және «базалық» (amount_base), 'fx _ source' деп белгілеп қайталаймыз.

6) Баяу өзгеретін өлшемдер (SCD)

Type I (қайта жазу): орфографиялық/критикалық емес түзетулер.
Type II (тарихы): 'valid _ from/valid _ to/is _ current', өзгерістер аудиті (мысалы, RG-мәртебесін ауыстыру).
Type III (баламалы баған): қысқаша салыстырулар үшін «дейін/кейін».

Ұсыным: RG/KYC/маркетингтік арна үшін - SCD II; ойын анықтамалықтары (RTP) үшін - әсер валидациясы бар SCD II.

sql
CREATE TABLE dim. users_scd (
user_pseudo_id STRING,
country STRING,
rg_status STRING,
valid_from TIMESTAMP,
valid_to  TIMESTAMP,
is_current BOOLEAN
);

7) Дедупликация және кілттер

Ішкі байланыстарға арналған суррогат кілттер (BIGINT/UUID).
Табиғи кілттер (мысалы, PSP-тен 'transaction _ id') - валидациялау және бөлек сақтау.
Silver бағдарламасындағы бизнес-кілттер бойынша '(event_id, source)' дедупы.

sql
CREATE TABLE silver. payments AS
SELECT EXCEPT(rn) FROM (
SELECT p., ROW_NUMBER() OVER (PARTITION BY transaction_id ORDER BY event_time) rn
FROM bronze. payment_events p
)
WHERE rn = 1;

8) Валюталар мен таймзондтарды стандарттау

'event _ time' - әрқашан UTC; витриналар үшін нарықтың жергілікті/таймзонын қосамыз.
Валюталар: 'amount _ orig' және 'amount _ base' (мысалы, EUR) + 'fx _ source', 'fx _ rate _ used'.
Курстарды күн сайын белгілеу: 'dim. Дереккөзі және хеш-қолтаңбасы бар fx_rates'.

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

9) Анықтамалықтардың консистенттілігі

Анықтамалықтардың бірыңғай тіркелімі (games, providers, markets, currencies).
DQ валидаторлары: 'in _ set', FK-сілтемелері, бірегейлігі, SCD-нің қайшылықсыздығы.
Сыртқы көздерден «жұқа» дименсиялардың автогенерациясы (ойындардың провайдерлері, елдер, PSP).

10) Қашан денормализациялау

• тұрақты «кең» репортаж (GGR, тәуекелді витриналар);
• BI-сұрау салуларды/дашбордтарды жеделдету;
• realtime-витринасы (ClickHouse/Pinot).

• Шындықтың көзі Silver болып қала береді.
• Денормалданған өрістер - Silver-дан есептелетін/көшірілетін; логиканы нұсқалау.
• Кез келген денормаландыру құжатталады және дұрыстығына тестіленеді.

11) «Жұлдыз» және «қар бүршігі» үлгісі

Жұлдыз: бір факт + жазық өлшемдер - оқу оңай және жылдам, жазу/келісу қымбат.
Қар бүршігі: өлшеулер қалыпқа келтірілді (кіші анықтамалықтарға қосылған) - телнұсқалар аз, сұрау салу қиынырақ.

Ұсыным: Gold көбінесе «жұлдыз», Silver - қалыпқа келтірілген «қар бүршігі».

12) Схемалардың эволюциясы (safe changes)

Back-compatible: nullable бағандарын қосу; жалаулары бар жаңа анықтамалық мәндер.
Breaking: қайта атау/түрін өзгерту/семантикалық жылжулар - тек '/v2 'арқылы және көші-қон кезеңіне екі рет жазу.
Келісімшарттар: JSON/Euro схемасы registry, consumer-tests үйлесімділікке.

13) Қалыпқа келтіру үшін DQ-бақылау

• Кілттердің бірегейлігі: 'transaction _ id', 'bet _ id'.
• Референттік тұтастық: FK на 'dim.'.
• Валюталары: 'currency' whitelist, 'fx _ rate _ used' емес NULL, 'amount _ base> = 0'.
• Уақыт: ақылға қонымды терезеде 'event _ time'; «болашақ» оқиғалардың болмауы.
• SCD дұрыстығы: 'valid _ from/valid _ to' қиылыспайтын ауқымдары.

14) SQL үлгілерінің үлгілері

sql
CREATE TABLE silver. fact_bets (
bet_id STRING PRIMARY KEY,
user_pseudo_id STRING NOT NULL,
game_id STRING NOT NULL,
stake_ccy DECIMAL(18,2) NOT NULL,
currency CHAR(3) NOT NULL,
stake_base DECIMAL(18,2) NOT NULL,
market CHAR(2) NOT NULL,
event_time TIMESTAMP NOT NULL
);

sql
CREATE VIEW gold. ggr_daily AS
SELECT
DATE(b. event_time) AS event_date,
m. 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. markets m ON m. code = b. market
JOIN dim. games g  ON g. game_id = b. game_id
GROUP BY 1,2,3;

15) Құпиялылық және комплаенс

Silver бағдарламасында пайдаланушыны псевдонимдеу; нақты ID байланыстары - жеке қорғалған контурда.
RLS/CLS және өрістерді бүркемелеу (талдамада e-mail/PAN қол жетімді емес).
Каталогтарды/кілттерді аймақтандыру, схемаларды кеңейтуге DPO-бақылау.

16) Бақылау және lineage

Data lineage - Bronze → Silver → Gold, трансформациялар мен келісімшарттардың нұсқалары.
Өлшемдер: completeness, validity, FK-қателер, телнұсқалар, уақыт бойынша «тесіктер», сұрау құны.
Анықтамалықтар мен FX-көздер үзілген кездегі алерттар.

17) RACI

R: Data Engineering (Silver/Gold модельдері), Data Platform (схемалар тіркелімі, DQ).
A: Head of Data/Architecture.
C: Compliance/DPO (PII/retention), Finance (FX/GGR), Risk (RG/AML).
I: BI/Өнім/Маркетинг/Операциялар.

18) Енгізу жол картасы

1. Анықтамалықтар тіркелімі (markets, currencies, providers, games).

2. Silver модельдері 'fact. payments`, `fact. bets ',' dim '(3НФ), SCD II үшін' dim. users`.

3. Валюталарды/таймзондтарды қалыпқа келтіру, базалық DQ-ережелер (FK/uniqueness/in_set).

4. Бірінші Gold-витрина (GGR Daily) және салыстыру тестілері.

• SCD кеңейту, ойын оқиғаларын жабу, провайдерлік конформалық модельдер.
• Схемалардың үйлесімділік автотестері, көші-қон симуляторы, метадеректер каталогы.
• Кілттерді/партияларды оңтайландыру, кластерлеу/Z-order.

• Gold, SLA/құны үшін денормализация саясаты; «жұлдыз/қар бүршігі» темплейттері.
• Құжаттаманың автогенерациясы, дашбордтардағы lineage бағандары.
• Өңірлік каталогтар мен шифрлау кілттері, DR-жаттығулар.

19) Сапа чек-парағы

• Бірыңғай кілттер мен анықтамалықтар бекітілді.
• 3НФ Silver, SCD «баяу» өлшемдерге қолданылған.
• Валюталар/таймзондтар қалыпқа келтірілді; 'fx _ source' белгіленеді.
• DQ ережелері (FK/uniqueness/range/in_set) белсенді.
• Теңдестіру құжатталды, түзету тестілері өтті.
• Дашбордтарда жаңалық/толымдылық сызғыштары мен өлшемдері көрінеді.

20) Жиі қателер және оларды болдырмау

Аналитикада PII араластыру: маппингтерді бөліңіз, CLS/RLS қолданыңыз.
Silver жеткіліксіз қалыпқа келтіру: 3НФ келтіріңіз, әйтпесе қымбат қолдау және салыстыру қателері.
FX «есеп беру фактісі бойынша»: бағамдар «артқы» санмен емес, оқиғамен тіркелуі тиіс.
Негізгі өлшеулер үшін SCD жоқ: RG/KYC/арналар тарихы жоғалады.
Gold қайта нормалау: артық join's → басқарылатын денормализация.
Схемалардың мөлдір емес эволюциясы: registry және consumer-tests пайдаланыңыз.

21) Қорытынды

Қалыпқа келтіру - бұл Silver-деңгей пәні: фактілер мен өлшемдер үшін 3НФ/BCNF бірыңғай кілттер мен анықтамалықтар, дұрыс тарих (SCD) және уақыт/валюталарды стандарттау. Осындай «скелетпен» Gold-витриналар болжамды, есептер салыстырмалы, ал иелену құны бақыланатын болады.