Нормализатсияи маълумот

1) Мақсад

Нормализатсия нусхаҳо ва аномалияҳои навсозиро бартараф мекунад, феҳристҳо ва калидҳои якхеларо муқаррар мекунад, маълумотро барои нигоҳдорӣ мувофиқ ва арзон мекунад. Дар IGaming, ин барои GGR/NGR, таҳлили AML/RG, ҳисоботи танзимкунанда, антифрауд ва ML муҳим аст.

2) Дар куҷо мо муқаррар мекунем

Биринҷӣ (хом): муқаррарӣ нашудааст - нигоҳдорӣ, ба монанди (танҳо замима) барои криминалистика.
Нуқра (тоза/мувофиқат): нормализатсияи асосӣ (3NF/BCNF, феҳристҳо, калидҳо, SCD).
Тилло (хизмат): дӯконҳои мақсаднок - denormalization назоратшаванда барои хондан/BI имконпазир аст.

3) Принсипҳои асосӣ

1. Схема-аввал-Ҳама ҷадвалҳо схемаҳо ва калидҳои возеҳ доранд.
2. Идентификаторҳои ягона: 'user _ pseudo _ id', 'session _ id', 'game _ id', 'provider _ id', 'transaction _ id'.
3. Феҳристҳои умумӣ: асъорҳо, бозорҳо/қаламравҳо, статусҳои KYC/RG, провайдерҳои бозӣ, каналҳои трафик.
4. Вақт ва асъор: 'event _ time' (UTC) -ро нигоҳ доред ва 'миқдор _ база' + 'fx _ source' -ро муқаррар кунед.
5. Эволютсия: версияҳои семантикӣ, танҳо тағиротҳои мувофиқ бидуни танаффуси "хомӯш".
6. Кам кардани PII: корбар - тавассути псевдо-ID; харитасозӣ алоҳида нигоҳ дошта мешавад, дастрасӣ маҳдуд аст.

4) Шаклҳои муқаррарӣ зуд

1NF: арзишҳои атомӣ, массивҳо дар сутунҳо нестанд (массивҳо → ҷадвалҳои кӯдакон).
2NF-Attributes аз тамоми калиди мураккаб вобаста аст.
3NF: вобастагии гузаранда (атрибут танҳо аз калид вобаста аст).
BCNF: ҳар як муайянкунанда калид аст. Барои "ядро" истифода баред (пардохт/бозӣ).

Амал: Моделҳои нуқраи пардохт ва фаъолияти бозӣ ҳадди аққал 3NF нигоҳ медоранд; BCNF сахттар - барои маълумотномаҳо ва ҷадвалҳои истинод.

5) Модели домени истинод (нуқра)

5. 1 Маълумотномаҳо

'дим. истифодабарандагон '(псевдо-ID, кишвар, синну сол, ҳолати RG).
'дим. бозиҳо '(game_id, provider_id, жанр, RTP, ноустуворӣ).
'дим. провайдерҳо '(provider_id, намуд, иҷозатнома).
'дим. бозорҳо '(кодекси юрисдиксия, танзимкунанда).
'дим. fx_rates' (сана, ccy_from, ccy_to, қурб, fx_source).

5. 2 Далелҳо (ҷадвалҳои танг/амалиёт)

'факт. пардохтҳо '(transaction_id, user_pseudo_id, amount_orig, асъор, amount_base, бозор, event_time, psp_ref, усул).
'факт. гаравҳо ( , , , , натиҷа, .
'факт. пардохтҳо '( , , , ).

Истинодҳо: далелҳо ↔ дастурҳои калидҳои устувор. Мо ҳама маблағҳоро дар "асъори манбаъ" ва дар "пойгоҳ" (amount_base), ислоҳи 'fx _ source') такрор мекунем.

6) Андозагирии оҳиста тағирёбанда (SCD)

