Cómo validar esquemas de datos en ELT: paso a paso

Validación de esquemas en ELT significa garantizar que los datos de origen tienen la estructura esperada antes de ser transformados en el destino. Esto evita que las cargas fallen, que los informes muestren valores erróneos y facilita el mantenimiento de los pipelines.

Pre-requisitos

• Cuenta con cluster o servicio que soporte SQL/SQL-like (ex.: Databricks, Synapse, Snowflake).
• Archivos de origen en JSON/CSV o una tabla de staging.
• Conocimientos básicos de SQL y alguna herramienta para ejecutar scripts (notebook o CLI).

Paso 1: Definir el esquema de referencia

El primer paso es tener un esquema de referencia (lo que esperamos). Puede ser un archivo JSON con campos y tipos, o una tabla de metadatos. Este esquema sirve para comparar con los datos de entrada y decidir si la carga debe proseguir, alertar o aplicar reglas de corrección.

{
  "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}
  ]
}

Paso 2: Inferir o leer el esquema de los datos de origen

Extraer el esquema actual de los archivos o de la tabla de origen. En muchos entornos (ex.: Spark/Databricks) es fácil inferir el esquema; en otros, se consulta la definición de la tabla 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';

Paso 3: Comparar esquemas e identificar discrepancias

Con el esquema de referencia y el esquema inferido, comparar campo por campo. Buscar diferencias: campos faltantes, tipos incompatibles, nullable diferente o nuevos campos inesperados. Implementar una función simple de comparación permite automatizar decisiones.

-- 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;

Paso 4: Reglas de corrección automática (casting y rellenado)

Decidir reglas para corregir automáticamente problemas comunes: convertir tipos (ex.: STRING a INTEGER con TRY_CAST), rellenar valores nulos con defaults, ignorar campos adicionales. Estas reglas mantienen la carga sin intervención manual cuando procede.

-- 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;

Paso 5: Tagging y auditoría de las discrepancias

Registrar meta-información sobre la validación: filas con casting fallido, campos rellenados por default, o columnas ignoradas. Crear una tabla de auditoría facilita el troubleshooting y el reprocesado.

-- 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);

Paso 6: Integrar validación en el pipeline ELT

Poner la validación como etapa inicial del ELT: (1) extraer a staging, (2) validar esquema, (3) aplicar correcciones, (4) cargar al destino. Automatizar con jobs que fallen en niveles críticos y alerten en discrepancias 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 el resultado

Confirmar que el destino tiene las columnas con los tipos esperados y que la tabla de auditoría contiene las discrepancias. Tests a ejecutar: query simple para verificar tipos y nulls; contar filas con casting fallido; revisar 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;

Conclusión

Validar esquemas en ELT reduce fallos y garantiza que las transformaciones se aplican a datos con estructura conocida. Próximos pasos: implementar tests automáticos de regresión de esquema y alertas via e-mail/Slack. Consejo: comienza con reglas simples (casting y defaults) y ve enriqueciendo la auditoría según la necesidad.