Pular para conteúdo

Fase 4: Validação dos Dados (O "Sherlock")

A Fase 4 foi o estágio de auditoria rigorosa do projeto InfoEscola. Após a extração do banco legado (MySQL 5.1) e a carga no Cloud SQL (MySQL 8.0), o desafio não era apenas garantir que os dados "pareciam" corretos, mas comprovar a integridade bit a bit de milhões de registros. Para isso, o projeto abandonou os simples scripts de diff em favor de um ecossistema de reconciliação forense automatizado.

🛠️ Técnicas e Arquitetura de Validação

A validação foi estruturada em "camadas de defesa", otimizando o uso de recursos e garantindo uma reconciliação inteligente e idempotente:

1. Gerenciamento de Estado com SQLite (pipeline_state.db) Verificar 100GB de dados em centenas de tabelas é demorado. Foi implementado um orquestrador com estado em SQLite (PipelineStateManager) para rastrear o status de cada tabela (PENDING, RUNNING, SUCCESS, FAILED). Se o script fosse interrompido, ele retomaria exatamente de onde parou, ignorando tabelas já validadas.

2. Validação em Três Níveis (Smart Reconciliation) Para economizar I/O e processamento, o script reconcile_tables.py executava os testes de forma condicional: * Nível 1 (Esquema): Comparava os DDLs, os nomes e os tipos das colunas. * Nível 2 (Contagem Rápida): Executava um SELECT COUNT(*) exato ("Just-in-Time"). Se houvesse divergência de linhas, o processo falhava imediatamente ("Fail Fast") sem gastar CPU com hashes. * Nível 3 (Checksum Agregado): Se a contagem batesse, era gerado um "fingerprint" matemático de toda a tabela.

3. Checksum Agregado no Servidor (Pushdown Hashing) Em vez de mover terabytes de dados pela rede para comparar em Python, o "trabalho pesado" foi empurrado para o motor do banco de dados. Foi utilizada uma query complexa que concatenava todas as colunas de uma linha, aplicava um Hash e agregava tudo: SELECT BIT_XOR(CAST(CONV(SUBSTRING(MD5(CONCAT_WS('~|~', ...))), 1, 16), 16, 10) AS UNSIGNED)). O uso do BIT_XOR garantiu que a verificação fosse independente da ordem física das linhas no disco e o tráfego de rede foi reduzido a meros 32 bytes (o hash final).

4. Chunking e "Early Exit" Para tabelas gigantes com Chaves Primárias numéricas, o checksum foi dividido em lotes (chunks) de 100.000 linhas usando BETWEEN min_id AND max_id. Ao encontrar o primeiro lote divergente, o script parava imediatamente (Early Exit), otimizando enormemente o tempo de depuração.

5. As Ferramentas Forenses: O "Sherlock" Quando uma divergência era detectada, ferramentas cirúrgicas entravam em ação: * find_row_diffs.py: Fazia queries de LEFT JOIN entre origem e destino para descobrir as IDs exatas de linhas "órfãs" ou "faltantes". * sherlock.py: Quando a contagem batia mas o checksum falhava, essa ferramenta buscava a linha exata em ambos os bancos e fazia uma comparação byte a byte no nível Hexadecimal, apontando exatamente o índice e o caractere divergente.

🚧 Problemas Enfrentados e 💡 Soluções Aplicadas

O nível de profundidade da ferramenta sherlock.py expôs anomalias fascinantes entre o comportamento do MySQL legado e as configurações modernas.

Problema 1: A Anomalia dos 274 Espaços e a Limitação do MySQL 5.1

  • O Problema: O checksum de uma tabela falhava constantemente. O sherlock.py identificou que na origem um campo de texto (TINYTEXT / TEXT) terminava com múltiplos espaços, enquanto no destino ele havia sido limpo corretamente. A investigação revelou um caso extremo: um registro com 274 espaços contíguos (...202020... em HEX).
  • A Solução Logarítmica: A lógica de limpeza na extração aninhava múltiplas chamadas REPLACE(col, ' ', ' '). Para lidar com 274 espaços, foi provado matematicamente que eram necessários 11 níveis de aninhamento (2^11 = 2048 espaços máximos tratados).
  • O Desafio do "Idoso": O otimizador do banco de origem (MySQL 5.1) era primitivo e falhava silenciosamente ao tentar executar a query com os 11 REPLACEs aninhados, ignorando as instruções.
  • A Solução Arquitetural: Abandonou-se a tentativa de forçar o MySQL 5.1 a limpar os dados. A lógica foi alterada para extrair o HEX puro (Offload de CPU) e processar as remoções de espaço em memória no cliente Python.

