მონაცემთა ნორმალიზაცია

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- ის საშუალებით; mapping ინახება ცალკე, დაშვება შეზღუდულია.

4) ნორმალური ფორმები სწრაფად

1NF: ატომური მნიშვნელობები, სვეტებში მასივების გარეშე (მასივები - ბავშვი ცხრილი).
2NF: ატრიბუტები დამოკიდებულია ყველა კომპონენტზე.
3NF: არ არსებობს გარდამავალი დამოკიდებულება (ატრიბუტი დამოკიდებულია მხოლოდ გასაღებზე).
BCNF: თითოეული დეტერმინანტი გასაღებია. გამოიყენეთ „ბირთვის“ (payments/gameplay).

პრაქტიკა: გადახდისა და თამაშის საქმიანობის ვერცხლის მოდელები ინახება მინიმუმ 3NF; უფრო მკაცრი BCNF - საცნობარო წიგნებისა და ცხრილებისთვის.

5) დომენის მოდელი (Silver)

5. 1 სახელმძღვანელო წიგნები

`dim. მომხმარებლები '(ფსევდო-ID, ქვეყანა, ასაკობრივი დიაპაზონი, RG სტატუსები).
`dim. თამაშები '(game _ id, provider _ id, ჟანრი, RTP, ცვალებადობა).
`dim. providers '(provider _ id, ტიპი, ლიცენზია).
`dim. ბაზრები '(იურისდიქციის კოდი, რეგულატორი).
`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)

ტიპი I (გადაწერა): ორთოგრაფიული/არაკრიტიკული შესწორებები.
ტიპი II (ისტორია): 'valid _ from/valid _ to/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) შიდა კავშირებისთვის.
ბუნებრივი გასაღებები (მაგალითად, PSP- ის „გადარიცხვა _ id“) - ცალკე მონიტორინგი და შენახვა.
Dedup '(event _ id, წყარო)' ingest + - ზე ბიზნეს გასაღებებში Silver- ში.

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, barkets, currencies).
DQ წამყვანები: 'in _ set', FK ბმულები, უნიკალურობა, SCD თანმიმდევრულობა.
გარე წყაროებიდან „თხელი“ დიმენსიის ავტომწარმოებელი (თამაშების პროვაიდერები, ქვეყნები, PSP).

10) როდესაც დენორმალიზაცია

• სტაბილური „ფართო“ რეპორტი (GGR, სარისკო ფანჯრები);
• BI მოთხოვნების/დაშბორდის დაჩქარება;
• realtime ფანჯარა (ClickHouse/Pinot) SLA კითხვის ქვეშ.

• სილვერი რჩება ჭეშმარიტების წყარო.
• დენორმალიზებული ველები - გამოთვლილი/კოპირებული ვერცხლისგან; ლოგიკის ვერსია.
• ნებისმიერი დენორმალიზაცია არის დოკუმენტირებული და შემოწმებულია სისწორეზე.

11) მოდელი „ვარსკვლავი“ და „ფიფქია“

ვარსკვლავი: ერთი ფაქტი + ბრტყელი გაზომვები უფრო მარტივი და სწრაფად იკითხება, უფრო ძვირია ჩანაწერი/კოორდინაცია.
ფიფქია: გაზომვები ნორმალიზებულია (დაკავშირებულია საცნობარო წიგნებთან) - ნაკლები დუბლიკატი, უფრო რთული, ვიდრე მოთხოვნები.

რეკომენდაცია: გოლდში უფრო ხშირად არის „ვარსკვლავი“, ვერცხლში - ნორმალიზებული „ფიფქები“.

12) სქემების ევოლუცია (safe changes)

Back compatible: ullable სვეტების დამატება; დროშების ახალი საცნობარო მნიშვნელობები.
Breaking: სახელის შეცვლა/ტიპიზაცია/სემანტიკური ძვრები - მხოლოდ '/v2 'და ორმაგი ჩაწერა მიგრაციის პერიოდისთვის.
კონტრაქტები: JSON/Avro სქემები registry, consumer-tests თავსებადობისთვის.

13) DQ კონტროლი ნორმალიზებისთვის

• გასაღებების უნიკალურობა: „გადარიცხვა _ id“, 'bet _ id'.
• რეფერენდუმის მთლიანობა: FK 'dim.'.
• ვალუტები: 'currence' whitelist- დან, 'fx _ rate _ used' არა NULL, 'amount _ base> = 0'.
• დრო: 'ღონისძიება _ დრო' გონივრულ ფანჯარაში; „მომავალი“ მოვლენების არარსებობა.
• 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) კონფიდენციალურობა და შესაბამისობა

მომხმარებლის ფსევდონიმი სილვერში; რეალურ ID- სთან კავშირი - ცალკეულ დაცულ წრეში.
RLS/CLS და ველების შენიღბვა (ელექტრონული ფოსტის/PAN არ არის ხელმისაწვდომი ანალიტიკაში).
კატალოგების/გასაღებების რეგონალიზაცია, DPO კონტროლი სქემების გაფართოებაზე.

16) დაკვირვება და ხაზები

მონაცემთა ხაზები Bronze - Silver - Gold- დან, ტრანსფორმაციებისა და კონტრაქტების ვერსიები.
მეტრიკა: კომპლექტი, ვალიდაცია, FK შეცდომები, დუბლიკატები, დროულად „ხვრელები“, მოთხოვნის ღირებულება.
ალერტები საცნობარო წიგნების და FX წყაროების დაშლის დროს.

17) RACI

R: მონაცემთა ინჟინერია (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. საცნობარო წიგნების რეესტრი (ბაზრები, ცნობები, პროვაიდერები, თამაშები).

2. Silver მოდელები 'fact. payments`, `fact. bets ',' dim. '(3NF), SCD II' dim. users`.

