نرمال سازی داده ها

1) هدف

Normalization تکراری و ناهنجاری های به روز رسانی را حذف می کند، دایرکتوری ها و کلید های یکنواخت را تنظیم می کند، داده ها را برای نگهداری سازگار و ارزان می کند. در iGaming، این برای تجزیه و تحلیل GGR/NGR، AML/RG، گزارش نظارتی، ضد تقلب و ML بسیار مهم است.

2) جایی که ما عادی می کنیم

برنز (خام): عادی نیست - ذخیره سازی به عنوان (فقط ضمیمه) برای پزشکی قانونی.
نقره ای (تمیز/مطابق): عادی سازی اولیه (3NF/BCNF، دایرکتوری ها، کلید ها، SCD).
طلا (خدمت): فروشگاه های هدف - کنترل شده برای خواندن/BI امکان پذیر است.

3) اصول اساسی

1. Schema-first-All جداول دارای طرح و کلید صریح هستند.
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: کاربر - از طریق شبه شناسه ؛ نقشه برداری به طور جداگانه ذخیره می شود، دسترسی محدود است.

4) فرم های عادی به سرعت

1NF: مقادیر اتمی، بدون آرایه در ستون (آرایه → جداول کودک).
2NF-Attributes به کل کلید ترکیبی بستگی دارد.
3NF: هیچ وابستگی متعدی وجود ندارد (attribute فقط به کلید بستگی دارد).
BCNF: هر تعیین کننده یک کلید است. استفاده برای «هسته» (پرداخت/گیم پلی).

تمرین: مدل های نقره ای پرداخت ها و فعالیت های بازی حداقل 3NF را حفظ می کنند ؛ BCNF دقیق تر - برای کتاب های مرجع و جداول مرجع.

5) مدل دامنه مرجع (نقره ای)

5. 1 کتاب مرجع

من هستم. کاربران (شبه شناسه، کشور، محدوده سنی، وضعیت RG).
من هستم. بازی ها (game_id، provider_id، ژانر، RTP، نوسانات).
من هستم. ارائه دهندگان (provider_id، نوع، مجوز).
من هستم. بازارها (کد صلاحیت، تنظیم کننده).

من هستم. (تاریخ، ، ، نرخ،

5. 2 حقایق (جداول رویداد/معامله باریک)

درست است. پرداخت ( ، ، ، ارز، بازار، بازار).

درست است. شرط ها ( ، ، ، نتیجه، نتیجه)

درست است. پرداخت ( ، ، ،.

لینک ها: حقایق ↔ راهنمایی در مورد کلید های پایدار. ما تمام مقادیر را در «ارز منبع» و در «پایه» (amount_base) اصلاح «fx _ source» تکرار می کنیم.

6) به آرامی تغییر اندازه گیری (SCD)

نوع I (بازنویسی): اصلاحات املایی/غیر انتقادی.
نوع دوم (تاریخچه): '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) برای لینک های داخلی.
کلید طبیعی (به عنوان مثال، 'transaction _ id' از PSP) - به اعتبار و ذخیره شده به طور جداگانه.
Dedup توسط «(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; برای پنجره های فروشگاه، منطقه/منطقه زمانی بازار را اضافه کنید.
ارزها: 'amount _ orig' و 'amount _ 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/داشبورد
• فروشگاه های زمان واقعی (ClickHouse/Pinot) تحت خواندن SLA.

• نقره منبع حقیقت است.
• زمینه های Denormalized - محاسبه/کپی از نقره ؛ منطق نسخه.
• هر denormalization مستند شده و برای صحت تست شده است.

11) مدل ستاره و برف ریزه

ستاره: یک واقعیت + اندازه گیری مسطح - خواندن آسان تر و سریع تر، نوشتن/تطبیق گران تر.
Snowflake: اندازه گیری ها نرمال می شوند (زیر شاخه های متصل) - تکراری کمتر، پرس و جوهای پیچیده تر.

توصیه: در طلا اغلب «ستاره»، در نقره - نرمال «دانه های برف».

12) تکامل طرح (تغییرات امن)

برگشت سازگار: اضافه کردن ستون nullable ؛ مقادیر مرجع جدید با پرچم.
شکستن: تغییر نام/تایپ کردن/تغییرات معنایی - فقط از طریق «/v2 »و ورود دوگانه برای دوره مهاجرت.
قراردادها: طرح های JSON/Avro در رجیستری، تست های مصرف کننده برای سازگاری.

