Normalización de datos

1) Asignación

La normalización elimina duplicados y anomalías de actualizaciones, establece referencias y claves únicas, hace que los datos sean consistentes y baratos acompañados. En iGaming, esto es crítico para GGR/NGR, análisis AML/RG, informes regulatorios, antifraude y ML.

2) Donde normalizamos

Bronze (raw): no normalizamos - el almacenamiento tal como es (append-only) para los forensics.
Silver (clean/amb): normalización básica (3NF/BCNF, guías, llaves, SCD).
Gold (serve): vitrinas de destino: la desnormalización controlada bajo lectura/BI es posible.

3) Principios básicos

1. Schema-first: todas las tablas tienen esquemas y claves explícitos.
2. Identificadores únicos: 'user _ pseudo _ id', 'session _ id', 'game _ id', 'provider _ id', 'transaction _ id'.
3. Referencias únicas: monedas, mercados/jurisdicciones, estados KYC/RG, proveedores de juegos, canales de tráfico.
4. Tiempo y moneda: almacenar 'event _ time' (UTC) y 'amount _ base' + 'fx _ source' normalizado.
5. Evolución: versiones semánticas, sólo cambios compatibles sin roturas «silenciosas».
6. PII-minimización: usuario - a través de pseudo-ID; el mapping se almacena por separado, el acceso está restringido.

4) Formas normales rápidamente

1NF: valores atómicos, sin matrices en columnas (matrices → tablas child).
2NF: los atributos dependen de toda la clave compuesta.
3NF: no hay dependencias transitivas (el atributo depende sólo de la clave).
BCNF: cada determinante es una clave. Aplicar para «kernel» (payments/gameplay).

Práctica: Los modelos de pago Silver y la actividad de juego mantienen un mínimo de 3NF; BCNF más riguroso - para guías y tablas de referencia.

5) Modelo de dominio de referencia (Silver)

5. 1 Manuales

`dim. users '(pseudo-ID, país, rango de edad, estados RG).
`dim. games '(game_id, provider_id, género, RTP, volatilidad).
`dim. providers '(provider_id, tipo, licencia).
`dim. markets '(código de jurisdicción, regulador).
`dim. fx_rates` (date, ccy_from, ccy_to, rate, fx_source).

5. 2 Hechos (tablas de eventos/transacciones estrechas)

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

Conexiones: hechos ↔ guías de claves estables. Todas las cantidades se duplican en «moneda de origen» y en «base» (amount_base), fijando 'fx _ source'.

6) Mediciones de cambio lento (SCD)

Tipo I (sobrescribir): correcciones ortográficas/no críticas.
Tipo II (historial): 'valid _ from/valid _ to/is _ current', auditoría de cambios (por ejemplo, cambio de estado RG).
Tipo III (columna alternativa): «antes/después» para comparaciones cortas.

Recomendación: para el canal de comercialización RG/KYC - SCD II; para guías de juegos (RTP) - SCD II con validación de influencia.

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) Desduplicación y claves

Llaves sustitutas (BIGINT/UUID) para conexiones internas.
Claves naturales (por ejemplo, 'transaction _ id' de PSP) - validar y almacenar por separado.
Dedoup por '(event_id, source)' en ingest + por claves de negocio en 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) Estandarización de monedas y temporización

'event _ time' - siempre UTC; para los escaparates añadimos el local/el timzone del mercado.
Monedas: 'amount _ orig' y 'amount _ base' (por ejemplo, EUR) + 'fx _ source', 'fx _ rate _ used'.
Fijación diaria de los cursos: 'dim. fx_rates' con la fuente y la firma 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) Consistencia de las guías

Registro único de referencias (games, providers, markets, currencies).
Validadores DQ: 'in _ set', referencias FK, singularidad, consistencia SCD.
Autogeneración de dimensiones «sutiles» de fuentes externas (proveedores de juegos, países, PSP).

10) Cuándo desnormalizar

• informes «amplios» estables (GGR, escaparates de riesgo);
• aceleración de las solicitudes BI/dashboards;
• realtime-showcase (ClickHouse/Pinot) bajo lectura SLA.

• La fuente de la verdad sigue siendo Silver.
• Campos denormalizados: calculados/copiados de Silver; versionar la lógica.
• Cualquier desnormalización está documentada y probada para ser correcta.

