Cómo validar data lineage en ELT: paso a paso

Validar data lineage en ELT significa demostrar de dónde vinieron los datos, qué transformaciones sufrieron y dónde terminaron. Esto ayuda a investigar errores, cumplir auditorías y garantizar confianza en los análisis.

Requisitos previos

• Un entorno ELT con soporte SQL (ej.: Databricks, Azure Synapse, Snowflake).
• Una tabla de origen (raw) y una tabla destino (trusted/silver) en el formato que prefiera (Delta, Parquet, o tabla SQL).
• Permiso para crear tablas/archivos de metadatos y ejecutar consultas.
• Conocimientos básicos de SQL y de versionado de código.

Paso 1: Definir los elementos de lineage que queremos capturar

Antes de codificar, elija qué metadatos serán registrados: fuente (nombre, esquema, timestamp), transformación (query o job id), columnas impactadas y checksum o hash de la carga. Esto hace la validación práctica y limitada a lo esencial.

Paso 2: Crear una tabla de metadatos de lineage

Crear una tabla simple para registrar cada ejecución ELT. Incluye id, fuente, target, query_hash, row_count, col_checksums y run_timestamp. Ejemplo en SQL que funciona en muchos 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
);

Paso 3: Generar un identificador y hash de la transformación

Antes de aplicar la transformación ELT, genere un run_id y un hash de la query para identificar con precisión la lógica aplicada. El hash ayuda a detectar regresiones cuando la query cambia.

-- Pseudocódigo SQL/psuedoscript
-- run_id: UUID o timestamp + jobname
SET run_id = 'run_' || CURRENT_TIMESTAMP();
-- query_text: a SQL que hará la transformación
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);

Paso 4: Calcular checksums de columnas relevantes en la origen

Calcule valores agregados que representen el contenido de las columnas (por ejemplo md5(concat(col1,col2))). Esto permite validar que los mismos datos llegaron al target tras transformaciones previsibles.

-- Ejemplo: calcular checksum por columna en la origen
SELECT
  COUNT(*) AS row_count,
  MD5(CONCAT(COALESCE(id,''), COALESCE(name,''))) AS cols_checksum
FROM raw
WHERE dt = '2026-01-01';

Paso 5: Ejecutar la transformación ELT y registrar métricas

Ejecute la transformación (INSERT/CREATE TABLE AS SELECT) e inmediatamente registre en lineage_metadata el run_id, fuentes, target, query_hash, row_count y col_checksums calculados antes o después de la carga.

-- 1) Transformación
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) Insertar metadatos
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());

Paso 6: Comparar checksums y row_count para validar lineage

Haga consultas que comparen el checksum y row_count de la origen con los registrados en el target. Si divergen, investigue: la transformación esperada (ej.: agregación) puede justificar diferencias; en caso contrario, hay un problema.

-- Comparar origen 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 el resultado

Confirme que row_count y col_checksums son iguales o que las diferencias se explican por la lógica de transformación (ej.: agregación, filtro). Verifique también el lineage_metadata para cada run_id: debe contener query_hash y timestamp. En caso de mismatch, corrija la query o reprocesar la carga.

Conclusión

Validar data lineage en ELT le proporciona trazabilidad y confianza para diagnósticos y auditorías. Próximos pasos: automatizar el registro con scripts/Notebooks, integrar con CI/CD y almacenar query_text junto al query_hash. Consejo: añada un campo job_version para ligar el lineage al código de transformación; ¿ya tiene eso en su proceso?