データの正規化

1）目的

正規化は、アップデートの重複や異常を排除し、均一なディレクトリとキーを設定し、データを一貫して安価に維持します。iGamingでは、これはGGR/NGR、 AML/RG分析、規制報告、不正防止、およびMLにとって重要です。

2）私達が正常化するところ

ブロンズ（raw）：正規化されていない-フォレンジックの保管（追加のみ）。
銀（クリーン/適合）：基本正規化（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最小化：ユーザー-疑似ID；マッピングは個別に保存され、アクセスは制限されます。

4）正常な形態すぐに

1NF： atomic values、カラム内の配列なし（arrays→child-tables）。
2NF-Attributesは複合キー全体に依存します。
3NF：トランジティブな依存関係はありません（属性はキーにのみ依存します）。
BCNF：各行列式はキーです。「カーネル」（支払い/ゲームプレイ）に使用します。

練習：支払およびゲーム活動の銀モデルは少なくとも3NFを保ちます；より厳格なBCNF-参照帳と参照テーブル用。

5）参照ドメインモデル（シルバー）

5.1参考書

「ディム」ユーザー（疑似ID、国、年齢、RGステータス）。
「ディム」ゲーム'（game_id、 provider_id、ジャンル、RTP、ボラティリティ）。

「ディム」providers '（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、方法）。
「ファクト」ベット'（bet_id、 user_pseudo_id、 game_id、 stake_base、 stake_ccy、結果、event_time)。
「ファクト」ペイアウト'（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（代替カラム）：「before/after」短い比較のため。

推奨事項：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）参考書の一貫性

統合されたディレクトリレジスタ（ゲーム、プロバイダー、市場、通貨）。
DQバリデータ：'in_set'、 FK参照、一意性、SCDの一貫性。
外部ソース（ゲームプロバイダ、国、PSP）からの「薄い」ディメンシアの自動生成。

10）いつデノーマライズするか

• 安定した「ワイド」レポート（GGR、リスクショーケース）；
• BIクエリ/ダッシュボードの高速化
• リアルタイムストアフロント（ClickHouse/Pinot）をSLA値で読み込みます。

• 銀は真実の源のままです。
• 非正規化フィールド-シルバーから計算/コピー。バージョン管理ロジック。
• すべての非正規化は文書化され、正しさのためにテストされます。

11）星と雪片モデル

スター：1つの事実+平らな測定-読みやすく、より速く、より高価な書き込み/マッチング。
Snowflake：測定は正規化されます（接続されたサブディレクトリ）-重複が少なく、複雑なクエリが少なくなります。

推奨事項：ゴールドでは、より頻繁に「星」、シルバーで-正規化された「雪片」。

12）スキームの進化（安全な変更）

バックコンパチブル：nullable列の追加；フラグ付きの新しい参照値。
破損：名前の変更/入力/セマンティックシフト-移行期間の'/v2'とダブルエントリのみ。
契約：レジストリ内のJSON/Avroスキーム、互換性のための消費者テスト。

13）正規化のためのDQ制御

• キーは一意です：'transaction_id'、 'bet_id'。
• 参照整合性：'dim'のFK。
• 通貨：ホワイトリストの'currency'、 'fx_rate_used' Not NULL、 'amount_base>=0'。
• 時間：合理的なウィンドウ内の'event_time'；「未来」の出来事はありません。
• SCD-correct：重複しない範囲'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とフィールドマスキング（電子メール/PANは分析では利用できません）。
ディレクトリ/キーの地域化、スキーマ拡張のためのDPO制御。

16）観察可能性と血統

ブロンズ→シルバー→ゴールド、変換と契約のバージョンからのデータ系統。
メトリクス：完全性、有効性、FKエラー、重複、時間内の「穴」、リクエストコスト。
ディレクトリとFXソースのブレーク時のアラート。

17） RACI

R：データエンジニアリング（シルバー/ゴールドモデル）、データプラットフォーム（回路レジスタ、DQ）。
A：データ・アーキテクチャ担当。
C： コンプライアンス/DPO （PII/リテンション）、ファイナンス（FX/GGR）、リスク（RG/AML）。
I： BI/プロダクト/マーケティング/操作。

18）実装ロードマップ

1.ディレクトリ登録（市場、通貨、プロバイダー、ゲーム）。

2.シルバーモデルの事実。支払いは事実だ。'bets'、' dim。 '（3HF）、' dim'のSCD II。「ユーザー」

3.通貨正規化/タイムゾーン、基本的なDQルール（FK/uniqueness/in_set)。

4.最初のゴールドショーケース（GGRデイリー）と和解テスト。

• SCDの拡張、ゲームイベントのカバレッジ、プロバイダのコンフォーマルモデル。
• スキーマ互換性オートテスト、移行シミュレータ、メタデータカタログ。
• キー/パーティの最適化、クラスタリング/Z順序。

• Gold、 SLA/valueの非正規化ポリシー;星/雪片テンプレート。
• ドキュメントの自動生成、ダッシュボードのリネージグラフ。
• 地域のディレクトリと暗号化キー、DR演習。

19）質のチェックリスト

• シングルキーとディレクトリが承認されます。
• 3NFの銀、SCDは「遅い」測定に適用しました。
• 通貨/タイムゾーンは正規化されます。'fx_source'が修正されました。
• DQルール （FK/uniqueness/range/in_set）がアクティブです。
• Denormalizationsが文書化され、正しさテストが合格しました。
• リネージと鮮度/フルネスの指標がダッシュボードに表示されます。

20）頻繁なミスとそれらを回避する方法

アナリティクスでのPIIブレンド：個別のマッピング、CLS/RLSを使用します。
シルバーの正規化が不十分：3NF、それ以外の場合は高価なサポートと和解のエラーにつながります。
FX「レポートあたり」：レートは「バックデート」ではなく、イベントでキャプチャする必要があります。
キー寸法のSCDがありません：RG/KYC/チャンネル履歴が失われました。
ゴールド・ルノーマライゼーション：冗長結合→管理されたルノーマライゼーション。
スキームの不透明な進化：レジストリと消費者テストを使用します。

21）ボトムライン

正規化はシルバーレベルの規律です：均一なキーと参考書、事実と測定のための3NF/BCNF、正しい履歴（SCD）と時間/通貨の標準化。このような「スケルトン」により、ゴールドケースは予測可能になり、レポートは比較可能であり、所有コストは制御されます。