Como auditar cargas de dados em ELT: passo a passo

Registar quem carregou o quê, quando e com que resultado é o que separa um pipeline ELT fiável de uma caixa preta. Auditar as cargas de dados em ELT permite investigar erros com rapidez, provar a origem de cada número e responder a uma auditoria sem andar às adivinhas.

Pré-requisitos

• Uma base de dados SQL Server ou Azure SQL (os exemplos usam T-SQL).
• Uma tabela de destino já existente para onde carrega os dados.
• Permissões para criar e alterar tabelas (CREATE TABLE, ALTER TABLE).
• Conhecimentos básicos de SQL: INSERT, UPDATE e SELECT.

Passo 1: Decidir o que auditar

Antes de escrever SQL, defina o mínimo útil. Numa carga ELT há quatro perguntas que interessam sempre: de onde vieram os dados, quando entraram, quantas linhas foram processadas e se a carga terminou com sucesso. Vamos responder a todas com duas peças simples: colunas de auditoria em cada tabela de destino e uma tabela de log central que guarda o histórico de execuções.

Mantenha a tabela de log separada dos dados de negócio. Assim pode arquivá-la ou limpá-la sem tocar nas suas tabelas de negócio.

Passo 2: Adicionar colunas de auditoria à tabela de destino

As colunas de auditoria acompanham cada linha e dizem-lhe de que carga essa linha veio. Adicione um identificador do lote, a data e hora de carga e o sistema de origem.

ALTER TABLE vendas
    ADD batch_id       BIGINT,
        data_carga     DATETIME2,
        sistema_origem VARCHAR(50);

A partir daqui, cada linha carregada fica "carimbada" e pode ser ligada a uma execução concreta.

Passo 3: Criar a tabela de log de cargas

A tabela de log é o diário do seu pipeline: uma linha por cada execução. Crie-a apenas uma vez.

CREATE TABLE etl_load_log (
    batch_id       BIGINT IDENTITY(1,1) PRIMARY KEY,
    tabela_destino VARCHAR(100),
    inicio         DATETIME2,
    fim            DATETIME2,
    linhas         INT,
    estado         VARCHAR(20)
);

Passo 4: Registar o início da carga

No arranque de cada carga, inserimos uma linha com o estado RUNNING e guardamos o batch_id gerado para o reutilizar nos passos seguintes.

DECLARE @batch_id BIGINT;

INSERT INTO etl_load_log (tabela_destino, inicio, estado)
VALUES ('vendas', SYSDATETIME(), 'RUNNING');

SET @batch_id = SCOPE_IDENTITY();

Passo 5: Carregar os dados e carimbar as linhas

Agora corre a transformação e a carga habituais, preenchendo as colunas de auditoria com o batch_id da execução atual. Guarde também o número de linhas inseridas com @@ROWCOUNT.

DECLARE @linhas INT;

INSERT INTO vendas (id, valor, batch_id, data_carga, sistema_origem)
SELECT s.id, s.valor, @batch_id, SYSDATETIME(), 'loja_online'
FROM   staging_vendas AS s;

SET @linhas = @@ROWCOUNT;

Passo 6: Fechar o registo com o resultado

Quando a carga termina, atualizamos a mesma linha do log com a hora de fim, o número de linhas afetadas e o estado final.

UPDATE etl_load_log
SET    fim    = SYSDATETIME(),
       linhas = @linhas,
       estado = 'SUCCESS'
WHERE  batch_id = @batch_id;

Se algum passo falhar, envolva a carga num bloco TRY...CATCH e grave o estado FAILED em vez de SUCCESS. Assim distingue de imediato as execuções com problemas.

Verificar o resultado

Consulte a tabela de log para ver o histórico das últimas cargas. Deve aparecer uma linha por execução, com início, fim, número de linhas e o estado SUCCESS.

SELECT batch_id, tabela_destino, inicio, fim, linhas, estado
FROM   etl_load_log
ORDER  BY batch_id DESC;

Para confirmar a rastreabilidade, escolha um batch_id e conte as linhas correspondentes na tabela de destino — o total deve coincidir com o valor gravado no log.

SELECT COUNT(*) FROM vendas WHERE batch_id = 1;

Conclusão

Com colunas de auditoria e uma tabela de log passou a saber, para cada linha, de onde veio e em que carga entrou — a base para investigar erros e confiar nos números que apresenta. O passo seguinte natural é construir um pequeno painel sobre a etl_load_log com as cargas falhadas e a duração média de cada carga. Qual das métricas seria mais útil vigiar primeiro no seu pipeline: as cargas que falham ou as que demoram tempo a mais?