Навъи I (аз нав нависед): ислоҳоти имло/ғайри интиқодӣ.
Намуди II (таърих): 'дуруст _ аз/дурустии _ ба/is _ current', тағиротҳои аудит (масалан, тағйири ҳолати RG).
Навъи 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) барои пайвандҳои дохилӣ.
Калидҳои табиӣ (масалан, 'transaction _ id' аз PSP) - тасдиқ ва алоҳида нигоҳ дошта мешаванд.
Dedup by '(event_id, манбаъ)' барои ворид кардани + калидҳои корӣ дар Силвер.

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; барои тирезаҳои мағоза, локал/вақти бозорро илова кунед.
Асъорҳо: 'миқдор _ orig' ва 'sature _ base' (масалан, EUR) + 'fx _ source', 'fx _ rate _ used'.
Таъмири ҳаррӯзаи курсҳо: 'хира. 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) Мувофиқати китобҳои истинодӣ

Феҳристи ягонаи феҳристҳо (бозиҳо, провайдерҳо, бозорҳо, асъорҳо).
Валидаторҳои DQ: 'in _ set', истинодҳои FK, беҳамтоӣ, мувофиқати SCD.
Автогенератсияи dimencias "лоғар" аз манбаъҳои беруна (провайдерҳои бозӣ, кишварҳо, PSP).

10) Кай denormalize

• гузоришҳои устувори "васеъ" (GGR, намоиши хатарҳо);
• Суръатбахшии дархостҳои BI/панели панелҳо
• дар вақти хониши SLA мағозаҳои вақти воқеӣ (Click 'House/Pinot).

• Нуқра манбаи ҳақиқат боқӣ мемонад.
• Майдонҳои ғайримуқаррарӣ - аз нуқра ҳисоб карда/нусхабардорӣ карда мешаванд; мантиқи версия.
• Ҳама гуна denormalization ҳуҷҷатгузорӣ карда мешавад ва барои дурустӣ санҷида мешавад.

11) Модели ситора ва барфпӯши барфӣ

Ситора: як далел + андозагирии ҳамвор - хониши осонтар ва тезтар, навиштан/муқоисаи гаронтар.
Барфпӯши барфӣ: ченакҳо ба эътидол оварда мешаванд (зерқисматҳои пайвастшуда) - нусхаҳои камтар, дархостҳои мураккаб.

Тавсия: дар тилло бештар "ситора", дар нуқра - "барфпӯши барфӣ".

12) Эволютсияи схемаҳо (тағироти бехатар)

Бозгашт мувофиқ аст: илова кардани сутунҳои беэътибор; арзишҳои нави истинод бо парчамҳо.
Танаффус: тағир додани номгузорӣ/чопкунӣ/семантикӣ - танҳо тавассути '/v2 'ва воридшавии дукарата барои давраи муҳоҷират.
Шартномаҳо: схемаҳои JSON/Avro дар феҳрист, санҷишҳои истеъмолкунандагон барои мутобиқат.

13) Назорати DQ барои нормализатсия

• Калидҳо беназиранд: 'transaction _ id', 'bet _ id'.
• Беайбии истинод: FK дар 'хира'.
• Асъорҳо: 'асъор' аз сафедпӯст, 'fx _ rate _ used' NULL нест, 'маблағ _ base> = 0'.
• Вақт: 'event _ time' дар равзанаи оқилона; ягон воқеаи "оянда" вуҷуд надорад.
• SCD-дуруст: диапазонҳои такрорнашаванда 'дуруст _ аз/дуруст _ ба'.

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) Махфият ва риояи он

Бегона кардани корбар дар нуқра; пайвастшавӣ бо ID воқеӣ - дар схемаи алоҳидаи муҳофизатшаванда.
RLS/CLS ва ниқоби саҳроӣ (почтаи электронӣ/PAN дар таҳлил мавҷуд нест).
Минтақасозии феҳристҳо/калидҳо, назорати DPO барои тамдиди схема.

16) Риоя ва насл