13) کنترل DQ برای عادی سازی

• کلیدها منحصر به فرد هستند: «transaction _ id»، «bet _ id».
• یکپارچگی مرجع: FK در «کم نور».
• ارزها: «ارز» از لیست سفید، «fx _ rate _ used» not NULL، «مقدار _ پایه> = 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) حفظ حریم خصوصی و انطباق

نام مستعار یک کاربر در نقره ؛ اتصال با شناسه واقعی - در یک مدار محافظت شده جداگانه.
RLS/CLS و فیلد ماسک (ایمیل/PAN در تجزیه و تحلیل در دسترس نیست).
منطقه بندی دایرکتوری ها/کلید ها، کنترل DPO برای توسعه طرح.

16) قابلیت مشاهده و اصل و نسب

اصل و نسب داده ها از برنز → نقره → طلا، نسخه تحولات و قراردادها.
معیارها: کامل بودن، اعتبار، خطاهای FK، تکراری، «سوراخ» در زمان، هزینه درخواست.
هشدار در شکاف در دایرکتوری ها و منابع FX.

17) RACI

R: مهندسی داده (مدل های نقره ای/طلایی)، پلت فرم داده (ثبت مدار، DQ).
A: رئیس داده/معماری.
C: انطباق/DPO (PII/احتباس)، امور مالی (FX/GGR)، ریسک (RG/AML).
I: BI/محصول/بازاریابی/عملیات.

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

1. ثبت نام دایرکتوری (بازارها، ارزها، ارائه دهندگان، بازی ها).

2. حقيقت مدل هاي نقره اي پرداخت، در واقع. شرط '،' کم '. (3HF)، SCD II برای' dim. کاربران.

3. عادی سازی ارز/منطقه زمانی، قوانین اساسی DQ (FK/uniqueness/in_set).

4. اولین نمایشگاه طلا (GGR روزانه) و آزمون آشتی.

• گسترش SCD، پوشش رویدادهای بازی، مدل های سازگار با ارائه دهنده.
• خودکار سازگاری طرح، شبیه ساز مهاجرت، کاتالوگ ابرداده.
• بهینه سازی کلید/حزب، خوشه بندی/Z-منظور.

• سیاست های غیر طبیعی برای طلا، SLA/ارزش ؛ قالب ستاره/برف ریزه.
• تولید خودکار مستندات، نمودار خطی در داشبورد.
• دایرکتوری های منطقه ای و کلید های رمزگذاری، تمرینات DR.

19) چک لیست کیفیت

• کلید های تک و دایرکتوری ها تایید شده است.
• نقره در 3NF، SCD اعمال شده به «آهسته» اندازه گیری.
• ارزها/مناطق زمانی عادی می شوند ؛ «fx _ source» درست شده است.
• قوانین DQ (FK/uniqueness/range/in_set) فعال هستند.
• Denormalizations مستند، آزمون صحت گذشت.
• معیارهای linage و freshness/fullness در داشبورد قابل مشاهده است.

20) اشتباهات مکرر و چگونگی اجتناب از آنها

ترکیب PII در تجزیه و تحلیل: نقشه های جداگانه، از CLS/RLS استفاده کنید.
عادی سازی ناکافی نقره: منجر به 3NF، در غیر این صورت پشتیبانی گران قیمت و اشتباهات آشتی.
FX «در هر گزارش»: نرخ ها باید در یک رویداد گرفته شوند، نه «عقب افتاده».
بدون SCD برای ابعاد کلیدی: تاریخچه RG/KYC/کانال را از دست داد.
renormalization طلا: افزونگی → denormalization مدیریت می شود.

تکامل مبهم طرح ها: استفاده از رجیستری و تست های مصرف کننده

21) خط پایین

عادی سازی یک رشته نقره ای است: کلید های یکنواخت و کتاب های مرجع، 3NF/BCNF برای حقایق و اندازه گیری ها، تاریخ صحیح (SCD) و استاندارد سازی زمان/ارز. با چنین «اسکلتی»، موارد طلا قابل پیش بینی می شوند، گزارش ها قابل مقایسه هستند و هزینه مالکیت کنترل می شود.