Trabajar con datos históricos

1) Nombramiento y principios

Objetivo: almacenar y procesar estados pasados para que los informes, modelos e investigaciones sean reproducibles, precisos y complacientes.

• Time-aware by design: patrones de tiempo explícitos en diagramas y consultas.
• Reproducibilidad: el mismo informe de fecha D siempre da el mismo resultado.
• Auditabilidad: origen probado (lineage), capas inmutables, WORM donde sea necesario.
• Coste-aware: capas de archivo, compresión, cold storage con SLAs comprensibles.
• Privacidad por diseño: gestión de PII en operaciones retrospectivas y solicitudes legales.

2) Modelos de tiempo

Event-time: tiempo del evento real (apuesta, depósito).
Tiempo de procesamiento: cuando el sistema ha procesado la entrada (puede variar).
Bitemporal: almacenamiento de información y tiempo de procesamiento para revisiones retroactivas.
Valores-intervalos: 'valid _ from', 'valid _ to', 'is _ current'.
As-of queries: muestra de datos «como se sabía en el momento T».

sql event_time TIMESTAMP, -- event time processed_at TIMESTAMP, -- TIMESTAMP valid_from processing time, -- start of version validity valid_to TIMESTAMP, -- end of validity (NULL if current)
is_current   BOOLEAN

3) Capas de almacenamiento y formatos

Lakehouse: Bronze (raw append-only) → Silver (clean/SCD/normalización) → Gold (escaparates).
ACID-форматы: Delta/Iceberg/Hudi (MERGE/Upsert, time-travel, snapshots).
Tiered storage: hot/warm/cold + WORM para artefactos reguladores.
Partición: 'event _ date', 'market', 'tenant'; clustering/Z-order por predicados frecuentes (user/game/provider).

4) Historización de las mediciones (SCD)

SCD I: regrabación - para ediciones no críticas.
SCD II: historia completa; recomendado para RG/KYC/canales de tráfico/atributos de juegos.
SCD III: «antes/después» son casos raros de comparación.

sql
MERGE INTO dim. users_scd t
USING stage. users u
ON t. user_pseudo_id = u. user_pseudo_id AND t. is_current = TRUE
WHEN MATCHED AND (t. rg_status <> u. rg_status OR t. country <> u. country) THEN
UPDATE SET is_current = FALSE, valid_to = CURRENT_TIMESTAMP
WHEN NOT MATCHED THEN
INSERT (user_pseudo_id, country, rg_status, valid_from, valid_to, is_current)
VALUES (u. user_pseudo_id, u. country, u. rg_status, CURRENT_TIMESTAMP, NULL, TRUE);

5) Historia de los hechos: imágenes y bitemporal

Instantáneas (snapshots): instantánea de agregados de fin de día/mes (por ejemplo, balance de billetera): acelera la recreación de informes históricos.
Datos Bitemporales: Registramos el tiempo de evento y el tiempo de procesamiento para distinguir las correcciones tardías de los cálculos retrospectivos.
Exactly-once story: dedoop por 'event _ id' + idempotent MERGE.

6) Tiempo de viaje y reproducibilidad

Tiempo de viaje: lectura de tablas «en el momento T» para depuración, incidentes, soldadura.
Versificación lógica: artefactos de transformación (versiones SQL/DBT, contenedores) y etiquetas «logic_version» en las tablas de salida.
Outputs Frozen: Los artefactos de informe de oro se registran y no se reescriben, hash disponible y registro de exportación.

sql
SELECT
FROM silver. fact_bets VERSION AS OF 1678901234567
WHERE event_date = DATE '2025-10-31';

7) Backfill и Reprocessing

Backfill: carga primaria/dosificación de rango histórico.
Reprocessing: un recuento después de corregir errores o cambiar las reglas de negocio.

• Idempotencia (MERGE/upsert), rangos, cuotas, «ejecución oscura» (dry-run) con comparación de métricas.
• Etiquetamos el resultado: 'recalc _ reason', 'logic _ version', 'reprocessed _ at'.

1. Freeze actual Oro; 2) verificación DLQ/DQ; 3) carrera Silver; 4) comparación de métricas; 5) Reconfiguración Gold; 6) publicación y firma.

8) Conciliaciones de la precisión (reconciliation)

Sumas de comprobación: conciliar las revoluciones/cantidades con OLTP, PSP/proveedores.
Verificación de dos circuitos: pipelina independiente en la muestra (comparación A/B).
Tolerancias: por ejemplo, discrepancia GGR ≤ 0. 2% por día.

sql
-- Duplicates
SELECT transaction_id, COUNT() c
FROM silver. payments
GROUP BY transaction_id
HAVING COUNT() > 1;

-- Unknown Currencies/Markets
SELECT p. currency
FROM silver. payments p
LEFT JOIN ref. currencies r ON r. code = p. currency
WHERE r. code IS NULL;

9) Monedas, tiempo, calendario: corrección histórica

FX en la fecha del evento: fijamos 'fx _ rate _ used' y 'fx _ source'.
Hora local del mercado: DST/zonas de tiempo a través del directorio de calendarios.
Vacaciones/estacionalidad: tabla de calendario separada, utilizada en modelos e informes.

sql
SELECT p. transaction_id,
p. amount_orig,
r. rate AS fx_rate_used,
p. amount_orig r. rate AS amount_base,
r. fx_source
FROM bronze. payment_events p
JOIN dim. fx_rates r
ON r. date = DATE(p. event_time) AND r. ccy_from = p. currency AND r. ccy_to = 'EUR';

10) PII, cumplimiento y Legal Hold