Насли маълумот аз Bronze → Silver → Gold, версияи тағирот ва шартномаҳо.
Нишондиҳандаҳо: пуррагӣ, эътимоднокӣ, хатогиҳои ФК, такрорӣ, "сӯрохиҳо" дар вақташ, арзиши дархост.
Огоҳиҳо ҳангоми танаффус дар феҳристҳо ва манбаъҳои FX.

17) RACI

R: Муҳандисии маълумот (моделҳои нуқра/тиллоӣ), Платформаи маълумот (қайди ноҳиявӣ, DQ).
A: Сардори маълумот/меъморӣ.
C: Мувофиқат/DPO (PII/нигоҳдорӣ), Молия (FX/GGR), Хавф (RG/AML).
I: BI/Маҳсулот/Маркетинг/Амалиёт.

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

1. Феҳристи феҳристҳо (бозорҳо, асъорҳо, провайдерҳо, бозиҳо).

2. Далели нуқра-моделҳо. пардохтҳо ',' далел. bets ',' хира '. (3HF), SCD II барои' dim. истифодабарандагон '.

3. Меъёри асъор/минтақаи вақт, қоидаҳои асосии DQ (FK/uniqueness/in_set).

4. Аввалин намоиши тиллоӣ (GGR Daily) ва санҷишҳои оштӣ.

• Тавсеаи SCD, фарогирии рӯйдодҳои бозӣ, моделҳои мутобиқати провайдер.
• Автотестҳои мутобиқати схема, симуляторҳои муҳоҷират, каталоги метамаълумот.
• Оптимизатсияи калидҳо/ҳизбҳо, кластер/Z-фармоиш.

• Сиёсати denormalization барои тилло, SLA/арзиш; қолабҳои ситораи/барфпӯши барфӣ.
• Тавлиди худкори ҳуҷҷатгузорӣ, графикаи насл дар панели панелҳо.
• Феҳристҳои минтақавӣ ва калидҳои рамзгузорӣ, машқҳои DR.

19) Рӯйхати назорати сифат

• Калидҳо ва феҳристҳои ягона тасдиқ карда мешаванд.
• нуқра дар 3NF, SCD ба андозагирии "суст" татбиқ мешавад.
• асъор/вақтҳо муқаррар карда мешаванд; 'fx _ source' собит шудааст.
• Қоидаҳои DQ (FK/uniqueness/range/in_set) фаъоланд.
• Denormalization ҳуҷҷатгузорӣ, санҷишҳои дурустӣ гузаштанд.
• Дар панели панелҳо ченакҳои хатӣ ва тару тоза/пуррагӣ намоёнанд.

20) Хатогиҳои зуд-зуд ва чӣ гуна аз онҳо канорагирӣ кардан

Омезиши PII дар таҳлил: харитасозии алоҳида, CLS/RLS-ро истифода баред.
Нормализатсияи нокифояи нуқра: ба 3NF оварда мерасонад, вагарна хатогиҳои дастгирӣ ва оштӣ.
FX "барои як гузориш": Нархҳо бояд дар як чорабинӣ сабт карда шаванд, на "ақибмонда".
SCD барои андозаҳои калидӣ нест: таърихи гумшудаи RG/KYC/channel.
Азнавбарқароркунии тилло: ҳамроҳшавии зиёдатӣ § denormalization идорашаванда.
Таҳаввулоти ношаффофи схемаҳо: истифодаи феҳрист ва санҷишҳои истеъмолкунандагон.

21) Сатри поён

Нормализатсия як фанни сатҳи нуқра аст: калидҳои ягона ва маълумотномаҳо, 3NF/BCNF барои далелҳо ва андозагирӣ, таърихи дуруст (SCD) ва стандартикунонии вақт/асъор. Бо чунин "скелет", ҳолатҳои тиллоӣ пешгӯишаванда мешаванд, гузоришҳо қиёс карда мешаванд ва арзиши моликият назорат карда мешавад.