Problema 2: Expansão de Encoding e Truncamento Silencioso

  • O Problema: Em tabelas sem truncamento aparente, o sherlock.py detectou a falta do final de algumas palavras (ex: conf cortado de uma string).
  • A Solução: Descobriu-se o problema da "expansão de encoding". A origem (latin1) armazenava acentos em 1 byte (ex: á = E1). O script os normalizava para o destino moderno em utf8mb4 (á = C3A1, 2 bytes). A string "expandia" e ultrapassava o limite de 255 bytes da coluna original TINYTEXT, causando o truncamento durante o LOAD DATA. A solução foi ajustar os metadados do schema no Cloud SQL, alterando a coluna de TINYTEXT para TEXT.

Problema 3: "Data Zero" (0000-00-00) em Campos NOT NULL

  • O Problema: A sanitização inicial convertia as datas inválidas do MySQL 5.1 (0000-00-00) para NULL. Entretanto, ao tentar carregar isso em tabelas cujo schema definia a coluna de data como NOT NULL, a inserção falhava.
  • A Solução: Foi implementada uma heurística sofisticada usando Expressão Regular/Condicionais (CASE WHEN DAY(col) = 0 OR MONTH(col) = 0...) para capturar até mesmo datas "parcialmente zero" (ex: 0001-01-00) e substituí-las por um "Valor Sentinela" seguro, como '1900-01-01', respeitando a restrição de integridade original.

Problema 4: Corrupção do Campo de Senha Criptografada

  • O Problema: A adoção maciça das rotinas de higienização de string no pipeline (remoção de espaços duplos e quebras de linha) acabou corrompendo a coluna PesSenhaCripto. O MySQL retornava metadados informando que a coluna era VARCHAR, então o script a "limpou", destruindo pedaços do hash binário.
  • A Solução: Foi necessário aplicar o Princípio da Responsabilidade Única e "ensinar" uma exceção explícita ao gerador de SQL. O script foi forçado a tratar esse campo disfarçado de texto como um autêntico VARBINARY, aplicando a função HEX() nativa no momento da extração sem nenhuma sanitização textual, permitindo a recomposição exata via UNHEX() na carga.

Problema 5: Timeouts em Consultas Pesadas

  • O Problema: As operações de checksum em grandes tabelas sofriam congelamento e quebravam a execução.
  • A Solução: Identificou-se que o gargalo não era falta de recursos, mas o parâmetro wait_timeout do servidor de origem matando a conexão após 5 minutos de aparente ociosidade. A injeção da flag read_timeout=3600 e a parametrização do Chunking mantiveram os pacotes TCP Keepalive ativos, curando os timeouts.

Problema 6: Data Drift (Deriva de Dados)

  • O Problema: Durante a execução do sherlock.py, alguns registros apresentavam divergências de tamanho (ex: Origem com 10 caracteres, Destino com 8).
  • A Solução: Confirmou-se que isso não era um bug técnico. O banco legado não estava congelado e ainda disparava "Events" ou "Triggers" em background. Entre o momento de extração (Tempo T0) e o momento da validação (Tempo T2), o dado da origem havia mudado. A solução foi processual: a validação final oficial demandaria uma "Janela de Silêncio" para garantir consistência de snapshot ponta a ponta.

Resumo da Fase: A Fase 4 provou que em migrações complexas, mover os dados é apenas 10% do esforço; provar a sua fidelidade representa os 90% restantes. As ferramentas forenses como o "Sherlock" validaram que as arquiteturas de transformação desenvolvidas em Python (Polars/ConnectorX) resistiram perfeitamente a problemas de enconding, data suja e limitações de hardware de um servidor "idoso".