Como validar esquemas de dados em ELT: passo a passo

Validação de esquemas em ELT é garantir que os dados de origem têm a estrutura esperada antes de serem transformados no destino. Isto evita que cargas falhem, que relatórios mostrem valores errados e facilita a manutenção de pipelines.

Pré-requisitos

• Conta com cluster ou serviço que suporte SQL/SQL-like (ex.: Databricks, Synapse, Snowflake).
• Ficheiros de origem em JSON/CSV ou uma tabela de staging.
• Conhecimentos básicos de SQL e alguma ferramenta para executar scripts (notebook ou CLI).

Passo 1: Definir o esquema de referência

O primeiro passo é ter um esquema de referência (o que esperamos). Pode ser um ficheiro JSON com campos e tipos, ou uma tabela de metadados. Este esquema serve para comparar com os dados de entrada e decidir se a carga deve prosseguir, alertar ou aplicar regras de correcção.

{
  "table": "clientes",
  "columns": [
    {"name":"id", "type":"INTEGER", "nullable":false},
    {"name":"nome", "type":"STRING", "nullable":false},
    {"name":"email", "type":"STRING", "nullable":true},
    {"name":"created_at", "type":"TIMESTAMP", "nullable":false}
  ]
}

Passo 2: Inferir ou ler o esquema dos dados de origem

Extrair o esquema actual dos ficheiros ou da tabela de origem. Em muitos ambientes (ex.: Spark/Databricks) é fácil inferir o esquema; noutras, consulta-se a definição da tabela de staging.

-- Exemplo SQL para obter colunas de uma tabela staging (Postgres/MySQL/Snowflake têm variações)
SELECT column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_name = 'staging_clientes';

Passo 3: Comparar esquemas e identificar discrepâncias

Com o esquema de referência e o esquema inferido, comparar campo a campo. Procurar diferenças: campos em falta, tipos incompatíveis, nullable diferente ou novos campos inesperados. Implementar uma função simples de comparação permite automatizar decisões.

-- Pseudocódigo SQL para identificar colunas em falta
SELECT r.name AS expected, s.column_name AS actual
FROM reference_schema r
LEFT JOIN information_schema.columns s
  ON r.name = s.column_name
WHERE s.column_name IS NULL;

Passo 4: Regras de correcção automática (casting e preenchimento)

Decidir regras para corrigir automaticamente problemas comuns: converter tipos (ex.: STRING para INTEGER com TRY_CAST), preencher valores nulos com defaults, ignorar campos adicionais. Estas regras mantêm a carga sem intervenção manual quando apropriado.

-- Exemplo em SQL/DBT-like para aplicar casting seguro
SELECT
  TRY_CAST(id AS INTEGER) AS id,
  nome,
  NULLIF(email, '') AS email,
  TRY_CAST(created_at AS TIMESTAMP) AS created_at
FROM staging_clientes;

Passo 5: Tagging e auditoria das discrepâncias

Registar meta-informação sobre a validação: linhas com casting falhado, campos preenchidos por default, ou colunas ignoradas. Criar uma tabela de auditoria facilita troubleshooting e reprocessamento.

-- Exemplo de inserção na tabela de auditoria
INSERT INTO schema_audits(table_name, issue_type, detail, detected_at)
VALUES('clientes','missing_column','email missing in source', CURRENT_TIMESTAMP);

Passo 6: Integrar validação no pipeline ELT

Colocar a validação como etapa inicial do ELT: (1) extrair para staging, (2) validar esquema, (3) aplicar correcções, (4) carregar para o destino. Automatizar com jobs que falhem em níveis críticos e alertem em discrepâncias menores.

-- Pseudoflow orchestration
1. Extract -> staging_clientes
2. Run validate_schema('clientes')
   -> returns status and audit rows
3a. If status = FAIL then alert and stop
3b. If status = WARN then apply corrections and continue
4. Load to production_clientes

Verificar o resultado

Confirmar que o destino tem as colunas com os tipos esperados e que a tabela de auditoria contém quaisquer discrepâncias. Testes a executar: query simples para verificar tipos e nulls; contar linhas com casting falhado; rever entradas de audit.

-- Verificações rápidas
SELECT COUNT(*) FROM production_clientes;
SELECT COUNT(*) FROM production_clientes WHERE id IS NULL;
SELECT * FROM schema_audits WHERE table_name='clientes' ORDER BY detected_at DESC LIMIT 10;

Conclusão

Validar esquemas em ELT reduz falhas e garante que as transformações são aplicadas a dados com estrutura conhecida. Próximos passos: implementar testes automatizados de regressão de esquema e alertas via e-mail/Slack. Dica: comece por regras simples (casting e defaults) e vá enriquecendo a auditoria conforme a necessidade.