Trabalhar com dados históricos

1) Atribuição e princípios

Objetivo: armazenar e processar estados passados para que relatórios, modelos e investigações sejam reproduzíveis, precisos e complicados.

• Time-aware by design: modelos de tempo nítidos em esquemas e requerimentos.
• Reprodúcibilidade: O mesmo relatório por data D é sempre o mesmo.
• Auditability: origem comprovada (lineage), camadas imutáveis, WOM onde for necessário.
• Costa-aware: camadas de arquivo, compressão, armazenamento cold com SLA compreensível.
• Private-by-design: gerenciamento do PII em transações e pedidos legais.

2) Modelos de tempo

Event time: hora do evento real (taxa, depósito).
Processing-time: quando o sistema processou a gravação (pode variar).
Bitemporal: armazenamento e event- e processing time para edição retroativa.
Espaçamento de validade: 'valid _ from', 'valid _ to', 'is _ current'.
As-of queries: amostra de dados «como sabiam no momento do 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) Camadas de armazenamento e formatos

Lakehouse: Bronze (raw append-only) → Silver (clean/SCD/normalização) → Gold (vitrines).
ACID-форматы: Delta/Iceberg/Hudi (MERGE/Upsert, time-travel, snapshots).
Armazenamento Tiered: hot/warm/cold + WORM para artefatos regulatórios.
Partilhar: 'event _ data', 'market', 'tenant'; clusterização/Z-order por predicado frequente (user/game/provider).

4) Histórico de medições (SCD)

SCD I: Reinicie - Edições não ríticas.
SCD II: história completa; recomendado para RG/KYC/canais de tráfego/atributos de jogos.
SCD III: «antes/depois» - malas raras de comparação.

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) História dos fatos: fotos e bitemporal

Instantâneos (snapshots): a imagem do dispositivo ao fim do dia/mês (como o balanço da carteira) - acelera a reconstituição dos relatórios históricos.
Fatos Bitemporal: Registramos event-time e processing-time para diferenciar as correções tardias dos cálculos retrospectivos.
Histórico exactly-once: Deadup por 'event _ id' + idumpotentes MERGE.

6) Time-travel e reprodução

Time-travel: leitura de tabelas «no momento T» para depuração, incidentes, forró.
Versionização da lógica: artefatos de transformação (versões SQL/DBT, contêineres) e rótulos logic _ versão nas tabelas de saída.
Frozen outputs: Os artefatos gold relatórios são captados e não são enviados, hash e o registro de exportação estão disponíveis.

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

7) Backfill и Reprocessing

Backfill é o principal/contorno da faixa histórica.
Reprocessing: Recontagem após a correção de bagagens ou mudança de regras empresariais.

• Idempotidade (MERGE/upsert), faixas, quotas, «tostão escuro» (dry-run) com comparações de métricas.
• Marcamos o resultado: 'recalc _ reason', 'logic _ version', 'reprocessed _ at'.

1. Freeze atual Gold; 2) verificar DLQ/DQ; 3) Detecção Silver; 4) comparação de métricas; 5) Cruzamento Gold; 6) publicação e assinatura.

8) Acréscimos de precisão (reconciação)

Somas de controle: processamento de rotações/quantidades com OLTP, PSP/provedores.
Verificação de dois contornos: pipeline independente por amostra (A/B comparação).
Permissões, por exemplo, divergência GGR ≤ 0. 2% por dia.

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) Moedas, tempo, calendário: correção histórica

FX na data do evento: registramos 'fx _ rate _ used' e 'fx _ fonte'.
Hora local do mercado: DST/Timsons através do guia de calendários.
Feriado/sazonalidade: tabela de calendário separada, use em modelos e relatórios.

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, Complance e Legal Hold

Minimização PII: pseudônimo, mapping protegido separado.
DSAR/PTBF: projeções computáveis e edições seletivas de camadas históricas; exceções ao dever legítimo de armazenamento são documentadas.
Legal Hold: bandeiras de «congelamento» remoções para faixas/objetos, WORM para artefatos relatados.
Auditoria: logs de acessibilidade e exportação imutáveis.

11) DQ e lineage para a história

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: Registramos versões de entradas/transformações/saídas; o gráfico de dependências é obrigatório para retocações.

