Como validar data lineage em ELT: passo a passo

Validar data lineage em ELT significa provar de onde os dados vieram, que transformações sofreram e onde acabaram. Isto ajuda a investigar erros, cumprir auditorias e garantir confiança nas análises.

Pré-requisitos

• Um ambiente ELT com suporte a SQL (ex.: Databricks, Azure Synapse, Snowflake).
• Uma tabela de origem (raw) e uma tabela alvo (trusted/silver) no formato que preferir (Delta, Parquet, ou tabela SQL).
• Permissão para criar tabelas/ficheiros de metadados e executar queries.
• Noções básicas de SQL e de versionamento de código.

Passo 1: Definir os elementos de lineage que queremos capturar

Antes de codificar, escolha quais metadados serão registados: fonte (nome, esquema, timestamp), transformação (query ou job id), colunas impactadas e checksum ou hash da carga. Isto torna a validação prática e limitada ao essencial.

Passo 2: Criar uma tabela de metadados de lineage

Criar uma tabela simples para registar cada execução ELT. Inclui id, fonte, target, query_hash, row_count, col_checksums e run_timestamp. Exemplo em SQL que funciona em muitos motores:

CREATE TABLE IF NOT EXISTS lineage_metadata (
  run_id STRING PRIMARY KEY,
  source_name STRING,
  target_name STRING,
  query_hash STRING,
  row_count BIGINT,
  col_checksums STRING,
  run_timestamp TIMESTAMP
);

Passo 3: Gerar um identificador e hash da transformação

Antes de aplicar a transformação ELT, gere um run_id e um hash da query para identificar com precisão a lógica aplicada. O hash ajuda a detetar regressões quando a query muda.

-- Pseudocódigo SQL/psuedoscript
-- run_id: UUID ou timestamp + jobname
SET run_id = 'run_' || CURRENT_TIMESTAMP();
-- query_text: a SQL que fará a transformação
SET query_text = 'INSERT INTO trusted SELECT id, name, amount*1.0 FROM raw WHERE dt = ''2026-01-01''';
-- query_hash: usar função de hash do motor (MD5/SHA1/sha2)
SET query_hash = MD5(query_text);

Passo 4: Calcular checksums de colunas relevantes na origem

Calcule valores agregados que representem o conteúdo das colunas (por exemplo md5(concat(col1,col2))). Isto permite validar que os mesmos dados chegaram ao target após transformações previsíveis.

-- Exemplo: calcular checksum por coluna na origem
SELECT
  COUNT(*) AS row_count,
  MD5(CONCAT(COALESCE(id,''), COALESCE(name,''))) AS cols_checksum
FROM raw
WHERE dt = '2026-01-01';

Passo 5: Executar a transformação ELT e registar métricas

Execute a transformação (INSERT/CREATE TABLE AS SELECT) e imediatamente registe no lineage_metadata o run_id, fontes, target, query_hash, row_count e col_checksums calculados antes ou depois da carga.

-- 1) Transformação
INSERT INTO trusted
SELECT id, name, amount*1.0 AS amount
FROM raw
WHERE dt = '2026-01-01';

-- 2) Medir target
SELECT COUNT(*) AS row_count_target,
       MD5(CONCAT(COALESCE(id,''), COALESCE(name,''))) AS cols_checksum_target
FROM trusted
WHERE dt = '2026-01-01';

-- 3) Inserir metadados
INSERT INTO lineage_metadata (run_id, source_name, target_name, query_hash, row_count, col_checksums, run_timestamp)
VALUES (run_id, 'raw', 'trusted', query_hash, :row_count_target, :cols_checksum_target, CURRENT_TIMESTAMP());

Passo 6: Comparar checksums e row_count para validar lineage

Faça queries que comparem o checksum e row_count da origem com os registados no target. Se divergirem, investigue: transformação esperada (ex.: agregação) pode justificar diferenças; caso contrário, há problema.

-- Comparar origem vs target
WITH src AS (
  SELECT COUNT(*) AS rc, MD5(CONCAT(COALESCE(id,''), COALESCE(name,''))) AS cs
  FROM raw WHERE dt = '2026-01-01'
), tgt AS (
  SELECT COUNT(*) AS rc, MD5(CONCAT(COALESCE(id,''), COALESCE(name,''))) AS cs
  FROM trusted WHERE dt = '2026-01-01'
)
SELECT
  src.rc AS src_rows, tgt.rc AS tgt_rows,
  src.cs AS src_checksum, tgt.cs AS tgt_checksum
FROM src CROSS JOIN tgt;

Verificar o resultado

Confirme que row_count e col_checksums são iguais ou que as diferenças são explicadas pela lógica de transformação (ex.: agregação, filtro). Verifique também o lineage_metadata para cada run_id: deve conter query_hash e timestamp. Em caso de mismatch, corrija a query ou reprocese a carga.

Conclusão

Validar data lineage em ELT dá-lhe rastreabilidade e confiança para diagnósticos e auditorias. Próximos passos: automatizar o registo com scripts/Notebooks, integrar com CI/CD e armazenar query_text junto do query_hash. Dica: adicione um campo job_version para ligar o lineage ao código de transformação; já tem isso no seu processo?