3. ვალუტის/დროის ნორმალიზაცია, ძირითადი DQ წესები (FK/uniqueness/in _ set).

4. პირველი ოქროს ვიტრინა (GGR Daily) და შედუღების ტესტები.

• SCD გაფართოება, თამაშის მოვლენების დაფარვა, პროვაიდერის კონფორმული მოდელები.
• სქემების თავსებადობა, მიგრაციის სიმულატორი, მეტამონაცემების კატალოგი.
• გასაღებების/წვეულებების ოპტიმიზაცია, კლასტერიზაცია/Z-წესრიგი.

• დენორმალიზაციის პოლიტიკოსები გოლდისთვის, SLA/ღირებულებისთვის; სიბნელე „ვარსკვლავი/ფიფქია“.
• დოკუმენტაციის ავტომატური წარმოება, ხაზის გრაფიკი დაშბორდში.
• რეგიონალური კატალოგები და დაშიფვრის გასაღებები, DR სწავლებები.

19) ხარისხის ჩამწერი

დამტკიცებულია ერთიანი გასაღებები და ცნობარები.

• ვერცხლი 3NF, SCD გამოიყენება „ნელი“ გაზომვებისთვის.
• ვალუტები/დროზონები ნორმალიზებულია; „fx _ source“ ფიქსირდება.
• DQ წესები (FK/uniqueness/range/in _ set) აქტიურია.
• დენორმალიზაცია დოკუმენტირებულია, სისწორის ტესტები გაიარა.
• ახალი/სისრულის ხაზები და მეტრიკები ჩანს დაშბორდებზე.

20) ხშირი შეცდომები და როგორ მოვერიდოთ მათ

PII ნაზავი ანალიტიკაში: გამოყავით მაპინგები, გამოიყენეთ CLS/RLS.
Silver- ის არასაკმარისი ნორმალიზაცია: გამოიწვიოს 3NF, წინააღმდეგ შემთხვევაში, ძვირადღირებული მხარდაჭერა და შედუღების შეცდომები.
FX „ანგარიშის ფაქტზე“: კურსები უნდა იყოს დაფიქსირებული ღონისძიებაზე და არა „რეტროაქტიული“ ნომერი.
საკვანძო გაზომვებისთვის არ არსებობს SCD: RG/KYC/არხების ისტორია იკარგება.
Gold- ის გადაკეთება: ზედმეტი join's - კონტროლირებადი დენორმალიზაცია.
სქემების გაუმჭვირვალე ევოლუცია: გამოიყენეთ registry და consumer-tests.

21) შედეგი

ნორმალიზაცია არის Silver დონის დისციპლინა: ერთიანი გასაღებები და საცნობარო წიგნები, 3NF/BCNF ფაქტებისა და გაზომვებისთვის, სწორი ისტორია (SCD) და დროის/ვალუტის სტანდარტიზაცია. ასეთი „ჩონჩხის“ საშუალებით, ოქროს ფანჯრები პროგნოზირებადი ხდება, მოხსენებები შედარებულია, ხოლო საკუთრების ღირებულება კონტროლდება.