Veri normalleştirme

1) Amaç

Normalleştirme, güncellemelerin kopyalarını ve anomalilerini ortadan kaldırır, tek tip dizinleri ve anahtarları ayarlar, verileri tutarlı ve ucuz hale getirir. Bu, iGaming'de GGR/NGR, AML/RG analizleri, düzenleyici raporlama, antifraud ve ML için kritik öneme sahiptir.

2) Normalleştiğimiz yer

Bronz (ham): normalize değil - adli tıp için olduğu gibi depolama (sadece ekle).
Gümüş (temiz/uygun): temel normalleştirme (3NF/BCNF, dizinler, anahtarlar, SCD).
Altın (servis): hedef vitrinler - okuma/BI için kontrollü denormalizasyon mümkündür.

3) Temel ilkeler

1. Schema-first-All tabloları açık şemalara ve anahtarlara sahiptir.
2. Tek tanımlayıcılar: 'User _ pseudo _ id', 'session _ id', 'game _ id', 'provider _ id', 'transaction _ id'.
3. Ortak dizinler: para birimleri, pazarlar/yargı alanları, KYC/RG durumları, oyun sağlayıcıları, trafik kanalları.
4. Zaman ve para birimi: 'event _ time' (UTC) ve normalize edilmiş 'amount _ base' + 'fx _ source' depolar.
5. Evrim: semantik versiyonlar, sadece "sessiz" molalar olmadan uyumlu değişiklikler.
6. PII minimizasyonu: kullanıcı - pseudo-ID ile; Haritalama ayrı olarak saklanır, erişim kısıtlanır.

4) Normal formlar hızlı

1NF: atomik değerler, sütunlarda diziler yok (diziler - alt tablolar).
2NF-Attributes tüm bileşik anahtara bağlıdır.
3NF: geçişli bağımlılık yok (nitelik yalnızca anahtara bağlıdır).
BCNF: Her determinant bir anahtardır. "Çekirdek" (ödemeler/oyun) için kullanın.

Uygulama: Gümüş ödeme modelleri ve oyun etkinliği en az 3NF tutar; Daha sıkı BCNF - referans kitapları ve referans tabloları için.

5) Referans etki alanı modeli (Gümüş)

5. 1 Referans kitapları

'im. Kullanıcıların (sahte kimlik, ülke, yaş aralığı, RG durumları).
'im. Oyunlar "(game_id, provider_id, tür, RTP, volatilite).
'im. Sağlayıcıların (provider_id, tür, lisans).
'im. Piyasalar '(yargı kodu, düzenleyici).
'im. fx_rates' (tarih, ccy_from, ccy_to, oran, fx_source).

5. 2 Gerçekler (dar olay/işlem tabloları)

'Fiact. Ödemeler "(transaction_id, user_pseudo_id, amount_orig, para birimi, amount_base, piyasa, event_time, psp_ref, yöntem).
'Fiact. Bahisler (bet_id, user_pseudo_id, game_id, stake_base, stake_ccy, sonuç, event_time).
'Fiact. Ödemeler "(payout_id, user_pseudo_id, game_id, amount_base, event_time).

Bağlantılar: sabit anahtarlarla ilgili gerçekler ↔ kılavuzlar. Tüm tutarları "kaynak para birimi've" temel "cinsinden çoğaltıyoruz (amount_base), 'fx _ source' sabitleniyor.

6) Yavaşça değişen ölçümler (SCD)

Tip I (üzerine yazma): yazım denetimi/kritik olmayan düzeltmeler.
Tip II (geçmiş): 'Valid _ from/valid _ to/is _ current', denetim değişiklikleri (örneğin, RG durum değişiklikleri).
Tip III (alternatif sütun): Kısa karşılaştırmalar için'önce/sonra ".

Öneri: RG/KYC/pazarlama kanalı için - SCD II; Oyun referans kitapları (RTP) için - etki doğrulama ile 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) Veri Tekilleştirme ve Anahtarlar

İç bağlantılar için vekil anahtarlar (BIGINT/UUID).
Doğal anahtarlar (örneğin, PSP'den 'transaction _ id') - ayrı olarak doğrulanır ve saklanır.
'(event_id, kaynak)'ile Dedup, Silver'daki iş anahtarlarına göre + almak için.

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) Para birimi standardizasyonu ve saat dilimleri

'event _ time' - her zaman UTC; Mağaza vitrinleri için, pazarın yerel ayarını/saat dilimini ekleyin.
Para birimleri: 'Amount _ orig've' amount _ base '(örneğin, EUR) +' fx _ source ',' fx _ rate _ used '.
Kursların günlük sabitlenmesi: Kaynak ve hash imzası ile 'loş 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) Referans kitaplarının tutarlılığı

Birleşik dizin kaydı (oyunlar, sağlayıcılar, pazarlar, para birimleri).
DQ doğrulayıcıları: 'In _ set', FK referansları, benzersizlik, SCD'nin tutarlılığı.
Dış kaynaklardan (oyun sağlayıcıları, ülkeler, PSP)'ince "dimenciaların otomatik olarak oluşturulması.

10) Ne zaman denormalize edilmeli

• Stabil "geniş" raporlar (GGR, risk vitrinleri);
• BI sorgularının/gösterge tablolarının hızlandırılması
• SLA okumaları altında gerçek zamanlı vitrinler (ClickHouse/Pinot).