11) Modelo «estrella» y «copo de nieve»

Estrella: un hecho + medidas planas - lectura más fácil y más rápida, más costosa escritura/negociación.
Copo de nieve: las mediciones están normalizadas (sub-guías conectadas) - menos duplicados, las consultas más difíciles.

Recomendación: en Oro es más común «estrella», en Plata son «copos de nieve» normalizados.

12) Evolución de los esquemas (cambios seguros)

Back-compatible: agregar columnas nullables; nuevos valores de referencia con banderas.
Breaking: cambio de nombre/cambio de tipo/cambios semánticos - sólo a través de '/v2 'y entrada doble para el período de migración.
Contratos: JSON/Avro esquemas en registry, consumer-tests de compatibilidad.

13) Controles DQ para la normalización

• Singularidad de las claves: 'transaction _ id', 'bet _ id'.
• Integridad referencial: FK en 'dim.'.
• Monedas: 'currency' de whitelist, 'fx _ rate _ used' no NULL, 'amount _ base> = 0'.
• Tiempo: 'event _ time' en una ventana razonable; ausencia de eventos «futuros».
• Corrección SCD: rangos no intersectoriales 'valid _ from/valid _ to'.

14) Ejemplos 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) Privacidad y cumplimiento

Alias de usuario en Silver; Vínculos con ID real: en un bucle seguro independiente.
RLS/CLS y enmascaramiento de campos (e-mail/PAN no están disponibles en analítica).
Regionalización de directorios/claves, control DPO para la expansión de esquemas.

16) Observabilidad y lineaje

Línea de datos de Bronze → Silver → Gold, una versión de transformaciones y contratos.
Métricas: completeness, validity, errores FK, duplicados, «agujeros» en el tiempo, costo de consulta.
Alertas en roturas de referencia y fuentes FX.

17) RACI

R: Ingeniería de datos (modelos Silver/Gold), Plataforma de datos (registro de circuitos, DQ).
A: Head of Data/Architecture.
C: Compliance/DPO (PII/retention), Finance (FX/GGR), Risk (RG/AML).
I: BI/Producto/Marketing/Operaciones.

18) Hoja de ruta para la implementación

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

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

3. Normalización de monedas/timeson, reglas DQ básicas (FK/uniqueness/in_set).

4. El primer escaparate de oro (GGR Daily) y pruebas de soldadura.

• Expansión de SCD, cobertura de eventos de juegos, modelos conformes de proveedores.
• Autotestas de compatibilidad de esquemas, simulador de migración, catálogo de metadatos.
• Optimización de claves/lotes, clustering/Z-order.

• Políticas de desnormalización para Gold, SLA/costo; templates «estrella/copo de nieve».
• Documentación de autogeneración, grafo lineage en dashboards.
• Catálogos regionales y claves de cifrado, ejercicios de DR.

19) Check-list de calidad

• Se han aprobado claves y guías únicas.
• Silver en 3NF, SCD se aplican a mediciones «lentas».
• Las monedas/zonas de tiempo se normalizan; 'fx _ source' se fija.
• Las reglas DQ (FK/uniqueness/range/in_set) están activas.
• Las denormalizaciones están documentadas, se han realizado las pruebas de corrección.
• Los linajes y métricas de frescura/plenitud son visibles en los dashboards.

20) Errores frecuentes y cómo evitarlos

Mezcla de PII en análisis: separe los muppings, aplique CLS/RLS.
Falta de normalización de Silver: conduce a 3NF, de lo contrario costoso soporte y errores de soldadura.
FX «por el hecho del informe»: los cursos deben registrarse en el evento y no «retroactivamente».
No hay SCD para las mediciones clave: se pierde el historial de RG/KYC/canales.
Reordenación Gold: exceso de join's → denormalización controlada.
Evolución opaca de los esquemas: utilice el registro y las pruebas de consumo.

21) Resultado

La normalización es una disciplina de nivel Silver: claves y guías únicas, 3NF/BCNF para hechos y medidas, historial correcto (SCD) y estandarización de tiempo/monedas. Con este «esqueleto», las vitrinas de oro se vuelven predecibles, los informes son comparables y el costo de propiedad se controla.