12) Desempenho e custo

Particionamento: por data/mercado/tenante; clusterização agressiva por 'user _ pseudo _ id '/' game _ id', se filtrado frequentemente.
Formatos: Parquet + estatística/compressão; VACUUM/OPTIMIZE regular.
Materialização: precompute para agregações históricas «caras»; para relatórios quadrienais/anuais.
Arquivamento: Tradução de partituras antigas para armazenamento (SLA para recuperação).
Samplying: apenas para tarefas de pesquisa, não para regulação/finanças.

13) Fichas históricas para ML

Função registry: Cada fichinha tem uma fórmula, owner, SLO, 'modelo _ versão'.
Coerência online/offline: uma base de dados de transformação, testes de reaproveitabilidade.
Sinais à deriva: PSI/KS por períodos, armazenamento de repartições históricas.

14) Pattern de consulta

As-of (data): Reprodutividade de relatórios.
Colhort-análise: Colas de registros/primeiros depósitos, janelas 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) Processos e RACI

R (Resolvível): Data Engineering (modelos/SCD/backfill), Data Platford (ACID/Arquivo), Finance/Compliance (conformações/requisitos de armazenamento).
A (Accountable): Head of Data/CDO.
C (Consulted): Legal/DPO (DSAR/PHILBF/Legal Hold), SRE (custo/SLA), Arquitetura.
I (Informed): BI/Produto/Marketing/Operações.

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

1. Tabelas ACID com time-travel (Delta/Iceberg/Hudi) e partitização básica.

2. SCD II para medidas-chave (users/games/providers).

3. Diárias snapshots de unidades críticas (GGR Daily).

4. DQ-como-código (uniqueness/in _ set/temporal) + lineage-grafo.

• Fatos Bitemporal, as-of API/modelos SQL, runbooks backfill/reprocessing.
• FX/calendário/enriquecimento DST, comprimidos OLTP↔DWH/provaydery.
• Arquivamento de cold armazenamento, WORM para pacotes de relatório, Legal Hold.

• Automação completa de «replay & what-if», comparando métricas e alertas de regressão.
• Fichas históricas e controle à deriva da ML, um chargeback de custo de armazenamento.
• Documentação «as-of» e relatórios reproduzidos.

17) Folha de cheque antes de vender

• Tabelas suportam time-travel; as políticas VACUUM/RETENÇÃO estão alinhadas.
• O SCD II é implementado para medições críticas; join's testados.
• As imagens das unidades-chave em D/M estão disponíveis e testadas em comprimidos.
• As regras DQ estão ativas; lineage exibe entradas/saídas e versões da lógica.
• DSAR/PTBF/Legal Hold testados em camadas históricas.
• O arquivamento e recuperação a partir de cold armazenamento foi documentado e verificado.
• Custo de armazenamento sob controle (custo/GB, porção cold, SLA recuperação).

18) Erros frequentes e como evitá-los

Não há um modelo de tempo claro: adicione event/processing/validity.
FX «retroativo»: sempre um curso no momento do evento, armazenar 'fx _ fonte'.
Join's incorretos com SCD: use o intervalo de validade em vez de 'is _ current'.
Vitrines Gold em mutação: as saídas de relatório devem ser inalteráveis (ou versionizadas).
Sem lineage/DQ: Sem comprovabilidade ou pontos de controle - digite-os a partir do primeiro dia.
Custo descontrolado: Desliga as partituras quentes, vácuo, transfira para cold.

19) Glossário

As-of Query - o pedido de dados «como eram no momento T».
Bitemporal - fixação simultânea de event e processing de tempo.
O Snapshot é uma imagem materializada do estado/unidade ao final do período.
Time-travel - leitura de versões históricas de tabelas.
WORM - Armazenamento imutável (Write Once Read Many).

20) Total

Trabalhar com dados históricos não é apenas «armazenamento longo», mas sim uma disciplina de tempo: modelos explícitos de event/processing/bitemporal, SCD e snapshots, solicitações de reposição as-of, acertos rigorosos e controladores complacentes, observabilidade e arquitetura de armazenamento de baixo custo. Seguindo esta orientação, você terá uma base histórica confiável para relatórios, analistas e ML resistente à auditoria e mudanças de lógica de negócios.