Normalizar dados

1) Destino

A normalização elimina duplicações e anomalias de atualizações, especifica guias e chaves unificados, torna os dados coerentes e baratos acompanhados. Em iGaming, isso é crítico para a GGR/NGR, AML/RG, relatórios regulatórios, antifrode e ML.

2) Onde normalizável

Bronze (raw): Não é normalizável - armazenamento como é (append-only) para forensica.
Silver (clean/conform): normalização básica (3NF/BCNF, guias, chaves, SCD).
Gold (serve): Vitrines de destino - pode ser gerenciada sob leitura/BI.

3) Princípios básicos

1. Schema-first: Todas as tabelas têm esquemas e chaves explícitos.
2. Identificadores unificados: 'user _ pseudo _ id', 'direction _ id', 'game _ id', 'provider _ id', 'direction _ id'.
3. Guias unificados: moedas, mercados/jurisdições, estatais KYC/RG, provedores de jogos, canais de tráfego.
4. Hora e moeda: armazenar 'event _ time' (UTC) e normalizado 'amount _ base' + 'fx _ fonte'.
5. Evolução: versões semânticas, apenas alterações compatíveis sem quebras silenciosas.
6. Minimização PII: Usuário - via pseudo-ID; o mapping é mantido separadamente, e o acesso é limitado.

4) Formas normais rapidamente

1NF: valores atômicos, sem matrizes em colunas (matrizes → tabelas child).
2NF: os atributos dependem de toda a chave composta.
3NF: nenhuma dependência transitiva (o atributo depende apenas da chave).
BCNF: Cada determinante é a chave. Aplicar para «núcleo» (payments/gamplay).

Prática: Modelos silver de pagamento e atividade de jogos mantendo um mínimo de 3NF; BCNF mais rigoroso - para guias e tabelas de referência.

5) Modelo de domínio de referência (Silver)

5. 1 Guias

`dim. users '(pseudo-ID, país, faixa etária, estados RG).
`dim. games '(game _ id, provider _ id, gênero, RTP, volatilidade).
`dim. providers '(provider _ id, tipo, licença).
`dim. markets '(código de jurisdição, regulador).
`dim. fx_rates` (date, ccy_from, ccy_to, rate, fx_source).

5. 2 Factos (tabelas estreitas de eventos/transações)