Minimización PII: seudonimización, mapping protegido separado.
DSAR/RTBF: proyecciones calculadas y ediciones selectivas de capas históricas; se documentan las excepciones a la obligación legal de almacenamiento.
Legal Hold: banderas de eliminación de «congelación» en rangos/objetos, WORM para artefactos de informe.
Auditoría: registros de acceso y exportación inmutables.

11) DQ y lineage para la historia

yaml table: silver. fact_bets slo:
completeness_percent: 99. 5 freshness_minutes: 60 rules:
- name: unique_bet type: unique columns: [bet_id]
severity: critical
- name: market_known type: in_set column: market set_ref: ref. markets
- name: ts_in_range type: temporal expression: "event_time BETWEEN date_sub(now(), interval 5 year) AND now()"

Lineage: fijamos versiones de entradas/transformaciones/salidas; el grafo de dependencias es obligatorio para las retroinformaciones.

12) Rendimiento y costo

Lotes: por fecha/mercado/tenante; clustering agresivo por 'user _ pseudo _ id '/' game _ id' si a menudo filtramos.
Formatos: Parquet + estadísticas/compresión; VACUUM/OPTIMIZE regular.
Materialización: precomputo para agregaciones históricas «caras»; snapshots para informes trimestrales/anuales.
Archiving: traducción de lotes antiguos en cold storage (SLA para recuperación documentada).
Sampling: sólo para tareas de investigación, no para reguladores/finanzas.

13) Fichas históricas para ML

Registro de características: cada ficha tiene una fórmula, owner, SLO, 'model _ version'.
Consistencia online/offline: una base de códigos de transformación, pruebas de retransmisión.
Deriva de características: PSI/KS por período, almacenamiento de distribuciones históricas.

14) Patrones de consulta

As-of (en la fecha): reproducibilidad de los informes.
Cohort-análisis: cohortes de registros/primeros depósitos, ventanas rolling.
Slowly changing facts: корректные join’ы с SCD II (`event_time BETWEEN valid_from AND COALESCE(valid_to, '9999-12-31')`).

sql
SELECT b. bet_id, u. rg_status
FROM silver. fact_bets b
JOIN dim. users_scd u
ON u. user_pseudo_id = b. user_pseudo_id
AND b. event_time >= u. valid_from
AND (u. valid_to IS NULL OR b. event_time < u. valid_to);

15) Procesos y RACI

R (Responsable): Ingeniería de datos (modelos/SCD/backfill), Plataforma de datos (ACID/archivo), Finance/Compliance (conciliaciones/requisitos de almacenamiento).
A (Accountable): Head of Data/CDO.
C (Consultado): Legal/DPO (DSAR/RTBF/Legal Hold), SRE (costo/SLA), Arquitectura.
I (Informed): BI/Producto/Marketing/Operaciones.

16) Hoja de ruta para la implementación

1. Tablas ACID con tiempo de viaje (Delta/Iceberg/Hudi) y partición básica.

2. SCD II para mediciones clave (users/games/providers).

3. Snapshots diarios de agregados críticos (GGR Daily).

4. DQ-as-code (uniqueness/in_set/temporal) + lineage-grafo.

• Bitemporal hechos, como-de las plantillas API/SQL, runbooks backfill/reprocessing.
• FX/calendario/enriquecimiento DST, OLTP↔DWH/provaydery de conciliación.
• Archiving de cold storage, WORM para paquetes de informes, Legal Hold.

• Automatización completa de «replay & what-if», comparación de métricas y alertas de regresiones.
• Fichas históricas y control de deriva ML, chargeback por valor de almacenamiento.
• Documentación «as-of» de métricas e informes reproducidos.

17) Lista de verificación antes de la venta

• Las tablas admiten tiempo de viaje; Las políticas VACUUM/RETENTION son coherentes.
• SCD II se implementa para mediciones críticas; join's probados.
• Las imágenes de los agregados clave en D/M están disponibles y verificadas por las conciliaciones.
• Las reglas DQ están activas; lineage muestra las entradas/salidas y versiones de la lógica.
• DSAR/RTBF/Legal Hold se han probado en capas históricas.
• Archiving y recuperación de cold storage está documentado y validado.
• Costo de almacenamiento bajo control (costo/GB, fracción de cold, SLA de recuperación).

18) Errores frecuentes y cómo evitarlos

Ningún modelo de tiempo explícito: agregue event/processing/validity.
FX «retroactivamente»: siempre el curso en el momento del evento, almacenar 'fx _ source'.
Join's incorrectos con SCD: utilice el intervalo de validez en lugar de 'is _ current'.
Escaparates de oro mutantes: las salidas de informes deben ser inmutables (o versionables).
Sin lineage/DQ: no hay probabilidades y puntos de control - ingresarlos desde el primer día.
Costo no administrado: desconecte los lotes en caliente, vacíe, convierta en frío.

19) Glosario

As-of Query es una solicitud de datos «como se veían en el momento T».
Bitemporal - Fijación simultánea de eventos y procesamiento de tiempo.
Snapshot es una instantánea materializada del estado/agregados al final del período.
Time-travel - Lectura de las versiones históricas de las tablas.
WORM - Almacenamiento inmutable (Write Once Read Many).

20) Resultado

Trabajar con datos históricos no es solo un «almacenamiento largo», sino una disciplina de tiempo: modelos explícitos de eventos/procesar/bitemporal, SCD y snapshots, consultas como reproducibles, rigurosas conciliaciones y controles de cumplimiento, observabilidad y arquitectura de almacenamiento rentable. Siguiendo esta guía, obtendrá una sólida base histórica para informes, análisis y ML, resistente a la auditoría y a los cambios en la lógica empresarial.