• Gümüş gerçeğin kaynağı olmaya devam ediyor.
• Denormalize alanlar - Gümüş'ten hesaplanır/kopyalanır; sürüm mantığı.
• Herhangi bir denormalizasyon belgelenir ve doğruluk için test edilir.

11) Yıldız ve kar tanesi modeli

Yıldız: bir gerçek + düz ölçümler - daha kolay ve daha hızlı okuma, daha pahalı yazma/eşleştirme.
Snowflake: ölçümler normalleştirilir (bağlı alt dizinler) - daha az kopya, daha karmaşık sorgular.

Öneri: Altın daha sık "yıldız", Gümüş - normalize "kar taneleri".

12) Şemaların evrimi (güvenli değişiklikler)

Geri uyumlu: Geçersiz sütunlar ekleme; Bayraklarla yeni referans değerleri.
Kırılma: Yeniden adlandırma/yazma/anlamsal kaymalar - yalnızca'/v2've geçiş dönemi için çift giriş yoluyla.
Sözleşmeler: Kayıt defterinde JSON/Avro şemaları, uyumluluk için tüketici testleri.

13) Normalleştirme için DQ kontrolleri

• Anahtarlar benzersizdir: 'transaction _ id', 'bet _ id'.
• Referans bütünlüğü: FK üzerinde'dim. '.
• Para birimleri: Beyaz listeden 'para birimi', 'fx _ rate _ used' NULL değil ',' amount _ base> = 0 '.
• Zaman: Makul bir pencerede'olay _ zaman "; "Gelecek" olayları yok.
• SCD-correct: çakışmayan aralıklar 'valid _ from/valid _ to'.

14) SQL modellerine örnekler

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) Gizlilik ve uyumluluk

Silver'da bir kullanıcıyı aliasing; Gerçek kimlikle bağlantı - ayrı bir korumalı devrede.
RLS/CLS ve alan maskeleme (analitikte e-posta/PAN kullanılamaz).
Dizinlerin/anahtarların bölgeselleştirilmesi, şema uzantısı için DPO kontrolü.

16) Gözlemlenebilirlik ve soy

Bronzdan Veri soyu - Gümüş - Altın, dönüşümlerin ve sözleşmelerin versiyonu.
Metrikler: eksiksizlik, geçerlilik, FK hataları, kopyalar, zamandaki "delikler", talep maliyeti.
Dizinlerdeki ve FX kaynaklarındaki molalarda uyarılar.

17) RACI

R: Veri Mühendisliği (Gümüş/Altın modeller), Veri Platformu (devre kaydı, DQ).
A: Veri/Mimari Başkanı.
C: Uyum/DPO (PII/tutma), Finans (FX/GGR), Risk (RG/AML).
I: BI/Ürün/Pazarlama/Operasyonlar.

18) Uygulama Yol Haritası

1. Dizin kaydı (piyasalar, para birimleri, sağlayıcılar, oyunlar).

2. Gümüş modeller gerçeği. Ödemeler ',' gerçek. Bahisler ',' dim. '(3HF), SCD II for'im. kullanıcıların.

3. Para birimi normalleştirme/saat dilimi, temel DQ kuralları (FK/uniqueness/in_set).

4. İlk Altın Vitrin (GGR Daily) ve mutabakat testleri.

• SCD'nin genişletilmesi, oyun etkinliklerinin kapsamı, sağlayıcı konformal modelleri.
• Şema uyumluluğu ototestleri, geçiş simülatörü, meta veri kataloğu.
• Anahtar/parti optimizasyonu, kümeleme/Z sırası.

• Altın, SLA/değer için denormalizasyon politikaları; Yıldız/kar tanesi şablonları.
• Otomatik belge oluşturma, panolarda soy grafiği.
• Bölgesel dizinler ve şifreleme anahtarları, DR egzersizleri.

19) Kalite kontrol listesi

• Tek tuşlar ve dizinler onaylanmıştır.
• Gümüş 3NF, SCD "yavaş" ölçümlere uygulanır.
• Para birimleri/zaman dilimleri normalleştirilir; 'fx _ source' sabittir.
• DQ kuralları (FK/uniqueness/range/in_set) aktiftir.
• Denormalizasyonlar belgelendi, doğruluk testleri geçti.
• Linaj ve tazelik/dolgunluk ölçümleri panolarda görülebilir.

20) Sık yapılan hatalar ve bunlardan nasıl kaçınılacağı

Analitikte PII harmanlama: ayrı eşlemeler, CLS/RLS kullanın.
Gümüşün yetersiz normalleştirilmesi: 3NF, aksi takdirde pahalı destek ve uzlaşma hatalarına yol açar.
FX "rapor başına": Oranlar "geçmişe dönük'değil, bir olayda yakalanmalıdır.
Önemli boyutlar için SCD yok: kayıp RG/KYC/kanal geçmişi.
Altın renormalizasyon: yedekli birleştirmeler - yönetilen denormalizasyon.
Şemaların opak evrimi: kayıt defteri ve tüketici testleri kullanın.

21) Alt satır

Normalleştirme Gümüş seviyeli bir disiplindir: tekdüze anahtarlar ve referans kitapları, gerçekler ve ölçümler için 3NF/BCNF, doğru tarih (SCD) ve zaman/para birimlerinin standardizasyonu. Böyle bir "iskelet'ile Altın durumları öngörülebilir hale gelir, raporlar karşılaştırılabilir ve sahip olma maliyeti kontrol edilir.