`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).

Factos ↔ guias de chaves estáveis. Todos os valores são duplicados em «moeda de origem» e «base de base» (amount _ base), registrando «fx _ fonte».

6) Dimensões em mudança lenta (SCD)

Tipo I (reiniciar): correções ortográficas/não-ríticas.
Tipo II (histórico): 'valid _ from/valid _ to/is _ current', auditoria de alterações (por exemplo, mudança de status RG).
Tipo III (coluna alternativa): «antes/depois» para comparações curtas.

Recomendação: para RG/KYC/canal de marketing - SCD II; para guias de jogos (RTP) - SCD II com validação de influência.

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) Deduplicação e chaves

Chaves de aluguel (BIGINT/UUID) para ligações internas.
As chaves naturais (por exemplo, 'direction _ id' do PSP) são validadas e armazenadas separadamente.
Deadup por '(event _ id, fonte)' em ingest + em chaves de negócio no 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) Normalização de moedas e timeson

'event _ time' é sempre UTC; para as vitrines adicionamos local/timeson do mercado.
Moedas: 'amount _ orig' e 'amount _ base' (EUR, por exemplo) + 'fx _ fonte', 'fx _ rate _ used'.
A fixação diária dos cursos é 'dim. fx _ rates 'com origem e assinatura hash.

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) Consistência de guias

Um único registro de guias (games, providers, markets, currencies).
Validadores DQ: 'in _ set', referências FK, exclusividade, não conformidade SCD.
Geração automática de dimensas «finas» de fontes externas (provedores de jogos, países, PSP).

10) Quando denormalizar

• Reportes estáveis «amplos» (GGR, vitrines de risco);
• acelerar as pesquisas BI/dashboards;
• realtime-vitrine (ClickHouse/Pinot) sob a leitura SLA.

• A fonte da verdade continua a ser Silver.
• Campos denormalizados - computáveis/copiados de Silver; versionização da lógica.
• Toda a desnormalização é documentada e testada como correta.

11) Modelo «estrela» e «floco de neve»

Estrela: um dado + medidas planas - leitura mais fácil e mais rápida, escrita mais cara/concordância.
Flocos de neve: medidas normalizadas (sob-guias) - menos duplicados, mais complexos pedidos.

Recomendação: a Gold tem mais «estrela» e a Silver tem «flocos de neve» normalizados.

12) Evolução dos circuitos (safe changes)

Back-composto: adição de colunas nullable; novos valores de referência com bandeiras.
Breaking: renomear/mudar de tipo/deslocamento semântico - apenas através de '/v2 'e gravar duplamente para o período de migração.
Contratos: JSON/Avro esquema em registry, consumer-testes de compatibilidade.

13) Controladores DQ para normalização

• A exclusividade das chaves é 'trans _ id', 'bet _ id'.
• Integridade arbitral: FK em 'dim'.
• Moedas: 'currency' de whitelist, 'fx _ rate _ used' não NULL, 'amount _ base> = 0'.
• Hora: 'event _ time' em uma janela inteligente; não há eventos futuros.
• Correto SCD: faixas não cruzadas 'valid _ from/valid _ to'.

14) Exemplos de modelos 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) Privacidade e complacência

Pseudônimo do usuário no Silver; ligações com a ID real - em um circuito protegido separado.
RLS/CLS e camuflagem de campos (e-mail/PAN não estão disponíveis no analista).
Regionalização de diretórios/chaves, controle DPO para extensão de circuitos.

16) Observabilidade e lineagem

Data lineage do Bronze → Silver → Gold, versões de transformações e contratos.
Métricas: completeness, validity, erros FK, duplicados, «buracos» no tempo, custo de consulta.
Alertas para quebras de guias e fontes FX.

17) RACI

R: Data Engineering (modelos Silver/Gold), Data Plate (minúsculas de esquema, DQ).
A: Head of Data/Architecture.
C: Compliance/DPO (PII/retention), Finance (FX/GGR), Risk (RG/AML).
I: BI/Produto/Marketing/Operações.

18) Mapa de trânsito de implementação

1. Registro de guias (markets, currencies, providers, games).

2. Modelos Silver 'fact'. payments`, `fact. bets ',' dim '(3NF), SCD II para' dim. users`.

3. Normalização das moedas/temporizão, regras DQ básicas (FK/uniqueness/in _ set).

4. Primeira vitrine Gold (GGR Daily) e testes de suavidade.

• Extensão SCD, cobertura de eventos de jogos, modelos de conectividade de provedor.
• Auto de compatibilidade de circuito, simulador de migração, catálogo de metadados.
• Otimizar chaves/partituras, clusterizar/Z-order.

• Políticas de denormização para Gold, SLA/custo; Templates estrela/flocos de neve.
• Documentação automática, grafo lineage em dashboards.
• Diretórios regionais e chaves de criptografia, ensinamentos Dr.

19) Folha de cheque de qualidade

• Chaves e guias unificados foram aprovados.
• Silver em 3NF, SCD aplicado a medidas «lentas».
• As moedas/temporizões estão normalizadas; 'fx _ fonte' é fixado.
• As regras DQ (FK/uniqueness/range/in _ set) estão ativas.
• As denormalizações foram documentadas e os testes de correção foram concluídos.
• A linha e as métricas de frescura/completura são visíveis nos dashboards.

20) Erros frequentes e como evitá-los

Mistura de PII no analista: separe os muppings, aplique o CLS/RLS.
Falta de normalização do Silver: leve a 3NF, senão suporte caro e erros de fusão.
FX «de acordo com o relatório»: os cursos devem ser registrados em um evento e não em um número «retroativo».
Sem SCD para dimensões essenciais: perde-se o histórico RG/KYC/canais.
Reordenação Gold: join's em excesso → desnormalização controlada.
Evolução opaca dos circuitos: use registry e consumer-tests.

21) Total

A normalização é uma disciplina de nível Silver: chaves e guias unificados, 3NF/BCNF para factos e medições, histórico correto (SCD) e normalização de tempo/moedas. Com este «esqueleto», as vitrines Gold tornam-se previsíveis, os relatórios são comparáveis e o custo de propriedade é controlado.