Como fazer auditoria de cargas em Data Warehouse: passo a passo

Este tutorial mostra como implementar uma tabela de auditoria de cargas (load auditing) num Data Warehouse para registar sucessos, falhas e tempos. Saber o que correu bem ou mal nas cargas é útil para deteção de problemas, reexecução e melhoria contínua.

Pré-requisitos

• Conhecimentos básicos de SQL (SELECT, INSERT, UPDATE).
• Um ambiente Data Warehouse com acesso para criar tabelas e executar jobs (por exemplo SQL Server, Azure Synapse, PostgreSQL).
• Uma pipeline ETL/ELT simples ou processo de carga que possa invocar instruções SQL antes e depois da execução.

Passo 1: Conceito e colunas essenciais

Antes de criar a tabela, definimos o que queremos registar: identificador da carga, data/hora de início e fim, estado (SUCESSO/ERRO), contagens lidas/gravadas, duração e mensagem de erro. Isto permite análises de frequência de falhas e tempos médios de carga.

Passo 2: Criar a tabela de auditoria

Criamos uma tabela simples para guardar os registos de cada execução de carga. Mantemos campos suficientes para diagnóstico e agregação.

CREATE TABLE load_audit (
  load_id      BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  job_name     VARCHAR(200) NOT NULL,
  start_time   TIMESTAMP NOT NULL,
  end_time     TIMESTAMP NULL,
  status       VARCHAR(20) NOT NULL,
  rows_read    BIGINT NULL,
  rows_written BIGINT NULL,
  error_msg    VARCHAR(2000) NULL
);

Passo 3: Inserir registo no início da carga

No início do job ETL/ELT inserimos um registo com start_time e status = 'RUNNING'. Isto cria a referência para actualizar no final. Em pipelines que suportam variáveis, guarde o load_id retornado.

INSERT INTO load_audit (job_name, start_time, status)
VALUES ('daily_customer_load', CURRENT_TIMESTAMP, 'RUNNING')
RETURNING load_id;

Passo 4: Actualizar no fim com sucesso

Se a carga terminar sem erros, actualize o registo com end_time, status = 'SUCCESS' e contagens. Use a load_id obtida no passo anterior para apontar para o registo correcto.

UPDATE load_audit
SET end_time = CURRENT_TIMESTAMP,
    status = 'SUCCESS',
    rows_read = 12345,
    rows_written = 12000
WHERE load_id = 42;

Passo 5: Registar erros e mensagens de falha

Se ocorrer uma falha, capte a mensagem de erro e actualize o registo com status = 'FAILED'. Inclua stack trace reduzido ou código do erro para investigação.

UPDATE load_audit
SET end_time = CURRENT_TIMESTAMP,
    status = 'FAILED',
    error_msg = 'Timeout na leitura de origem: connection reset'
WHERE load_id = 42;

Passo 6: Automatizar em pipelines (exemplo conceptual)

Integre a lógica de insert/updates no seu orquestrador (Azure Data Factory, Azure Synapse Pipelines, Airflow). Use transacções ou blocos try/catch para garantir que o registo é actualizado mesmo em erro.

# Pseudocódigo conceptual
load_id = INSERT ... RETURNING load_id
try:
  run_etl_process()
  UPDATE load_audit SET ... WHERE load_id = load_id
except Exception as e:
  UPDATE load_audit SET status='FAILED', error_msg=substr(e.message,1,2000) WHERE load_id = load_id
  raise

Passo 7: Indicadores e relatórios

Com a tabela em uso, crie queries para métricas como duração média, taxa de sucesso e principais erros. Estes relatórios ajudam a priorizar correcções e monitorizar SLAs.

-- Duração média das cargas por job
SELECT job_name,
       AVG(EXTRACT(EPOCH FROM (end_time - start_time))) AS avg_seconds,
       SUM(CASE WHEN status='FAILED' THEN 1 ELSE 0 END) AS failures
FROM load_audit
GROUP BY job_name;

Verificar o resultado

Confirme que para cada execução do job existe um registo na tabela load_audit com start_time e end_time preenchidos e status adequado. Verifique que as falhas aparecem com error_msg e que as consultas de métricas retornam valores coerentes.

Conclusão

Implementar auditoria de cargas em Data Warehouse é um passo simples com grande retorno em diagnóstico e fiabilidade. Próximo passo: adicionar alertas automáticos para falhas recorrentes ou tempos acima do SLA. Dica: registe também a versão do job ou commit do código para facilitar a identificação da causa raiz.